Linq to SQL 如何执行“ where [ column ] in (列表值)”

我有一个函数，在该函数中我得到一个 id 列表，并且我需要返回一个与 id 相关联的描述相匹配的列表。例如:

public class CodeData
{
string CodeId {get; set;}
string Description {get; set;}
}


public List<CodeData> GetCodeDescriptionList(List<string> codeIDs)
//Given the list of institution codes, return a list of CodeData
//having the given CodeIds
}

因此，如果我自己为此创建 sql，我只需要执行以下操作(in 子句包含 codeIds 参数中的所有值) :

Select CodeId, Description FROM CodeTable WHERE CodeId IN ('1a','2b','3')

在 Linq to Sql 中，我似乎找不到与“ IN”子句等价的内容。到目前为止，我发现最好的(不起作用的)是:

 var foo = from codeData in channel.AsQueryable<CodeData>()
where codeData.CodeId == "1" || codeData.CodeId == "2"
select codeData;

问题在于，我不能动态地为 linq 到 sql 生成一个“ OR”子句列表，因为它们是在编译时设置的。

如何使用 Linq to Sql 来完成检查列是否在动态值列表中的 where 子句？

147236 次浏览
小开
最佳答案

Use

where list.Contains(item.Property)

Or in your case:

var foo = from codeData in channel.AsQueryable<CodeData>()
where codeIDs.Contains(codeData.CodeId)
select codeData;

But you might as well do that in dot notation:

var foo = channel.AsQueryable<CodeData>()
.Where(codeData => codeIDs.Contains(codeData.CodeId));
小开

You could also use:

List<int> codes = new List<int>();


codes.add(1);
codes.add(2);


var foo = from codeData in channel.AsQueryable<CodeData>()
where codes.Any(code => codeData.CodeID.Equals(code))
select codeData;
小开

I had been using the method in Jon Skeet's answer, but another one occurred to me using Concat. The Concat method performed slightly better in a limited test, but it's a hassle and I'll probably just stick with Contains, or maybe I'll write a helper method to do this for me. Either way, here's another option if anyone is interested:

The Method

// Given an array of id's
var ids = new Guid[] { ... };


// and a DataContext
var dc = new MyDataContext();


// start the queryable
var query = (
from thing in dc.Things
where thing.Id == ids[ 0 ]
select thing
);


// then, for each other id
for( var i = 1; i < ids.Count(); i++ ) {
// select that thing and concat to queryable
query.Concat(
from thing in dc.Things
where thing.Id == ids[ i ]
select thing
);
}

Performance Test

This was not remotely scientific. I imagine your database structure and the number of IDs involved in the list would have a significant impact.

I set up a test where I did 100 trials each of Concat and Contains where each trial involved selecting 25 rows specified by a randomized list of primary keys. I've run this about a dozen times, and most times the Concat method comes out 5 - 10% faster, although one time the Contains method won by just a smidgen.

小开 
 var filterTransNos = (from so in db.SalesOrderDetails
where  ItemDescription.Contains(ItemDescription)
select new { so.TransNo }).AsEnumerable();




listreceipt = listreceipt.Where(p => filterTransNos.Any(p2 => p2.TransNo == p.TransNo)).ToList();
小开

Here is how I do it by using HashSet

        HashSet<String> hs = new HashSet<string>(new String[] { "Pluto", "Earth", "Neptune" });
String[] arr =
{
"Pluto",
"Earth",
"Neptune",
"Jupiter",
"Saturn",
"Mercury",
"Pluto",
"Earth",
"Neptune",
"Jupiter",
"Saturn",
"Mercury",
// etc.
};
ICollection<String> coll = arr;


String[] arrStrFiltered = coll.Where(str => hs.Contains(str)).ToArray();

HashSet is basically almost to O(1) so your complexity remains O(n).

小开

No Entity Framework Net Core 2

string[] names = stringsTest.name.Split('|');


if (names.Length > 1)
{
query = query.Where(w => names.Contains(w.name));
}
else
{
query = query.Where(w => w.name== stringsTest.name);
}


var listEntity = await query.ToListDtoAsync(stringsTest);

提交答案