c#-4.0 – 实体框架 – 可以通过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 } 谷歌搜索此问题后,我遇到了以下代码,将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)不对应,如果调用了ToList()或AsEnumerable()方法,这些属性将被实现查询.如果SQL语句列与匿名对象属性的顺序相同,那就不会那么糟糕了……但是,情况并非总是这样.在某处(我猜在IQueryable对象内)必须在SQL语句列名和生成的匿名对象属性名之间进行映射. 有谁知道如何获得这个映射? 解决方法
我设法找到了解决问题的方法:
首先,您需要以下代码(从How does Entity Framework manage mapping query result to anonymous type?开始),它将我的匿名对象属性的位置映射到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(); } } (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |