EF ToTraceString SQL生成结果列的顺序

本文关键字：顺序 结果 ToTraceString SQL EF | 更新日期: 2023-09-27 18:25:07

我的问题是，我是否可以预测或选择sql生成通过ToTraceString()返回的列的确切顺序。

我使用ToTraceString()作为IQueryable来获取结果SQL命令，然后将结果直接插入数据库表中。

所以，我需要生成的SQL与我的表结构保持一致。。。

string insertQuery = string.Format("INSERT INTO {0} {1}", sqlTableName ((System.Data.Objects.ObjectQuery<TRow>)results).ToTraceString());
Context.ExecuteStoreCommand(string.Format("TRUNCATE TABLE {0}", sqlTableName));
Context.ExecuteStoreCommand(insertQuery);

results = IQueryable<Row>，其中Row是具有与表列相同属性的类型

我选择直接插入到表中，因为我认为在Web服务器上获取ToList（）枚举没有意义，只是通过某种大容量插入（EF目前不支持…）将其发送回SQL。我的查询返回了相当多的行，我不想使用存储过程。

希望我能理解。。。感谢

EF ToTraceString SQL生成结果列的顺序

我遇到了这个问题，但这里的答案仍然需要大量的工作才能开始。我使用了实体框架如何管理将查询结果映射到匿名类型的部分？获取顺序并返回名称，然后进行简单的解析以提取字段名称。

我做了一个扩展方法，把所有东西都放在一起：

public static string ToWrappedString(this ObjectQuery query, out ObjectParameterCollection parameters)
{
    var trace = query.ToTraceString();
    parameters = query.Parameters;
    var positions = query.GetPropertyPositions();
    // the query should be SELECT'n
    //  Column AS NNN
    //  FROM
    // so we regex this out
    var regex = new Regex("^SELECT(?<columns>.*?)FROM", RegexOptions.Multiline);
    var result = regex.Match(trace.Replace(Environment.NewLine, ""));
    var cols = result.Groups["columns"];
    // then we have the columns so split to get each
    const string As = " AS ";
    var colNames = cols.Value.Split(',').Select(a => a.Substring(a.IndexOf(As, StringComparison.InvariantCulture) + As.Length)).ToArray();

    var wrapped = "SELECT " + String.Join(Environment.NewLine + ", ", colNames.Select((a, i) => string.Format("{0}{1} [{2}]", a, As, positions[i]))) + " FROM (" + trace
                  + ") WrappedQuery ";
    return wrapped;
}

这是来自另一个链接的代码，更新为EF6内部，并按列顺序而不是索引返回名称。

public static string[] 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.Entity.Core.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.Entity.Core.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.Entity.Core.EntityClient.Internal.EntityCommandDefinition");
    // get private readonly IColumnMapGenerator EntityCommandDefinition._columnMapGenerator;
    // of actual type private sealed class
    //      System.Data.EntityClient.EntityCommandDefinition.ConstantColumnMapGenerator
    var columnMapGeneratorArray = GetField(commandDefinition, "_columnMapGenerators") as object[];
    AssertNonNullAndOfType(columnMapGeneratorArray, "System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition+IColumnMapGenerator[]");
    var columnMapGenerator = columnMapGeneratorArray[0];
    // 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.Entity.Core.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.Entity.Core.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.Entity.Core.Query.InternalTrees.ColumnMap[]");
    int n = columnMapProperties.Length;
    string[] propertyPositions = new string[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.Entity.Core.Query.InternalTrees.ScalarColumnMap");
        string colName = (string)GetProperty(column, "Name");
        // can be used for more advanced bingings
        // get internal int ScalarColumnMap.ColumnPos;
        object columnPositionOfAProperty = GetProperty(column, "ColumnPos");
        AssertNonNullAndOfType(columnPositionOfAProperty, "System.Int32");
        propertyPositions[(int)columnPositionOfAProperty] = colName;
    }
    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");
}

这个问题的公认答案包含两个链接，描述了如何计算实体类型的各种属性在ToTraceString（）生成的SQL中的显示顺序。有了这些信息，您可以对原始SQL进行一些简单的解析/重组，用属性列名替换EF使用的奇怪列名（例如C1、C2等）。然后，您可以将生成的SQL封装在子查询中，该子查询按您想要的顺序选择相关列：

SELECT prop1, prop2
FROM
(
    // the result of ToTraceString(), with EF's generated column names replaced by the property names of the query type
) x
相关文章：