.net - Entity Framework 显式加载批量

Question

我需要加载一堆数据来生成报告。图中涉及大约 8 或 9 种不同的实体类型。

如果我打电话Include为了包含我需要的所有数据，结果查询非常复杂(左联接、联合、case 语句)，执行时间大约为 600 秒。

如果我在生成报告时让延迟加载为我延迟加载数据，则创建报告大约需要 180 秒。更好，但仍然不能接受。

如果我做一个

"load the next entity type based on the ids of the related entity type"

方法(有点像 LLBLGen，如果你熟悉的话)，我可以在大约 3 秒内获得我需要的所有数据。

关于如何做到这一点的任何建议？

基本上我想做这样的事情:

var invoices = objectContext.Invoices.Where(...reportCriteria...).ToArray();

objectContext.LoadProperty(invoices, "InvoiceReceivables");

objectContext.LoadProperty(invoices.SelectMany(i => i.InvoiceReceivables), "Adjustments");

objectContext.LoadProperty(invoices.SelectMany(i => i.InvoiceReceivables.SelectMany(ir => ir.Adjustments)), "AdjustmentComments")

... and so on

但是 LoadProperty 仅适用于单个实体，不适用于集合。

除了自己进行查询和构建对象图之外，还有其他方法吗？

Answer 1

尽管 E.J. 的回答非常有效，并且可能是一种更好、更高效的方法，但我们现在确实没有足够的带宽来重组报告。我把这个放在一起，它似乎可以解决问题。也许它对其他人有一些好处......为简洁起见，省略了一些琐碎的辅助方法。

用法:

Q.GetQueryableFactory(objectContext).Load(invoices, 
  i => i.InvoiceReceivables.SelectMany(ir => ir.Adjustments.SelectMany(
      a => a.AdjustmentComments)))


public static class Q
{
    /// <summary>
    /// Gets a queryable factory that returns a queryable for a specific entity type.
    /// </summary>
    /// <param name="objectContext">The object context.</param>
    /// <returns></returns>
    public static Func<Type, IQueryable> GetQueryableFactory(object objectContext)
    {
        var queryablePropertiesByType = objectContext.GetType().GetProperties().Where(p => p.GetIndexParameters().Length == 0 && p.PropertyType.IsGenericTypeFor(typeof(IQueryable<>)))
            .ToDictionary(p => p.PropertyType.FindElementType());

        return t =>
                   {
                       PropertyInfo property;
                       if (!queryablePropertiesByType.TryGetValue(t, out property))
                       {
                           property = queryablePropertiesByType.Values.FirstOrDefault(p => p.PropertyType.FindElementType().IsAssignableFrom(t))
                               .EnsureNotDefault("Could not find queryable for entity type {0}.".FormatWith(t.Name));

                           var queryable = property.GetValue(objectContext, null);

                           return (IQueryable)typeof(System.Linq.Queryable).GetMethod("OfType").MakeGenericMethod(t).Invoke(null, new[] { queryable });
                       }

                       return (IQueryable)property.GetValue(objectContext, null);
                   };
    }

    /// <summary>
    /// Loads the target along the specified path, using the provided queryable factory.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="queryableFactory">The queryable factory.</param>
    /// <param name="target">The target.</param>
    /// <param name="path">The path.</param>
    public static void Load<T>(this Func<Type, IQueryable> queryableFactory, T target, Expression<Func<T, object>> path)
    {
        queryableFactory.Load(target, path.AsEnumerable().Reverse().OfType<MemberExpression>().Select(m => m.Member.Name).Join("."));
    }

    /// <summary>
    /// Loads the target along the specified path, using the provided queryable factory.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="queryableFactory">The queryable factory.</param>
    /// <param name="target">The target.</param>
    /// <param name="path">The path.</param>
    public static void Load<T>(this Func<Type, IQueryable> queryableFactory, IEnumerable<T> target, Expression<Func<T, object>> path)
    {
        queryableFactory.Load(target, path.ToMemberAccessStrings().First());
    }

    /// <summary>
    /// Loads the target along the specified path, using the provided queryable factory.
    /// </summary>
    /// <param name="queryableFactory">The queryable factory.</param>
    /// <param name="target">The target.</param>
    /// <param name="path">The path.</param>
    public static void Load(this Func<Type, IQueryable> queryableFactory, object target, string path)
    {
        foreach (var pathPart in path.Split('.'))
        {
            var property = (target.GetType().FindElementType() ?? target.GetType()).GetProperty(pathPart);

            LoadProperty(queryableFactory(property.PropertyType.FindElementType() ?? property.PropertyType), target, pathPart);

            if (target is IEnumerable)
            {
                // select elements along path target.Select(i => i.Part).ToArray()
                target = target.CastTo<IEnumerable>().AsQueryable().Select(pathPart).ToInferredElementTypeArray();

                var propertyElementType = property.PropertyType.FindElementType();
                if (propertyElementType != null)
                {
                    target = target.CastTo<object[]>().SelectMany(i => i.CastTo<IEnumerable>().ToObjectArray()).ToArray(propertyElementType);
                }
            }
            else
            {
                target = property.GetValue(target, null);
            }
        }
    }

