Entity Framework - 将IQueryable转换为DataTable

Question

我想要将一个 IQueryable 对象转换为 DataTable。以下是一个需要转换为 DataTable 的查询示例：

var query = DbContext.SomeObjectSet.Select(x => new { PropertyName1 = x.ColumnName1, PropertyName2 = x.ColumnName2 });

请注意在Select方法中创建的匿名对象和属性名称：

new { PropertyName1 = x.ColumnName1, PropertyName2 = x.ColumnName2 }

在通过谷歌搜索此问题后，我找到了以下代码，它将一个 IQueryable 对象转换为 DataTable:

public static DataTable EntityToDatatable(this IQueryable result)
{
    ObjectQuery query = (result as ObjectQuery);

    ObjectContext context = query.Context;

    EntityConnection entityCon = (context.Connection as EntityConnection);

    using (SqlConnection sqlCon = new SqlConnection(entityCon.StoreConnection.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query.ToTraceString(), sqlCon))
        {
            foreach (var param in query.Parameters)
            {
                cmd.Parameters.AddWithValue(param.Name, param.Value);

            }

            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd))
            {
                using (DataTable dataTable = new DataTable())
                {
                    dataAdapter.Fill(dataTable);

                    return dataTable;

                }

            }

        }

    }

}

上述代码“可用”，ToTraceString() 方法生成的 SQL 语句如下：

SELECT [Extent1].[ColumnName1] AS [ColumnName1], [Extent1].[ColumnName2] AS [ColumnName2] FROM [dbo].[TableName] AS [Extent1]

问题: SQL语句中的列名 (即columnName1和columnName2) 与对象属性的名称 (即PropertyName1和PropertyName2) 不相对应, 如果在query上调用ToList()或AsEnumerable()方法，将会创建出一个匿名对象. 如果SQL语句的列与匿名对象的属性按相同顺序排列，那么问题就不大...但是，这并不总是如此. 在IQueryable对象内部，必须有一种映射方式，能够将SQL语句的列名和结果匿名对象的属性名联系起来。请问有谁知道如何获取这个映射呢？

- HydroPowerDeveloper

---

你为什么想要将结果存储在 DataTable 中？ - Eranga

@Eranga 很好的问题！对于大多数意图和目的而言，将IQueryable对象转换为DataTable是毫无意义的。但是，我已经创建了一个可以将DataTables进行透视的透视类。我已经创建了一个适用于IEnumerables的透视类，但它相当依赖反射。我认为使用datatables可以获得更好的性能。 - HydroPowerDeveloper

Answer 1

我已经找到了解决问题的方法：

首先，你需要使用来自如何使Entity Framework将查询结果映射到匿名类型？的以下代码，将我的匿名对象属性的位置映射到SQL语句列位置：

public static Int32[] GetPropertyPositions(this ObjectQuery query)
{
    // get private ObjectQueryState ObjectQuery._state; 
    // of actual type internal class 
    //      System.Data.Objects.ELinq.ELinqQueryState 

    Object queryState = GetProperty(query, "QueryState");
    AssertNonNullAndOfType(queryState, "System.Data.Objects.ELinq.ELinqQueryState");

    // get protected ObjectQueryExecutionPlan ObjectQueryState._cachedPlan; 
    // of actual type internal sealed class 
    //      System.Data.Objects.Internal.ObjectQueryExecutionPlan 
    Object plan = GetField(queryState, "_cachedPlan");
    AssertNonNullAndOfType(plan, "System.Data.Objects.Internal.ObjectQueryExecutionPlan");

    // get internal readonly DbCommandDefinition ObjectQueryExecutionPlan.CommandDefinition; 
    // of actual type internal sealed class 
    //      System.Data.EntityClient.EntityCommandDefinition 
    Object commandDefinition = GetField(plan, "CommandDefinition");
    AssertNonNullAndOfType(commandDefinition, "System.Data.EntityClient.EntityCommandDefinition");

    // get private readonly IColumnMapGenerator EntityCommandDefinition._columnMapGenerator; 
    // of actual type private sealed class 
    //      System.Data.EntityClient.EntityCommandDefinition.ConstantColumnMapGenerator 
    Object columnMapGenerator = GetField(commandDefinition, "_columnMapGenerator");
    AssertNonNullAndOfType(columnMapGenerator, "System.Data.EntityClient.EntityCommandDefinition+ConstantColumnMapGenerator");

    // get private readonly ColumnMap ConstantColumnMapGenerator._columnMap; 
    // of actual type internal class 
    //      System.Data.Query.InternalTrees.SimpleCollectionColumnMap 
    Object columnMap = GetField(columnMapGenerator, "_columnMap");
    AssertNonNullAndOfType(columnMap, "System.Data.Query.InternalTrees.SimpleCollectionColumnMap");

    // get internal ColumnMap CollectionColumnMap.Element; 
    // of actual type internal class 
    //      System.Data.Query.InternalTrees.RecordColumnMap 
    Object columnMapElement = GetProperty(columnMap, "Element");
    AssertNonNullAndOfType(columnMapElement, "System.Data.Query.InternalTrees.RecordColumnMap");

    // get internal ColumnMap[] StructuredColumnMap.Properties; 
    // array of internal abstract class 
    //      System.Data.Query.InternalTrees.ColumnMap 
    Array columnMapProperties = GetProperty(columnMapElement, "Properties") as Array;
    AssertNonNullAndOfType(columnMapProperties, "System.Data.Query.InternalTrees.ColumnMap[]");

    Int32 n = columnMapProperties.Length;

    Int32[] propertyPositions = new Int32[n];

    for (Int32 i = 0; i < n; ++i)
    {
        // get value at index i in array 
        // of actual type internal class 
        //      System.Data.Query.InternalTrees.ScalarColumnMap 
        Object column = columnMapProperties.GetValue(i);
        AssertNonNullAndOfType(column, "System.Data.Query.InternalTrees.ScalarColumnMap");

        //string colName = (string)GetProp(column, "Name"); 
        // can be used for more advanced bingings 

        // get internal int ScalarColumnMap.ColumnPos; 
        Object columnPositionOfAProperty = GetProperty(column, "ColumnPos");
        AssertNonNullAndOfType(columnPositionOfAProperty, "System.Int32");

        propertyPositions[i] = (int)columnPositionOfAProperty;

    }

    return propertyPositions;

}

