O/R-map a single arbitrary SQL SELECT statement to a single class?

I don't know much about the mechanics of how ORM tools work, but from working with Entity Framework (EF), I know it's somehow possible to auto-generate CLR classes from arbitrary Views stored in a database. In EF these classes are auto-generated from your entity model. EF can even get column information from a stored procedure and generate a new "complex type" from this information.

• How is it possible auto-generate .NET classes, using only (1) the information provided by the SQL SELECT statement and (2) the ability to query the database for its metadata?

Because the SQL is stored in a database, we know at compile time 开发者_Python百科what the SELECT SQL is. Similarly, Entity Framework knows what a View or stored procedure's SELECT SQL is at compile time. We want to auto-generate .NET classes from knowing the SELECT SQL and querying the database's metadata. I need to be able to transform the resulting single DataTable into the auto-generated classes.

It seems like the answer to my question lies in having knowledge of ORM tools;

• is there an ORM tool out there that can already do what I need, or
• could you explain how Entity Framework auto-generates its .NET classes from Views and Stored Procedures?
• Or if you can come up with another solution to auto-generating classes for my scenario, that would also be appreciated.

If any clarification is needed, just let me know.

---

Metadata about tables and views are loaded from system views: sys.tables, sys.views, sys.columns and others.

Edit:

I just tested how EF gets column names and types and it indeed executes stored procedure and iterates over returned columns. It only uses some complex queries to correctly build call command and handle return value.

---

You actually don't really need an ORM to do this if you use the System.Data.SqlClient namespace. Here's a quick-and-dirty example that works for random query statements:

public static List<string> GetColumns(string connectionString, string queryString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(queryString, connection);

        connection.Open();
        List<string> columnNames = new List<string>();
        SqlDataReader reader = adapter.SelectCommand.ExecuteReader();
        for (int xx = 0; xx < reader.FieldCount; xx++)
        {
            columnNames.Add(reader.GetName(xx));
        }
        return columnNames;
    }
}

This doesn't help you auto-generate the classes per se. Still, it's a starting point for pulling the data you'll need for doing so.

---

I figured out how to do this. I wrote a very basic solution to this here.