代码之家 › 专栏 › 技术社区 › Dylan Beattie

将LINQ到SQL谓词组合成单个谓词

composition functional-programming linq-to-sql linq c#

Dylan Beattie · 技术社区 · 14 年前

(前面的问题, Recursively (?) compose LINQ predicates into a single predicate ,与此类似,但实际上我问错了问题。。。那里的解决方案满足了提出的问题,但实际上不是我需要的。不过,他们是不同的。老实说。)

"keyword1 keyword2 ... keywordN"

我希望以以下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%'
    )

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

简而言之,我需要一种技术,在给定两个谓词的情况下,将返回一个谓词,该谓词由两个源谓词组成,并带有一个提供的运算符,但仅限于linqtosql显式支持的运算符。有什么想法吗?

2 回复 | 直到 8 年前

Dylan Beattie 14 年前

你可以用 PredicateBuilder 班

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);
}

谓词生成器

编辑:

事实上我误读了你的问题,我上面的例子只涵盖了解决方案的一部分。。。以下方法应满足您的要求:

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);
}

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

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

Marcelo Cantos 14 年前

通常你会连环调用 .Where(...) . 例如。:

var a = dataContext.Customers;
if (kwd1 != null)
    a = a.Where(t => t.Customer.Forenames.Contains(kwd1));
if (kwd2 != null)
    a = a.Where(t => t.Customer.Forenames.Contains(kwd2));
// ...
return a;

LINQ到SQL将把所有这些都焊接在一起形成一个 WHERE 条款。

这不适用于 OR 然而。您可以使用并集和交集,但我不确定linqtosql(或sqlserver)是否足够聪明,可以将其折叠回单个 哪里 条款。奥托,如果表演不受影响也没关系。不管怎样,它看起来是这样的:

<The type of dataContext.Customers> ff = null, ss = null;

foreach (k in keywords) {
    if (keywords != null) {
        var f = dataContext.Customers.Where(t => t.Customer.Forenames.Contains(k));
        ff = ff == null ? f : ff.Union(f);

        var s = dataContext.Customers.Where(t => t.Customer.Surname.Contains(k));
        ss = ss == null ? s : ss.Union(s);
    }
}
return ff.Intersect(ss);