将LINQ-to-SQL谓词组合到一个谓词中 [英] Compose LINQ-to-SQL predicates into a single predicate

问题描述

（更早的问题，递归地（？）将LINQ谓词组合成一个单一的谓词与此类似，但我实际上提出了错误的问题......那里的解决方案满足了所提出的问题，但实际上并不是我所需要的，尽管它们是不同的。 / p>

给出以下搜索文本：

 keyword1 keyword2 ... keywordN
  

我想结束以下SQL：

  SELECT [columns] FROM Customer 
 WHERE（
 Customer.Forenames LIKE'％keyword1％'
 OR 
 Customer .Forenames LIKE'％keyword2％'
或
 ... 
或
 Customer.Forenames LIKE'％keywordN％'
）AND（
 Customer .Surname LIKE'％keyword1％'
或
 Customer.Surname L. IKE'％keyword2％'
或
 .... 
或
 Customer.Surname LIKE'％keywordN％'
）
  

实际上，我们将搜索文本分割为空格，修剪每个标记，根据每个标记构造一个多部分的OR子句，然后AND'ing这些子句在一起。

我在Linq-to-SQL中这样做，我不知道如何动态地构造一个基于任意谓词的谓词，长长的子目标列表。对于已知数量的子句，手动编写谓词很简单：

  dataContext.Customers.Where（
（ 
 Customer.Forenames.Contains（keyword1）
 || 
 Customer.Forenames.Contains（keyword2）
）&&（
 Customer。 Surname.Contains（keyword1）
 || 
 Customer.Surname.Contains（keyword2）
）
）; 
  

总之，我需要一种技术，给定两个谓词，将返回一个构成两个谓词的谓词使用提供的运算符来源谓词，但仅限于Linq-to-SQL明确支持的运算符。任何想法？

解决方案

您可以使用 PredicateBuilder class

 的IQueryable<客户和GT; SearchCustomers（params string [] keywords）
 {
 var predicate = PredicateBuilder.False< Customer>（）; 
 
 foreach（关键字中的字符串关键字）
 {
 //请注意，您必须*在循环
中声明一个变量//否则您的所有lambda表达式最终会引用任何
 //关键字的值是在最终执行时的值。 
 string temp = keyword; 
 predicate = predicate.Or（p => p.Forenames.Contains（temp））; 
} 
返回dataContext.Customers.Where（谓词）; 
 
 $ / code> 

（这实际上是 PredicateBuilder page，我只是将它改编成你的个案......）



---

编辑：

其实我误解了你的问题，上面的例子只包含了解决方案的一部分......以下方法应该做你想做的事：

  IQueryable< Customer> SearchCustomers（string [] forenameKeyWords，string [] surnameKeywords）
 {
 var predicate = PredicateBuilder.True< Customer>（）; 
 
 var forenamePredicate = PredicateBuilder.False< Customer>（）; 
 foreach（forenameKeyWords中的字符串关键字）
 {
 string temp = keyword; 
 forenamePredicate = forenamePredicate.Or（p => p.Forenames.Contains（temp））; 
} 
 predicate = PredicateBuilder.And（forenamePredicate）; 
 
 var surnamePredicate = PredicateBuilder.False< Customer>（）; 
 foreach（surnameKeyWords中的字符串关键字）
 {
 string temp = keyword; 
 surnamePredicate = surnamePredicate.Or（p => p.Surnames.Contains（temp））; 
} 
 predicate = PredicateBuilder.And（surnamePredicate）; 
 
返回dataContext.Customers.Where（谓词）; 
} 
  

您可以像这样使用它：

  var query = SearchCustomers（
 new [] {keyword1，keyword2}，
 new [] {keyword3， keyword4}）; 
 
 foreach（var在查询中的Customer）
 {
 ... 
} 
  

(An earlier question, Recursively (?) compose LINQ predicates into a single predicate, is similar to this but I actually asked the wrong question... the solution there satisfied the question as posed, but isn't actually what I need. They are different, though. Honest.)

Given the following search text:

"keyword1 keyword2 ... keywordN"

I want to end up with the following SQL:

SELECT [columns] FROM Customer 
  WHERE (
        Customer.Forenames LIKE '%keyword1%' 
        OR 
        Customer.Forenames LIKE '%keyword2%'
        OR
        ...
        OR
        Customer.Forenames LIKE '%keywordN%'
    ) AND (
        Customer.Surname LIKE '%keyword1%' 
        OR 
        Customer.Surname LIKE '%keyword2%'
        OR
        ....
        OR
        Customer.Surname LIKE '%keywordN%'
    )

Effectively, we're splitting the search text on spaces, trimming each token, constructing a multi-part OR clause based on each , and then AND'ing the clauses together.

I'm doing this in Linq-to-SQL, and I have no idea how to dynamically compose a predicate based on an arbitrarily-long list of subpredicates. For a known number of clauses, it's easy to compose the predicates manually:

dataContext.Customers.Where(
    ( 
      Customer.Forenames.Contains("keyword1") 
      ||
      Customer.Forenames.Contains("keyword2")
    ) && (
      Customer.Surname.Contains("keyword1") 
      ||
      Customer.Surname.Contains("keyword2")
    )
);

In short, I need a technique that, given two predicates, will return a single predicate composing the two source predicates with a supplied operator, but restricted to the operators explicitly supported by Linq-to-SQL. Any ideas?

解决方案

You can use the PredicateBuilder class

IQueryable<Customer> SearchCustomers (params string[] keywords)
{
  var predicate = PredicateBuilder.False<Customer>();

  foreach (string keyword in keywords)
  {
    // Note that you *must* declare a variable inside the loop
    // otherwise all your lambdas end up referencing whatever
    // the value of "keyword" is when they're finally executed.
    string temp = keyword;
    predicate = predicate.Or (p => p.Forenames.Contains (temp));
  }
  return dataContext.Customers.Where (predicate);
}

(that's actually the example from the PredicateBuilder page, I just adapted it to your case...)

---

EDIT:

Actually I misread your question, and my example above only covers a part of the solution... The following method should do what you want :

IQueryable<Customer> SearchCustomers (string[] forenameKeyWords, string[] surnameKeywords)
{
    var predicate = PredicateBuilder.True<Customer>();

    var forenamePredicate = PredicateBuilder.False<Customer>();
    foreach (string keyword in forenameKeyWords)
    {
      string temp = keyword;
      forenamePredicate = forenamePredicate.Or (p => p.Forenames.Contains (temp));
    }
    predicate = PredicateBuilder.And(forenamePredicate);

    var surnamePredicate = PredicateBuilder.False<Customer>();
    foreach (string keyword in surnameKeyWords)
    {
      string temp = keyword;
      surnamePredicate = surnamePredicate.Or (p => p.Surnames.Contains (temp));
    }
    predicate = PredicateBuilder.And(surnamePredicate);

    return dataContext.Customers.Where(predicate);
}

You can use it like that:

var query = SearchCustomers(
    new[] { "keyword1", "keyword2" },
    new[] { "keyword3", "keyword4" });

foreach (var Customer in query)
{
    ...
}

这篇关于将LINQ-to-SQL谓词组合到一个谓词中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！