如何动态组合linq查询? [英] How to combine a linq query dynamically?

问题描述

假设我有一个表，该表在字符串(nvarchar)列中包含格式化的值.这些值应该是由某些const符号分隔的字符串(让它是分号;).例如，

Suppose I have a table, containing formatted values in a string (nvarchar) column. These values are supposed to be strings separated by some const symbol (let it be a semi-colon ;). For example,

12;0;5;dog //four values separated by a semi-colon

或

053 //if there is only one value, no semi-colon at the end

分隔符始终是分隔符，不能成为值的一部分.

The separator is always a separator, it can't be a part of the value.

我需要检查该表中是否已经有一行，并且该列中的值列表至少包含指定的一项.换句话说，我有一个值列表:

I need to check if there is already a row in that table, with a list of values in that column, which contains at least one of the specified items. In other words I have a list of values:

List<string> list = new List<string>() { "somevalue1", "somevalue2" };

分隔符:

string separator = ";";

我需要编写一个linq-to-sql查询:

And I need to write a linq-to-sql query doing this:

select ... from sometable
where Value='somevalue1' or Value like 'somevalue1;%' or
      Value like '%;somevalue1' or Value like '%;somevalue1;%'

   or Value='somevalue2' or Value like 'somevalue2;%' or 
      Value like '%;somevalue2' or Value like '%;somevalue2;%'

应该提到的是，任何搜索到的值都可以包含其他值.也就是说，我可能正在精确地搜索5，而某行可能包含1;15;55.这样的行必须​​不是是匹配项.而...;5;...或只是5或5;...或...;5是匹配项.

It should be mentioned that any of the searched values may contain the other. That is, I may be searching for exactly 5, while some row may contain 1;15;55. Such row must not be a match. While ...;5;... or just 5, or 5;..., or ...;5 are the matches.

使用linq-to sql，我知道如何进行以下类型的查询:

Using linq-to sql I know how to do queries of the following kind:

select ... from sometable where (... or ... or ...) and (... or ...) ...

那是

IQueryable<SomeTable> query = dc.SomeTable;
foreach (string l in list)
{
    string s = l;
    query = query.Where(b => b.Value.StartsWith(s + separator) ||
                             b.Value.EndsWith(separator + s) ||
                             b.Value.Contains(separator + s + separator) ||
                             b.Value.Equals(s));
}
if (query.Any()) {/*...*/}

很显然，在生成的sql查询中，Where语句与AND连接在一起，而我到处都需要OR.

Obviously the Where statements are joined with AND in the resulting sql query, while I need OR everywhere.

有没有办法在C#代码中获取我需要的查询?或唯一的方法是使用手写查询和 DataContext.ExecuteQuery来执行此操作方法?

So is there a way to get the query I need within C# code? Or the only way would be to do this with a hand-written query and DataContext.ExecuteQuery Method?

推荐答案

我想UNION会满足您的需求:

IQueryable<SomeTable> baseQuery = dc.SomeTable;
IQueryable<SomeTable> query = new List<SomeTable>().AsQueryable();
foreach (string l in list)
{
    string s = l;

    query.Union(baseQuery.Where(b => b.Value.StartsWith(s + separator) ||
                             b.Value.EndsWith(separator + s) ||
                             b.Value.Contains(separator + s + separator) ||
                             b.Value.Equals(s)));
}
if (query.Any()) {/*...*/}

这篇关于如何动态组合linq查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！