C#Linq:将多个.Where()与* OR *子句组合 [英] C# Linq: Combine multiple .Where() with an *OR* clause

问题描述

我一直在搜索有关当前问题的很多信息，但找不到解决该问题的真正答案.

I have been searching a lot about my current problem but I could not find a real answer to solve that issue.

我正在尝试构建一个生成以下SQL的LINQ查询:

I am trying to build a LINQ Query that produces the following SQL:

SELECT * FROM TABLE WHERE (Field1 = X, Field2 = Y ... ) or (Field3 = Z)

通常情况下，我会这样做:

In a normal situation I would just do this:

Object.Where(c => (c.Field1 == X && c.Field2 == Y) || (c.Field3 == Z))

我无法使用这种方法，因为查询是通过使用多个 .Where()调用构建的.

I cannot use this approach because the query is build by using multiple .Where() calls.

举个例子:

// This is a short example, the real world situation has 20 fields to check and they are all connected with an AND.
if (model.Field1.HasValue) 
{
    Query = Query.Where(c => c.Field1 == X)
}

if (model.Field2.HasValue) 
{
    Query = Query.Where(c => c.Field2 == X)
}

[...] like 20 more of these .Where() calls.

那对我来说就是这么复杂.所有这些 .Where()调用都在构建一个与 AND 相关联的Linq查询，这很好.

and that is how it gets complicated for me. All these .Where() calls are building a Linq Query that is connected with AND, which is fine.

如何让它们使用括号执行并现在使用API​​添加简单的 OR ?

How do I let them execute with Parenthese and add a simple OR now using the API?

有没有一种方法可以将谓词保存在某些变量中，这样我就可以进行以下操作:

Is there a way to save the predicate in some variables so I can make something like:

Query = Query.Where(c => previousPredicates || c.Field3 == X)

或如何解决该问题?

我认为必须为该特殊问题找到一个好的解决方案，我不是唯一需要它的人，但是我绝对不确定如何实现它.

I think there must be a good solution for that particual problem and I am not the only one who needs it, but I am absolute unsure how to achieve it.

P.S:我真的不能删除多个 .Where()调用，并且直接编写SQL都不可行.

P.S: I can't really remove the multiple .Where() calls and writing direct SQL is neither an option.

编辑StackOverflow要我说为什么我的问题与其他问题有所不同.好吧，这是关于 Parentheses 的问题.我不想将所有 .Where()与单个OR子句连接，我想让它们保留 AND 并添加另一个 OR 子句而所有 AND 查询都用括号括起来.

EDIT StackOverflow wants me to say why my question is different from others. Well, the thing is about Parentheses. I do not want to connect all .Where() with a single OR clause, I want to leave them with AND and add another OR clause while all the AND queries are being parenthesied.

推荐答案

如果要以编程方式构建查询并使其在SQL Server上执行，而不是获取所有记录并在内存中进行查询，则需要使用一组表达式 Expression 类上的静态方法，并使用这些方法构建查询.在您的示例中:

If you want to build your query programmatically and have it execute on your SQL server instead of fetching all records and querying in memory, you need to use the set of static methods on the Expression class and build your query using those. In your example:

public class Query // this will contain your 20 fields you want to check against
{
    public int? Field1; public int? Field2; public int? Field3; public int Field4;
}

public class QueriedObject // this is the object representing the database table you're querying
{
    public int QueriedField;
}

public class Program
{
    public static void Main()
    {
        var queryable = new List<QueriedObject>().AsQueryable();
        var query = new Query { Field2 = 1, Field3 = 4, Field4 = 2 };

        // this represents the argument to your lambda expression
        var parameter = Expression.Parameter(typeof(QueriedObject), "qo");

        // this is the "qo.QueriedField" part of the resulting expression - we'll use it several times later
        var memberAccess = Expression.Field(parameter, "QueriedField");

        // start with a 1 == 1 comparison for easier building - 
        // you can just add further &&s to it without checking if it's the first in the chain
        var expr = Expression.Equal(Expression.Constant(1), Expression.Constant(1));

        // doesn't trigger, so you still have 1 == 1
        if (query.Field1.HasValue)
        {
            expr = Expression.AndAlso(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field1.Value)));
        }
        // 1 == 1 && qo.QueriedField == 1
        if (query.Field2.HasValue)
        {
            expr = Expression.AndAlso(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field2.Value)));
        }
        // 1 == 1 && qo.QueriedField == 1 && qo.QueriedField == 4
        if (query.Field3.HasValue)
        {
            expr = Expression.AndAlso(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field3.Value)));
        }

        // (1 == 1 && qo.QueriedField == 1 && qo.QueriedField == 4) || qo.QueriedField == 2
        expr = Expression.OrElse(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field4)));

        // now, we combine the lambda body with the parameter to create a lambda expression, which can be cast to Expression<Func<X, bool>>
        var lambda = (Expression<Func<QueriedObject, bool>>) Expression.Lambda(expr, parameter);

        // you can now do this, and the Where will be translated to an SQL query just as if you've written the expression manually
        var result = queryable.Where(lambda);       
    }
}

这篇关于C#Linq:将多个.Where()与* OR *子句组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！