实体框架 - IQueryable to DataTable [英] Entity Framework - IQueryable to DataTable

问题描述

我正在尝试将 IQueryable 对象转换为 DataTable 。以下是一个我想转换为 DataTable 的查询示例：

I'm trying to convert an IQueryable object to a DataTable. Here's an example of a query that I would like to convert to a DataTable:

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

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

Please note the anonymous object that is created in the Select method and the names of the properties:

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

object to a DataTable ：

After Googling this issue, I came across the following code that converts an IQueryable object to a 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（）方法如下：

The code above "works" and the SQL statement from the ToTraceString() method is as follows:

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

问题： SQL语句的列名称（即 columnName1 和 columnName2 ）不符合名称的属性（即 PropertyName1 和 PropertyName2 ），如果在查询中调用ToList（）或 AsEnumerable（）方法。如果SQL语句列的顺序与匿名对象属性相同，则不会如此糟糕。但是，并不总是这样。。某处（我猜测在IQueryable对象内），必须有SQL语句列名与生成的匿名对象属性名之间的映射。

Problem: The column names of the SQL statement (i.e. columnName1 and columnName2) do not correspond to the names of the properties of the objects (i.e. PropertyName1 and PropertyName2) that would be materialized if a ToList() or AsEnumerable() method was called on the query. This wouldn't be so bad if the SQL statement columns were in the same order as the anonymous object properties...but, this is not always the case. Somewhere (I guess inside of the IQueryable object) there must be a mapping between the SQL statement column names and the resulting anonymous object property names.

有人知道如何获取在这个映射中？

Does anyone know how to get at this mapping?

推荐答案

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

I've managed to find a solution to my problem:

首先，您需要以下代码（从 Entity Framework如何管理映射查询结果到匿名类型？），它将我的匿名对象属性的位置映射到SQL语句列位置：

First, you need the following code (from How does Entity Framework manage mapping query result to anonymous type?) which maps the positions of my anonymous object properties to the SQL statement column position:

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 方法如下：

Then I can modify the EntityToDatatable method as follows:

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();

    }

}

这篇关于实体框架 - IQueryable to DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！