C# 使用 linq 根据 Id 列表选择多条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16824510/
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
Select multiple records based on list of Id's with linq
提问by Yustme
I have a list containing Id's of my UserProfiletable. How can i select all UserProfilesbased on the list of Id's i got in a varusing LINQ?
我有一个包含我的UserProfile表的ID 的列表。如何UserProfiles根据我在varusing 中获得的 Id 列表选择所有内容LINQ？
var idList = new int[1, 2, 3, 4, 5];
var userProfiles = _dataContext.UserProfile.Where(......);
I got stuck right here. I can do this using for loops etc. But I'd rather do this with LINQ.
我被卡在这里了。我可以使用 for 循环等来做到这一点。但我宁愿使用LINQ.
采纳答案by Yuck
You can use Contains()for that. It will feel a little backwards when you're really trying to produce an INclause, but this should do it:
你可以用Contains()它。当你真的试图产生一个IN子句时，它会感觉有点倒退，但这应该做到：
var userProfiles = _dataContext.UserProfile
.Where(t => idList.Contains(t.Id));
I'm also assuming that each UserProfilerecord is going to have an intIdfield. If that's not the case you'll have to adjust accordingly.
我还假设每条UserProfile记录都有一个intId字段。如果不是这种情况，您将不得不进行相应的调整。
回答by Fabian Bigler
That should be simple. Try this:
那应该很简单。尝试这个：
var idList = new int[1, 2, 3, 4, 5];
var userProfiles = _dataContext.UserProfile.Where(e => idList.Contains(e));
回答by David Gregor
Solution with .Where and .Contains has complexity of O(N square). Simple .Join should have a lot better performance (close to O(N) due to hashing). So the correct code is:
.Where 和 .Contains 的解决方案的复杂度为 O(N 平方)。Simple .Join 应该有更好的性能（由于散列，接近 O(N)）。所以正确的代码是：
_dataContext.UserProfile.Join(idList, up => up.ID, id => id, (up, id) => up);
And now result of my measurement. I generated 100 000 UserProfiles and 100 000 ids. Join took 32ms and .Where with .Contains took 2 minutes and 19 seconds! I used pure IEnumerable for this testing to prove my statement. If you use List instead of IEnumerable, .Where and .Contains will be faster. Anyway the difference is significant. The fastest .Where .Contains is with Set<>. All it depends on complexity of underlying coletions for .Contains. Look at this postto learn about linq complexity.Look at my test sample below:
现在我的测量结果。我生成了 100 000 个用户配置文件和 100 000 个 ID。Join 耗时 32 毫秒，.Where 和 .Contains 耗时 2 分 19 秒！我在这个测试中使用了纯 IEnumerable 来证明我的陈述。如果使用 List 而不是 IEnumerable，.Where 和 .Contains 会更快。无论如何，差异是显着的。最快的 .Where .Contains 是 Set<>。这一切都取决于 .Contains 的底层集合的复杂性。查看这篇文章以了解 linq 复杂性。看看我下面的测试示例：
private static void Main(string[] args)
{
var userProfiles = GenerateUserProfiles();
var idList = GenerateIds();
var stopWatch = new Stopwatch();
stopWatch.Start();
userProfiles.Join(idList, up => up.ID, id => id, (up, id) => up).ToArray();
Console.WriteLine("Elapsed .Join time: {0}", stopWatch.Elapsed);
stopWatch.Restart();
userProfiles.Where(up => idList.Contains(up.ID)).ToArray();
Console.WriteLine("Elapsed .Where .Contains time: {0}", stopWatch.Elapsed);
Console.ReadLine();
}
private static IEnumerable<int> GenerateIds()
{
// var result = new List<int>();
for (int i = 100000; i > 0; i--)
{
yield return i;
}
}
private static IEnumerable<UserProfile> GenerateUserProfiles()
{
for (int i = 0; i < 100000; i++)
{
yield return new UserProfile {ID = i};
}
}
Console output:
控制台输出：
Elapsed .Join time: 00:00:00.0322546
Elapsed .Where .Contains time: 00:02:19.4072107
已过。加入时间：00:00:00.0322546
Elapsed .Where .Contains time: 00:02:19.4072107
回答by Tomino
Nice answers abowe, but don't forget one IMPORTANTthing - they provide different results!
不错的答案，但不要忘记一件重要的事情 - 它们提供不同的结果！
var idList = new int[1, 2, 2, 2, 2]; // same user is selected 4 times
var userProfiles = _dataContext.UserProfile.Where(e => idList.Contains(e)).ToList();
This will return 2 rows from DB (and this could be correct, if you just want a distinct sorted list of users)
这将从 DB 返回 2 行（这可能是正确的，如果您只想要一个不同的排序用户列表）
BUTin many cases, you could want an unsortedlist of results. You always have to think about it like about a SQL query. Please see the example with eshop shopping cart to illustrate what's going on:
但是在许多情况下，您可能需要一个未排序的结果列表。您总是必须像考虑 SQL 查询一样考虑它。请参阅 eshop 购物车示例以说明发生了什么：
var priceListIDs = new int[1, 2, 2, 2, 2]; // user has bought 4 times item ID 2
var shoppingCart = _dataContext.ShoppingCart
.Join(priceListIDs, sc => sc.PriceListID, pli => pli, (sc, pli) => sc)
.ToList();
This will return 5results from DB. Using 'contains' would be wrong in this case.
这将从 DB返回5 个结果。在这种情况下，使用“包含”是错误的。
