C# 您将如何使用 LINQ 进行“不在”查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/183791/
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
How would you do a "not in" query with LINQ?
提问by Brennan
I have two collections which have property Emailin both collections. I need to get a list of the items in the first list where Emaildoes not exist in the second list. With SQL I would just use "not in", but I do not know the equivalent in LINQ. How is that done?
我有两个集合，它们Email在两个集合中都有属性。我需要获取第一个列表中Email第二个列表中不存在的项目列表。对于 SQL，我只会使用“not in”，但我不知道 LINQ 中的等价物。这是怎么做的？
So far I have a join, like...
到目前为止，我有一个加入，例如...
var matches = from item1 in list1
join item2 in list2 on item1.Email equals item2.Email
select new { Email = list1.Email };
But I cannot join since I need the difference and the join would fail. I need some way of using Contains or Exists I believe. I just have not found an example to do that yet.
但是我无法加入，因为我需要差异并且加入会失败。我需要某种方式来使用我相信的“包含”或“存在”。我只是还没有找到一个例子来做到这一点。
采纳答案by Robert Rouse
I don't know if this will help you but..
我不知道这是否对你有帮助，但是..
NorthwindDataContext dc = new NorthwindDataContext();
dc.Log = Console.Out;
var query =
from c in dc.Customers
where !(from o in dc.Orders
select o.CustomerID)
.Contains(c.CustomerID)
select c;
foreach (var c in query) Console.WriteLine( c );
回答by tvanfosson
var secondEmails = (from item in list2
select new { Email = item.Email }
).ToList();
var matches = from item in list1
where !secondEmails.Contains(item.Email)
select new {Email = item.Email};
回答by Echostorm
You want the Except operator.
您需要“除外”运算符。
var answer = list1.Except(list2);
Better explanation here: https://docs.microsoft.com/archive/blogs/charlie/linq-farm-more-on-set-operators
更好的解释在这里：https: //docs.microsoft.com/archive/blogs/charlie/linq-farm-more-on-set-operators
NOTE:This technique works best for primitive types only, since you have to implement an IEqualityComparerto use the Exceptmethod with complex types.
注意：此技术仅适用于原始类型，因为您必须实现IEqualityComparer才能将该Except方法用于复杂类型。
回答by Inisheer
Example using List of int for simplicity.
为简单起见，使用 int 列表的示例。
List<int> list1 = new List<int>();
// fill data
List<int> list2 = new List<int>();
// fill data
var results = from i in list1
where !list2.Contains(i)
select i;
foreach (var result in results)
Console.WriteLine(result.ToString());
回答by Amy B
items in the first list where the Email does not exist in the second list.
第一个列表中的项目，而第二个列表中不存在电子邮件。
from item1 in List1
where !(list2.Any(item2 => item2.Email == item1.Email))
select item1;
回答by Ryan Lundy
While Exceptis part of the answer, it's not the whole answer. By default, Except(like several of the LINQ operators) does a reference comparison on reference types. To compare by values in the objects, you'll have to
虽然Except是答案的一部分，但不是全部答案。默认情况下，Except（像几个 LINQ 运算符一样）对引用类型进行引用比较。要按对象中的值进行比较，您必须
- implement
IEquatable<T>in your type, or - override
EqualsandGetHashCodein your type, or - pass in an instance of a type implementing
IEqualityComparer<T>for your type
IEquatable<T>在您的类型中实现，或- 覆盖
Equals和GetHashCode您的类型，或 - 传入
IEqualityComparer<T>为您的类型实现的类型的实例
回答by Brett
In the case where one is using the ADO.NET Entity Framework, EchoStorm's solution also works perfectly. But it took me a few minutes to wrap my head around it. Assuming you have a database context, dc, and want to find rows in table x not linked in table y, the complete answer answer looks like:
在使用ADO.NET Entity Framework的情况下，EchoStorm 的解决方案也能完美运行。但是我花了几分钟才把头环绕在它周围。假设您有一个数据库上下文 dc，并且想要在表 x 中查找未在表 y 中链接的行，完整的答案如下所示：
var linked =
from x in dc.X
from y in dc.Y
where x.MyProperty == y.MyProperty
select x;
var notLinked =
dc.X.Except(linked);
In response to Andy's comment, yes, one can have two from's in a LINQ query. Here's a complete working example, using lists. Each class, Foo and Bar, has an Id. Foo has a "foreign key" reference to Bar via Foo.BarId. The program selects all Foo's not linked to a corresponding Bar.
作为对 Andy 评论的回应，是的，一个 LINQ 查询中可以有两个 from。这是一个完整的工作示例，使用列表。每个类，Foo 和 Bar，都有一个 Id。Foo 通过 Foo.BarId 对 Bar 有一个“外键”引用。该程序选择所有未链接到相应 Bar 的 Foo。
class Program
{
static void Main(string[] args)
{
// Creates some foos
List<Foo> fooList = new List<Foo>();
fooList.Add(new Foo { Id = 1, BarId = 11 });
fooList.Add(new Foo { Id = 2, BarId = 12 });
fooList.Add(new Foo { Id = 3, BarId = 13 });
fooList.Add(new Foo { Id = 4, BarId = 14 });
fooList.Add(new Foo { Id = 5, BarId = -1 });
fooList.Add(new Foo { Id = 6, BarId = -1 });
fooList.Add(new Foo { Id = 7, BarId = -1 });
// Create some bars
List<Bar> barList = new List<Bar>();
barList.Add(new Bar { Id = 11 });
barList.Add(new Bar { Id = 12 });
barList.Add(new Bar { Id = 13 });
barList.Add(new Bar { Id = 14 });
barList.Add(new Bar { Id = 15 });
barList.Add(new Bar { Id = 16 });
barList.Add(new Bar { Id = 17 });
var linked = from foo in fooList
from bar in barList
where foo.BarId == bar.Id
select foo;
var notLinked = fooList.Except(linked);
foreach (Foo item in notLinked)
{
Console.WriteLine(
String.Format(
"Foo.Id: {0} | Bar.Id: {1}",
item.Id, item.BarId));
}
Console.WriteLine("Any key to continue...");
Console.ReadKey();
}
}
class Foo
{
public int Id { get; set; }
public int BarId { get; set; }
}
class Bar
{
public int Id { get; set; }
}
回答by StriplingWarrior
For people who start with a group of in-memory objects and are querying against a database, I've found this to be the best way to go:
对于从一组内存对象开始并查询数据库的人来说，我发现这是最好的方法：
var itemIds = inMemoryList.Select(x => x.Id).ToArray();
var otherObjects = context.ItemList.Where(x => !itemIds.Contains(x.Id));
This produces a nice WHERE ... IN (...)clause in SQL.
这会WHERE ... IN (...)在 SQL 中生成一个不错的子句。
回答by mangeshkt
Thank you, Brett. Your suggestion helped me too. I had a list of Objects and wanted to filter that using another list of objects. Thanks again....
谢谢你，布雷特。你的建议也帮助了我。我有一个对象列表，想使用另一个对象列表过滤它。再次感谢....
If anyone needs, please have a look at my code sample:
如果有人需要，请查看我的代码示例：
'First, get all the items present in the local branch database
Dim _AllItems As List(Of LocalItem) = getAllItemsAtBranch(BranchId, RecordState.All)
'Then get the Item Mappings Present for the branch
Dim _adpt As New gItem_BranchesTableAdapter
Dim dt As New ds_CA_HO.gItem_BranchesDataTable
_adpt.FillBranchMappings(dt, BranchId)
Dim _MappedItems As List(Of LocalItem) = (From _item As LocalItem In _AllItems Join _
dr As ds_CA_HO.gItem_BranchesRow In dt _
On _item.Id Equals dr.numItemID _
Select _item).ToList
_AllItems = _AllItems.Except(_MappedItems.AsEnumerable).ToList
Return _AllItems
回答by Chintan Udeshi
You can take both the collections in two different lists, say list1 and list2.
您可以将两个集合放在两个不同的列表中，比如 list1 和 list2。
Then just write
然后就写
list1.RemoveAll(Item => list2.Contains(Item));
This will work.
这将起作用。