    /// <summary>
    /// Loads the property on the target using the queryable source.
    /// </summary>
    /// <param name="source">The source.</param>
    /// <param name="target">The target.</param>
    /// <param name="targetProperty">The target property.</param>
    /// <param name="targetIdProperty">The target id property.</param>
    /// <param name="sourceProperty">The source property.</param>
    public static void LoadProperty(this IQueryable source, object target, string targetProperty, string targetIdProperty = null, string sourceProperty = null)
    {
        var targetType = target.GetType();
        targetType = targetType.FindElementType() ?? (targetType.Assembly.IsDynamic && targetType.BaseType != null ? targetType.BaseType : targetType);

        // find the property on the source so we can do queryable.Where(i => i.???)
        var sourceType = source.ElementType;
        PropertyInfo sourcePropertyInfo;
        if (sourceProperty == null)
        {
            sourcePropertyInfo = sourceType.GetProperty(targetType.Name + "Id") ?? sourceType.GetProperty(targetType.Name + "ID") ?? sourceType.GetProperty("Id") ?? sourceType.GetProperty("ID");
        }
        else
        {
            sourcePropertyInfo = sourceType.GetProperty(sourceProperty);
        }

        if (sourcePropertyInfo == null || sourcePropertyInfo.GetIndexParameters().Length != 0) throw new InvalidOperationException("Could not resolve id property on source {0}.".FormatWith(source.ElementType.Name));


        // find the property on the target so we can find the relevant source objects via queryable.Where(i => i.Property == ???)
        PropertyInfo targetIdPropertyInfo;
        if (targetIdProperty == null)
        {
            targetIdPropertyInfo = targetType.GetProperty(targetProperty + "Id") ?? targetType.GetProperty(targetProperty + "ID") ?? targetType.GetProperty("Id") ?? targetType.GetProperty("Id").EnsureNotDefault("Could not resolve id property to use on {0}.".FormatWith(targetType.Name));
        }
        else
        {
            targetIdPropertyInfo = targetType.GetProperty(targetIdProperty);
        }

        if (targetIdPropertyInfo == null || targetIdPropertyInfo.GetIndexParameters().Length != 0) throw new InvalidOperationException("Could not resolve id property for {0} on target {1}.".FormatWith(targetProperty, targetType.Name));


        var targetPropertyInfo = targetType.GetProperty(targetProperty);
        if (targetPropertyInfo == null || targetPropertyInfo.GetIndexParameters().Length != 0) throw new InvalidOperationException("Could not find property {0} on target type {1}.".FormatWith(targetProperty, target.GetType()));

        // go get the data and set the results.
        if (target is IEnumerable)
        {
            // filter to only non loaded targets
            var nullOrEmptyPredicate = "{0} == null".FormatWith(targetPropertyInfo.Name);
            if (targetPropertyInfo.PropertyType.FindElementType() != null) nullOrEmptyPredicate += " or {0}.Count = 0".FormatWith(targetPropertyInfo.Name);
            target = target.CastTo<IEnumerable>().AsQueryable().Where(nullOrEmptyPredicate).ToInferredElementTypeArray();

            var ids = target.CastTo<IEnumerable>().OfType<object>().Select(i => targetIdPropertyInfo.GetValue(i, null)).WhereNotDefault().Distinct().ToArray();

            if (!ids.Any()) return;

            var predicate = ids.Select((id, index) => "{0} = @{1}".FormatWith(sourcePropertyInfo.Name, index)).Join(" or ");
            // get the results in one shot
            var results = source.Where(predicate, ids.ToArray()).ToInferredElementTypeArray().AsQueryable();

            foreach (var targetItem in target.CastTo<IEnumerable>())
            {
                SetResultsOnTarget(results, targetItem, sourcePropertyInfo, targetIdPropertyInfo, targetPropertyInfo);
            }
        }
        else
        {
            // only fetch if not loaded already
            var value = targetPropertyInfo.GetValue(target, null);
            if (value == null || value.As<IEnumerable>().IfNotNull(e => e.IsNullOrEmpty()))
            {
                SetResultsOnTarget(source, target, sourcePropertyInfo, targetIdPropertyInfo, targetPropertyInfo);
            }
        }

    }

    /// <summary>
    /// Sets the results on an individual target entity.
    /// </summary>
    /// <param name="source">The source.</param>
    /// <param name="target">The target.</param>
    /// <param name="sourcePropertyInfo">The source property info.</param>
    /// <param name="targetIdPropertyInfo">The target id property info.</param>
    /// <param name="targetPropertyInfo">The target property info.</param>
    private static void SetResultsOnTarget(IQueryable source, object target, PropertyInfo sourcePropertyInfo, PropertyInfo targetIdPropertyInfo, PropertyInfo targetPropertyInfo)
    {
        var id = targetIdPropertyInfo.GetValue(target, null);

        var results = source.Where("{0} = @0".FormatWith(sourcePropertyInfo.Name), id).As<IEnumerable>().OfType<object>().ToArray();

        var targetPropertyElementType = targetPropertyInfo.PropertyType.FindElementType();
        if (targetPropertyElementType != null)
        {
            // add all results
            object collection = targetPropertyInfo.GetValue(target, null);

            if (collection == null)
            {
                // instantiate new collection, otherwise use existing
                collection = Activator.CreateInstance(typeof(List<>).MakeGenericType(targetPropertyElementType));
                targetPropertyInfo.SetValue(target, collection, null);
            }

            var addMethod = collection.GetType().GetMethods().FirstOrDefault(m => m.Name == "Add" && m.GetParameters().Length == 1 && m.GetParameters()[0].ParameterType.IsAssignableFrom(targetPropertyElementType)).EnsureNotDefault("Could not find add method for collection type.");

            foreach (var result in results)
            {
                if (!Enumerable.Contains((dynamic)collection, result)) addMethod.Invoke(collection, new[] { result });
            }
        }
        else
        {
            targetPropertyInfo.SetValue(target, results.FirstOrDefault(), null);
        }
    }
}