Linq to Entities，在查询表达式中注入联接 [英] Linq to Entities, Inject Join Inside Query Expression

问题描述

我正在尝试提高某些LINQ查询的性能，有一件小事情有很大的改进空间:联接. 几乎我所有的查询都有一些用于过滤结果的联接，但未选择它们作为结果.这些过滤条件是可选的...

I'm trying to improve the performance for some of our LINQ queries and there is one little thing that has a lot of space for improvement: joins. Almost all my queries have some join that is used to filter the result, but are not selected into de result. And these filtering conditions are optional...

我今天所拥有的类似于:

What I have today is something like:

var q = from t1 in context.Set<T1>() 
    where t1.mandatoryfilter >= 0
    select t1;

if (useFilter) 
{
    var q2 = from t1 in q
        from t2 in context.Set<T2>().Where(t2 => t2.fk == t1.pk).DefaultIfEmpty
        where t2.filterProperty == filterValue
        select t1;

    if (useFilter2) 
    {
        [...]

        return q3.ToList();
    }

    return q2.ToList();
} 
else 
{
    if (useFilter2) 
    {
        [...]

        return q2.ToList();
    }

    return q.ToList();
}

这将生成查询的投影.并且，根据过滤器的复杂性/数量，它将生成越来越多的投影，并且如果我需要组合一些过滤器，则需要嵌套代码.结果查询的大小可能会变得太大，以至于无法通过Internet发送(实际上不是太大的XD，但它会导致性能问题)，并且代码本身也很难维护.

This will generate a projection of the query. And depending on the complexity/quantity of the filters it will generate more and more projections and the code need to be nested if I need to combine some of the filters. The resulting query can grow to a size where it gets too large to send over the internet (not actually too large XD but it becomes a performance issue) and the code itself gets hard to maintain.

我知道我可以改回字符串SQL ...但这不是一个非常优雅的解决方案吗?

I know I could just change back to string SQL... but that is not a very elegant solution is it?

我想知道是否有一种方法可以注入联接，并且过滤器直接执行表达式树，因此提供程序将不会生成投影，并且代码将是线性且简单的.

I'd like to know if there is a way to inject the join and the filter directly do the Expression Tree, so the provider would not generate the projections and the code would be linear and simple.

ps .:非常重要的信息，我正在使用EF6，Linq-to-Entities，默认SqlClient.

ps.: very important information, I'm using EF6, Linq-to-Entities, default SqlClient.

谢谢大家.

推荐答案

如果您使用导航属性，则几乎不需要连接，并且可以将大多数过滤条件用作单个表达式.

If you use navigation properties you hardly ever need joins and you can apply most filter conditions as a single expression.

让我们假设您的T1实体具有导航属性T2.过滤器表达式将如下所示:

Let's suppose your T1 entity has a navigation property T2. The filter expression will look like this:

q = q.Where(q.T2.filterProperty1 == filterProperty == filterValue);

您还可以在此处看到，您可以将表达式追加到现有查询q.

You also see here that you can just append an expression to the existing query q.

现在它变成一种常用的模式，以向IQueryable添加过滤条件:

Now it turns into a commonly applied pattern to add filter conditions to an IQueryable:

IQueryable<T1> query = from t1 in context.Set<T1>() 
                       where t1.mandatoryfilter >= 0
                       select t1;

if (filterValue2 != null)
    query = query.Where(t => t.T2.filterProperty == filterValue2);
if (filterValue3 != null)
    query = query.Where(t => t.T3.filterProperty == filterValue3);
if (filterValue4 != null)
    query = query.Where(t => t.T4.filterProperty == filterValue4);

(请注意，应将query强制转换为IQueryable，否则分配query = query....不会编译.)

(Note that query should be cast to IQueryable, otherwise the assignments query = query.... don't compile.)

现在您甚至可以将过滤器应用于1:n关联

And now you can even apply filters to 1:n associations

if (filterValue5 != null)
    query = query.Where(t => t.T5s.Any(t5 => t5.filterProperty == filterValue5));

如果使用join进行过滤，则无法执行此操作，因为它将使结果集相乘.

You can't do this if you use join for filtering, because it would multiply the result set.

这篇关于Linq to Entities，在查询表达式中注入联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！