Geek asks and answers

LINQ query: determining if an object exists in one list in another based on a key

Basically, what I'm trying to do is take a list of objects and filter it based on a number of criteria, in which one of the criteria is that the key does not exist in another list. Here is an example:
My two classes are similar to the following:

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

I want to sort the List <Test> by looking at the course and making sure that the test does not exist in the <Destination> list. In SQL, I would 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)

However, I cannot figure out how to do this in LINQ. Any ideas?

+4
source share
2 answers

http://introducinglinq.com/blogs/marcorusso/archive/2008/01/14/the-not-in-clause-in-linq-to-sql.aspx

, , Orders. SQL-, .

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

( NOT EXISTS - - ). LINQ , .

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 to SQL SQL:

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 to SQL.

+4

, LINQ to Objects. - :

List<Test> tests = ...;
List<Appointment> appts = ...;

var query = tests.Except(
            tests.Join(appts, t => t.name, a => a.testName, (t, a) => t));

:

var query = tests.Where(t => !appts.Any(a => a.testName == t.name));

, Join - , appts tests.

+10

More articles:


All Articles