C# LINQ to SQL：针对列表、数组和对象列表的高级查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/731569/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ to SQL: Advanced queries against lists, arrays and lists of objects
提问by cllpse
Single and multiple lists
单个和多个列表
Consider the following lists:
考虑以下列表：
List<Int32> appleIdentities = new List<int>(new[] { 1, 2, 3 });
List<Int32> chocolateIdentities = new List<int>(new[] { 2, 3, 4 });
List<Int32> icecreamIdentities = new List<int>(new[] { 11, 14, 15, 16 });
Using LINQ to SQL; is it possible to wite a statement which translates into:
使用 LINQ to SQL；是否有可能将声明翻译为：
SELECT
DesertsID,
DesertsName
FROM
Deserts
WHERE
Deserts.AppleIdentity IN (1, 2, 3) AND
Deserts.ChocolateIdentity IN (2, 3, 4) AND
Deserts.IcecreamIdentity IN (11, 14, 15m 16)
If yes; how would the code look if I wanted to query my database of deserts against just the appleIdentitieslist?
Arrays
如果是; 如果我只想根据appleIdentities列表查询我的沙漠数据库，代码会是什么样子？
数组
Consider the following arrays:
考虑以下数组：
Int32[] appleIdentities = new[] {1, 2, 3, 4};
String[] chocolateNames = new[] {"Light", "Dark"};
Using LINQ to SQL; is it possible to wite a statement which translates into:
使用 LINQ to SQL；是否有可能将声明翻译为：
SELECT
DesertsID,
DesertsName
FROM
Deserts
WHERE
Deserts.AppleIdentity IN (1, 2, 3) AND
Deserts.ChocolateName IN ('Light', 'Dark')
If yes; how would the code look if I wanted to query my database of deserts against just the appleIdentitiesarray?
List of objects
如果是; 如果我只想根据appleIdentities数组查询我的沙漠数据库，代码会是什么样子？
对象列表
Consider the following:
考虑以下：
public class Identities
{
public Int32 appleIdentity { get; set; }
public String chokolateName { get; set; }
}
List<Identities> identities = new List<Identities>(new[] {
new Identities { appleIdentity = 1, chokolateName = "Light" },
new Identities { appleIdentity = 2, chokolateName = "Dark" },
});
Using LINQ to SQL; is it possible to wite a statement which translates into:
使用 LINQ to SQL；是否有可能将声明翻译为：
SELECT
DesertsID,
DesertsName
FROM
Deserts
WHERE
Deserts.AppleIdentity IN (1, 2) AND
Deserts.ChocolateName IN ('Light', 'Dark')
If yes; how would the code look if I wanted to query my database of deserts against just the appleIdentity-property on my list of Identitiesobjects?
This is branch off of LINQ to SQL query against a list of entities
如果是; 如果我只想appleIdentity根据Identities对象列表中的-property查询我的沙漠数据库，代码会是什么样子？
这是针对实体列表的 LINQ to SQL 查询的分支
采纳答案by Marc Gravell
Sure - just use Contains- using Northwind as an example:
当然 - 只需使用Contains- 以 Northwind 为例：
var qry = from cust in ctx.Customers
where custIds.Contains(cust.CustomerID)
&& regions.Contains(cust.Region)
select cust; // or your custom projection
回答by Jon Skeet
Well, you can try:
嗯，你可以试试：
var query = from dessert in db.Desserts
where appleIdentities.Contains(dessert.AppleIdentity)
&& chocolateIdentities.Contains(dessert.ChocolateIdentity)
&& iceCreamIdentities.Contains(dessert.IceCreamIdentity)
select new { dessert.Id, dessert.Name };
I believe that's okay, although it'll fail when the lists get big enough IIRC. That should be okay for lists and arrays.
我相信这没关系，尽管当列表变得足够大 IIRC 时它会失败。这对于列表和数组应该没问题。
I'm not sure about your third query though - I think you'd need a list for each of the separate Contains calls.
不过，我不确定您的第三个查询 - 我认为您需要为每个单独的 Contains 调用提供一个列表。
回答by Pop Catalin
how would the code look if I wanted to query my database of deserts against just the appleIdentities list?
如果我只想根据 appleIdentities 列表查询我的沙漠数据库，代码会是什么样子？
You can compose a linq query in multiple statements, like so, and select at runtime which filters your want to use in your where clause.
您可以像这样在多个语句中编写 linq 查询，并在运行时选择要在 where 子句中使用的过滤器。
var query = db.Desserts;
if (filterbyAppleIdentity)
query = query.Where( q => appleIdentities.Contains(q.DesertsID));
if (filterbyChocolateIdentities)
query = query.Where( q => chocolateIdentities.Contains(q.DesertsID));
if (filterbicecreamIdentities)
query = query.Where( q => icecreamIdentities.Contains(q.DesertsID));
var deserts = query.ToList();
you can also write an extension method to do this without if statements: (Edit fixed typo, return type should be IQueriable
您还可以编写一个扩展方法来执行此操作，而无需 if 语句：（编辑固定的错字，返回类型应为 IQueriable
public static class LinqExtensions {
public IQueriable<T> CondWhere<T>(this IQueriable<T> query, bool condition, Expression<Func<T,bool>> predicate) {
if (condition)
return query.Where(predicate);
else
return query;
}
}
and write your linq query like this:
并像这样编写你的 linq 查询：
var deserts = db.Desserts;
.CondWhere(filterbyAppleIdentity, q => appleIdentities.Contains(q.DesertsID));
.CondWhere(filterbyChocolateIdentities, q => chocolateIdentities.Contains(q.DesertsID));
.CondWhere(filterbicecreamIdentities, q => icecreamIdentities.Contains(q.DesertsID)).ToList();
Another way to do it is to union the id lists:
另一种方法是联合 id 列表：
var deserts = db.Deserts
.Where( d => appleIdentities.Union(chocolateIdentities).Union(icecreamIdentities).Contains(d.DesertsID);
For a list of objects you can use .Select extension method to project your list into a int or string IEnumerable and you can use contains in the query in the same way:
对于对象列表，您可以使用 .Select 扩展方法将您的列表投影到 int 或 string IEnumerable 中，您可以以相同的方式在查询中使用 contains：
var deserts = db.Deserts
.Where(d =>
identities.Select(i => i.appleIdentity).Contains(d => d.DesertID) &&
identities.Select(i => i.chokolateName).Contains(d => d.DesertsName)
)
回答by Amy B
As others have said, LinqToSql will translate Containsto IN.
正如其他人所说，LinqToSql 将转换Contains为IN.
There's some caveats:
有一些警告：
- this translation works for
List<T>.Contains(), but doesn't work forIList<T>.Contains(). Does it work for arrays? I don't know. - This translation will happily translate as many elements as you like - each element becomes a sql parameter. SQL Server 2008 has an approx 2000 parameter limit and will throw sql exceptionsat you if you try this with a collection that is too big.
- This translation, when applied to a collection of strings, will produce nvarcharparameters. This could be a serious problem if the target column is varcharand you want to use the index on this column. Sql Server will convert the index, instead of the parameters... which involves reading and converting every string in the whole index.
- 此翻译适用于
List<T>.Contains()，但不适用于IList<T>.Contains()。它适用于数组吗？我不知道。 - 这个翻译会很高兴地翻译任意多的元素——每个元素都成为一个 sql 参数。SQL Server 2008 有大约 2000 个参数限制，如果您尝试使用太大的集合，则会向您抛出 sql 异常。
- 当应用于字符串集合时，此转换将生成nvarchar参数。如果目标列是varchar并且您想在该列上使用索引，这可能是一个严重的问题。Sql Server 将转换索引，而不是参数......这涉及读取和转换整个索引中的每个字符串。
Here's some code for your List of Objectsquestion:
这是您的对象列表问题的一些代码：
List<int> someIDs = identities
.Select(x => x.appleIdentity).ToList();
List<string> someStrings = identities
.Select(x => x.chokolateName).ToList();
var query = db.Desserts.Where(d =>
someIDs.Contains(d.AppleIdentity) &&
someStrings.Contains(d.ChocolateName)
)
