LINQ查询：确定是否在一个列表对象在另一个存在基于关键 [英] LINQ Query: Determining if object in one list exists in another based on key

问题描述

基本上我想要做的就是对象的列表和过滤它基于一系列标准，其中的标准之一是，关键并不在另一份名单存在。这里有一个例子：结果
我的两个类是与此类似：

Basically what I'm trying to do is take a list of objects and filter it based on a number of criteria where one of the criteria is that the key doesn't exist in another list. Here's an example:
My two classes are similar to this:

public class Test  
{  
  public string name;  
  public string instructor_name;  
  public string course;  
}  

public class Appointment  
{  
  public string site;
  public DateTime forWhen;
  public string testName;
}

我想通过一个List<进行排序，通过查看课程考试>和确保试验没有在列表<存在;预约>。在SQL中我会做这样的事情：

I want to sort through a List<Test> by looking at the course and making sure that test doesn't exist in the List<Appointment>. In SQL I'd do it something like this:

SELECT new Group<Test>(c.Key, c)
FROM tests in testList
WHERE tests.Course != "Science"
AND tests.name NOT IN (SELECT testName FROM appotList)

不过，我想不通我怎么会在LINQ做到这一点。任何想法？

However, I can't figure out how I would do this in LINQ. Any ideas?

推荐答案

的 http://introducinglinq.com/blogs/marcorusso/archive/2008/01/14/the-not -in子句，在LINQ到sql.aspx

考虑以下代码返回所有的客户不必在命令谁订单表。这是一条SQL查询，返回该值。

Consider this code that returns all the customers who don't have an order in the Orders table. This is one SQL query that returns that value.

SELECT *
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] NOT IN (
    SELECT [t1].[CustomerID]
    FROM [dbo].[Orders] AS [t1]
)

这是不是更快的方式来获得期望的结果（使用NOT EXISTS是最喜欢的方式 - 更多关于这个不久）。 。LINQ提供包含扩展方法，允许写下面的代码

This is not the faster way to get the desired result (using a NOT EXISTS is the favorite way - more on this shortly). LINQ offers a Contains extension method that allows writing the following code.

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 );

在LINQ到SQL的查询转换到这个SQL代码：

In LINQ to SQL the query is translated into this SQL code:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
       [t0].[ContactTitle], [t0].[Address], [t0].[City],
       [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Orders] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[CustomerID]
    ))

这方法不仅语义上相当，但也快于执行。以下是SET STATISTICS IO ON的结果。第一个结果是对于使用NOT IN子句中的手写查询。第二个结果是LINQ到SQL生成的查询。

This approach is not only semantically equivalent, but also faster in execution. The following is the result with SET STATISTICS IO ON. The first result is for the hand-written query that use the NOT IN clause. The second result is for the LINQ to SQL generated query.

这篇关于LINQ查询：确定是否在一个列表对象在另一个存在基于关键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！