static object GetProperty(object obj, string propName)
{
    PropertyInfo prop = obj.GetType().GetProperty(propName, BindingFlags.NonPublic | BindingFlags.Instance);
    if (prop == null) throw EFChangedException();
    return prop.GetValue(obj, new object[0]);

}

static object GetField(object obj, string fieldName)
{
    FieldInfo field = obj.GetType().GetField(fieldName, BindingFlags.NonPublic | BindingFlags.Instance);
    if (field == null) throw EFChangedException();
    return field.GetValue(obj);

}

static void AssertNonNullAndOfType(object obj, string fullName)
{
    if (obj == null) throw EFChangedException();
    string typeFullName = obj.GetType().FullName;
    if (typeFullName != fullName) throw EFChangedException();

}

static InvalidOperationException EFChangedException()
{
    return new InvalidOperationException("Entity Framework internals has changed, please review and fix reflection code");

}

那么我可以将EntityToDatatable方法修改为以下内容：

public static DataTable EntityToDatatable(this IQueryable query)
{
    SqlConnection sqlConnection = null;
    SqlCommand sqlCommand = null;
    SqlDataAdapter sqlDataAdapter = null;
    DataTable dataTable = null;

    try
    {
        ObjectQuery objectQuery = (query as ObjectQuery);

        ObjectContext objectContext = objectQuery.Context;

        EntityConnection entityConnection = (objectContext.Connection as EntityConnection);

        sqlConnection = new SqlConnection(entityConnection.StoreConnection.ConnectionString);

        sqlCommand = new SqlCommand(objectQuery.ToTraceString(), sqlConnection);

        foreach (var parameter in objectQuery.Parameters)
        {
            sqlCommand.Parameters.AddWithValue(parameter.Name, parameter.Value);

        }

        sqlDataAdapter = new SqlDataAdapter(sqlCommand);

        dataTable = new DataTable();

        sqlDataAdapter.Fill(dataTable);

        // Get the mapping between the object property position and 
        // the SQL statment column position.
        Int32[] propertyPositions = objectQuery.GetPropertyPositions();

        // Create a column name to column position (ordinal) lookup.
        Dictionary<String, Int32> mapColumnNameToColumnPosition = new Dictionary<string, int>();

        // Populate the lookup.
        for (Int32 i = 0; i < propertyPositions.Length; ++i)
        {
            mapColumnNameToColumnPosition.Add(dataTable.Columns[propertyPositions[i]].ColumnName, i);

        }

        // Get the object's property information.
        PropertyInfo[] pi = query.GetType().GetGenericArguments()[0].GetProperties();

        // Iterate through the lookup and change the position of the datatable columns.
        // The order of the datatable columns will now correspond to the order of the object
        // properties.
        foreach (var map in mapColumnNameToColumnPosition)
        {
            // Change the column position.
            dataTable.Columns[map.Key].SetOrdinal(map.Value);

            // Change the column name.
            dataTable.Columns[map.Key].ColumnName = pi[map.Value].Name;

        }

        return dataTable;

    }
    catch (Exception ex)
    {
        // Something went wrong and we're going to raise an exception...we
        // might as well dispose of the datatable if it exists because it's
        // not going to be used.
        if (dataTable != null) dataTable.Dispose();

        throw new Exception("IQueryable to DataTable conversion error.", ex);

    }
    finally
    {
        // Do some cleanup on objects that are no longer needed.

        if (sqlDataAdapter != null) sqlDataAdapter.Dispose();

        if (sqlCommand != null) sqlCommand.Dispose();

        if (sqlConnection != null) sqlConnection.Dispose();

    }

}

- HydroPowerDeveloper