How does the Entity Framework control the mapping of query results to an anonymous type?

Question

Consider the following LINQ example to query an entity

from history in entities.foreignuserhistory select new { history.displayname, login=history.username, history.foreignuserid } 

ToTraceString() the return string is as follows:

 SELECT "Extent1"."foreignuserid" AS "foreignuserid", "Extent1"."displayname" AS "displayname", "Extent1"."username" AS "username" FROM "integration"."foreignuserhistory" AS "Extent1" 

The problem for me is that the columns come in a different order from the request and do not accept login aliases in the example. Where does the Entity Framework store mapping information for anonymous types?

Background: I'm going to expand the insert using the select operation using LINQ for the object for bulk operations.

Update: Inserting with select is not that complicated, except for an unknown column in the property matching algorithm. You can get the table and column names for the ObjectSet destination using metadata, build the INSERT INTO tableName (column_name1, …) sql statement, and then add a few ObjectQuery.ToTraceString SELECT statements. Then create a DbCommand with the resulting text using ((EntityConnection)ObjectContext.Connection).StoreConnection and fill in the command parameters from ObjectQuery . Therefore, the problem is to find the appropriate column order in the inserted and selected records.

Answer 1

Here is my decision to the end of the rank and file. It moves with reflection into the query caching plan that will exist after calling ToTraceString or executing the request to get what is called _columnMap . The column map contains ScalarColumnMap objects in the order of properties of anonymous objects and pointing to the corresponding column position with the ColumnPos property.

 using System; using System.Data.Objects; using System.Reflection; static class EFQueryUtils { public static int[] GetPropertyPositions(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[]"); int n = columnMapProperties.Length; int[] propertyPositions = new int[n]; for (int 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"); } } 

I think that some statements can be softened to check not the exact type, but the base type containing the necessary property.

Is there a solution without reflection?

Answer 2

As columns, aliases in a query do not matter, and none of them should order them. Entity Framework processes a new instance of your anonymous type with each result, as well as where you get the login type alias.

As a note, I think the Entity Framework may not work as you think. You cannot do select / insert in one operation, as you can use a regular SQL query. Entity Framework will make your choice, return the results, use these results to create new instances of your entities (or in your case an anonymous type), and then you will have to use each result to create a new instance of your target type, adding each of them to the context of the / object, and finally, call save changes in the context of the object / object. This will lead to a separate insert statement for each new object added.

If you want to do all this in one operation without creating an instance of a new object for each record, you need to either use a stored procedure that you map in your context, or execute a built-in SQL query using ObjectContext.ExecuteStoreCommand

UPDATE:. Based on your answers, what you really get is closer to metaprogramming, which relies more on your entity model than actually using an entity. I don’t know which version of EF you are using (EF 4.0? 4.1 w / code first and DbContext?), But I was very successful using the C # POCO template with EF 4.0 (the POCO template is to download from the online gallery gallery). It uses the T4 template to create POCO classes from the .edmx data model. In your T4 template, you can add methods to your context that will essentially call ExecuteStoreCommand , but the difference is that you can generate a query that will be executed based on your data model. Thus, at any time when your data model changes, your request will remain in sync with the changes.

Answer 3

Updated reflection of this for EF 4.4 (5-RC)

full post at http://imaginarydevelopment.blogspot.com/2012/06/compose-complex-inserts-from-select.html

using this functionality / logic to perform bulk insertion from a selection with the provided parameters

 int Insert<T>(IQueryable query,IQueryable<T> targetSet) { var oQuery=(ObjectQuery)this.QueryProvider.CreateQuery(query.Expression); var sql=oQuery.ToTraceString(); var propertyPositions = GetPropertyPositions(oQuery); var targetSql=((ObjectQuery)targetSet).ToTraceString(); var queryParams=oQuery.Parameters.ToArray(); System.Diagnostics.Debug.Assert(targetSql.StartsWith("SELECT")); var queryProperties=query.ElementType.GetProperties(); var selectParams=sql.Substring(0,sql.IndexOf("FROM ")); var selectAliases=Regex.Matches(selectParams,@"\sAS \[([a-zA-Z0-9_]+)\]").Cast<Match>().Select(m=>m.Groups[1].Value).ToArray(); var from=targetSql.Substring(targetSql.LastIndexOf("FROM [")+("FROM [".Length-1)); var fromAlias=from.Substring(from.LastIndexOf("AS ")+"AS ".Length); var target=targetSql.Substring(0,targetSql.LastIndexOf("FROM [")); target=target.Replace("SELECT","INSERT INTO "+from+" (")+")"; target=target.Replace(fromAlias+".",string.Empty); target=Regex.Replace(target,@"\sAS \[[a-zA-z0-9]+\]",string.Empty); var insertParams=target.Substring(target.IndexOf('(')); target = target.Substring(0, target.IndexOf('(')); var names=Regex.Matches(insertParams,@"\[([a-zA-Z0-9]+)\]"); var remaining=names.Cast<Match>().Select(m=>m.Groups[1].Value).Where(m=>queryProperties.Select(qp=>qp.Name).Contains(m)).ToArray(); //scrape out items that the anonymous select doesn't include a name/value for //selectAliases[propertyPositions[10]] //remaining[10] var insertParamsOrdered = remaining.Select((s, i) => new { Position = propertyPositions[i], s }) .OrderBy(o => o.Position).Select(x => xs).ToArray(); var insertParamsDelimited = insertParamsOrdered.Aggregate((s1, s2) => s1 + "," + s2); var commandText = target + "(" + insertParamsDelimited + ")" + sql; var result=this.ExecuteStoreCommand(commandText,queryParams.Select(qp=>new System.Data.SqlClient.SqlParameter{ ParameterName=qp.Name, Value=qp.Value}).ToArray()); return result; }