c# - Entity Framework 代码优先 IQueryable 导航属性

Question

我在互联网上看到的大多数示例都将导航属性显示为 ICollection或直List执行。他们通常是 virtual , 启用延迟加载。

但是，当您访问此类属性时，它将在内存中加载整个集合，并且如果您在它之后有一个子查询(即 object.MyListProperty.Where(...) )，我注意到将为 MyListProperty 中的每个项目发出一个 SQL 查询。 .

我如何避免这种情况？我想要 where如果可能，要在 SQL 服务器上执行的 list 属性后面的子句。我可以使用 IQueryable导航属性？这种情况有什么最佳实践吗？

Answer 1

我对最佳实践的建议是完全禁用延迟加载。相反，强制调用者通过包含语句或使用投影急切地加载导航属性。

There are 3rd party products that support include with filters, as described in this post: How to filter include entities in entity framework, but in my experience this further complicates down-stream processing of the objects that are retrieved. If the entity object is loaded outside of method X, because method X can't know for sure if the navigation properties have been loaded with the correct filters, method X starts off by re-querying for the precise rows that it knows it needs.

using (var context = new MyDbContext())
{
    context.Configuration.LazyLoadingEnabled = false;

    // TODO: load your data
    ...
}

这样，只有在明确请求时才会加载记录。
当您想访问 IQueryable 时因此您可以推迟数据的加载，然后针对 DbContext 实例而不是从对象进行这些查询。

在这个例子中假设一个 Customer有成千上万的事务，所以我们根本不希望它们被急切或延迟加载。

using (var context = new MyDbContext())
{
    context.Configuration.LazyLoadingEnabled = false;

    var customer = context.Customers.First(x => x.Id == 123);
    ...
    // count the transactions in the last 30 days for this customer
    int customerId = customer.Id;  
    DateTime dateFrom = DateTime.Today.AddDays(-30)

    // different variations on the same query
    int transactionCount1 = context.Customers.Where(x => x.Id == customerId)
                                             .SelectMany(x => x.Transactions.Where(x => x.TransactionDate >= dateFrom))
                                             .Count();
    int transactionCount2 = context.Customers.Where(x => x.Id == customerId)
                                             .SelectMany(x => x.Transactions)
                                             .Where(x => x.TransactionDate >= dateFrom)
                                             .Count();
    int transactionCount3 = context.Transactions.Where(x => x.CustomerId == customerId)
                                                .Where(x => x.TransactionDate >= dateFrom)
                                                .Count();
}

很高兴您已经确定要使用 IQueryable<T>我们从 DbContext 访问它们直接，而不是从先前检索到的实例。