实体框架 - IQueryable到DataTable [英] Entity Framework - IQueryable to DataTable
本文介绍了实体框架 - IQueryable到DataTable的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图将 IQueryable 对象转换为 DataTable 。以下是我想要转换为 DataTable 的查询示例：
var query = DbContext.SomeObjectSet.Select（x => new {PropertyName1 = x.ColumnName1，PropertyName2 = x.ColumnName2}）;
请注意在 Select 方法和属性名称：
new {PropertyName1 = x.ColumnName1，PropertyName2 = x.ColumnName2} $在Googling这个问题后，我遇到了下面的代码转换 IQueryable / code>对象到 DataTable ： public static DataTable EntityToDatatable（this IQueryable result）
{
ObjectQuery query =（result as ObjectQuery）;
ObjectContext context = query.Context;
EntityConnection entityCon =（context.Connection as EntityConnection）;
使用（SqlConnection sqlCon = new SqlConnection（entityCon.StoreConnection.ConnectionString））
{
using（SqlCommand cmd = new SqlCommand（query.ToTraceString（），sqlCon））
{
foreach（query.Parameters中的var param）
{
cmd.Parameters.AddWithValue（param.Name，param.Value）;
}
使用（SqlDataAdapter dataAdapter = new SqlDataAdapter（cmd））
{
using（DataTable dataTable = new DataTable（））
{
dataAdapter.Fill（dataTable）;
return dataTable;
}
}
}
}
}
上面的代码works和SQL语句从 ToTraceString（）方法如下：
SELECT [Extent1]。[ColumnName1] AS [ColumnName1]，[Extent1]。[ColumnName2 ] AS [ColumnName2] FROM [dbo]。[TableName] AS [Extent1]
： SQL语句的列名称（即 columnName1 和 columnName2 ）不与名称属性的属性（即 PropertyName1 和 PropertyName2 在查询上调用了ToList（）
或 AsEnumerable（）如果SQL语句列与匿名对象属性的顺序相同，但这不是那么糟糕，但这不总是。在某处（我猜在IQueryable对象内部）必须在SQL语句列名称和生成的匿名对象属性名称之间有一个映射。
有人知道如何获取在这个映射？
解决方案我设法找到一个解决我的问题：
首先，您需要以下代码（从 Entity Framework如何管理将查询结果映射到匿名类型？），它将我的匿名对象属性的位置映射到SQL语句列位置：
public static Int32 [] GetPropertyPositions（this ObjectQuery query）
{
// get private ObjectQueryState ObjectQuery._state;
//实际类型的内部类
// System.Data.Objects.ELinq.ELinqQueryState
对象queryState = GetProperty（query，QueryState）;
AssertNonNullAndOfType（queryState，System.Data.Objects.ELinq.ELinqQueryState）;
//获得protected ObjectQueryExecutionPlan ObjectQueryState._cachedPlan;
//实际类型的内部密封类
// System.Data.Objects.Internal.ObjectQueryExecutionPlan
对象计划= GetField（queryState，_cachedPlan）;
AssertNonNullAndOfType（plan，System.Data.Objects.Internal.ObjectQueryExecutionPlan）;
//获取内部readonly DbCommandDefinition ObjectQueryExecutionPlan.CommandDefinition;
//实际类型的内部密封类
// System.Data.EntityClient.EntityCommandDefinition
对象commandDefinition = GetField（plan，CommandDefinition）;
AssertNonNullAndOfType（commandDefinition，System.Data.EntityClient.EntityCommandDefinition）;
//获取私有的readonly IColumnMapGenerator EntityCommandDefinition._columnMapGenerator;
//实际类型的私有密封类
// System.Data.EntityClient.EntityCommandDefinition.ConstantColumnMapGenerator
对象columnMapGenerator = GetField（commandDefinition，_columnMapGenerator）;
AssertNonNullAndOfType（columnMapGenerator，System.Data.EntityClient.EntityCommandDefinition + ConstantColumnMapGenerator）;
//获取私有只读ColumnMap ConstantColumnMapGenerator._columnMap;
//实际类型的内部类
// System.Data.Query.InternalTrees.SimpleCollectionColumnMap
对象columnMap = GetField（columnMapGenerator，_columnMap）;
AssertNonNullAndOfType（columnMap，System.Data.Query.InternalTrees.SimpleCollectionColumnMap）;
//获取内部ColumnMap CollectionColumnMap.Element;
//实际类型的内部类
// System.Data.Query.InternalTrees.RecordColumnMap
对象columnMapElement = GetProperty（columnMap，Element）;
AssertNonNullAndOfType（columnMapElement，System.Data.Query.InternalTrees.RecordColumnMap）;
//获取内部ColumnMap [] StructuredColumnMap.Properties;
//内部抽象类的数组
// System.Data.Query.InternalTrees.ColumnMap
数组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
//在数组中的索引i获取值
//实际类型内部类
// System.Data.Query.InternalTrees.ScalarColumnMap
对象column = columnMapProperties.GetValue（i）;
AssertNonNullAndOfType（column，System.Data.Query.InternalTrees.ScalarColumnMap）;
// string colName =（string）GetProp（column，Name）;
//可用于更高级的绑定
//获取内部int ScalarColumnMap.ColumnPos;
对象columnPositionOfAProperty = GetProperty（column，ColumnPos）;
AssertNonNullAndOfType（columnPositionOfAProperty，System.Int32）;
PropertyPositions [i] =（int）columnPositionOfAProperty;
}
return propertyPositions;
}
静态对象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]）;
}
静态对象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内部已更改，请检查并修复反射代码） ;
}
然后我可以修改 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（objectQuery.Parameters中的var参数）
{
sqlCommand.Parameters.AddWithValue（parameter.Name，parameter.Value）;
}
sqlDataAdapter = new SqlDataAdapter（sqlCommand）;
dataTable = new DataTable（）;
sqlDataAdapter.Fill（dataTable）;
//获取对象属性位置和
// SQL statment列位置之间的映射。
Int32 [] propertyPositions = objectQuery.GetPropertyPositions（）;
//创建列名称到列位置（顺序）查找。
字典< String，Int32> mapColumnNameToColumnPosition = new Dictionary< string，int>（）;
//填充查找。
for（Int32 i = 0; i< propertyPositions.Length; ++ i）
{
mapColumnNameToColumnPosition.Add（dataTable.Columns [propertyPositions [i]]。ColumnName，i）;
}
//获取对象的属性信息。
PropertyInfo [] pi = query.GetType（）。GetGenericArguments（）[0] .GetProperties（）;
//通过查找迭代并更改数据类型列的位置。
//数据类型列的顺序现在对应于对象
//属性的顺序。
foreach（mapColumnNameToColumnPosition中的变量映射）
{
//更改列位置。
dataTable.Columns [map.Key] .SetOrdinal（map.Value）;
//更改列名称。
dataTable.Columns [map.Key] .ColumnName = pi [map.Value] .Name;
}
返回dataTable;
}
catch（Exception ex）
{
//出错了，我们要引发异常... we
/ /可以处理datatable，如果它存在，因为它是
//不会被使用。
if（dataTable！= null）dataTable.Dispose（）;
throw new Exception（IQueryable to DataTable conversion error。，ex）;
}
finally
{
//对不再需要的对象进行清理。
if（sqlDataAdapter！= null）sqlDataAdapter.Dispose（）;
if（sqlCommand！= null）sqlCommand.Dispose（）;
if（sqlConnection！= null）sqlConnection.Dispose（）;
}
}
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 }
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;
}
}
}
}
}
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]
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:
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");
}
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到DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
