none
NOT IN with composite primary keys RRS feed

  • Question

  • Hi all,

    Marco Russo describes a nice way to realize a not-in query with linq-to-sql http://programminglinq.com/blogs/marcorusso/archive/2008/01/14/the-not-in-clause-in-linq-to-sql.aspx but i wonder how to use the contains method with two parameters:

    Forexaple if the primary key is not only the customerID but the Customer email as well:


    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 && c.CustomerEmail)

        select c;

    foreach (var c in query) Console.WriteLine( c );


    Thx and kind regards
    Thursday, September 4, 2008 1:04 PM

Answers

  • IN does not support testing sets of values across column but you can achieve the same effect using an outer join and a test for null.

    e.g. something like:

    var query =

        from c in dc.Customers

        join o in dc.Orders on c.CustomerID == o.CustomerID && c.CustomerEmail == o.CustomerEmail

        into orderJoin

        where orderJoin.CustomerID == null

        select c;


    [)amien

    Thursday, September 4, 2008 3:36 PM
    Moderator