none
Linq 2 Entities: Query a "Couple" table RRS feed

  • Question

  • Hi, i'm working with Entity Framework 4 and i have a doubt to query an entity with Linq.

    My schema is:

    public class Person
    {
      public int PersonId {get;set;}
      public string FirstName {get;set;}
      public string SecondName {get;set;}
    }
    
    public class Couple
    {
      public int CoupleId {get; set;}
      public Person Her {get; set;}
      public Person Him {get; set;}
    }  

    My output DB has two tables:

    TABLE PERSONS

    PersonId [PK, int, not null]

    FirstName [nvarchar(max), not null]

    SecondName [nvarchar(max), not null]

    TABLE COUPLES

    CoupleId [PK, int, not null]

    Her_PersonId [FK, int, null]

    Him_PersonId [FK, int, null]

    I need to retrieve persons not in a couple.

    In SQL syntax is it simply:

    SELECT * FROM Persons p
    LEFT JOIN Couples c ON (p.PersonId = c.Her_PersonId OR p.PersonId = c.Him_PersonId)
    WHERE c.CoupleId IS NULL

    This is my DbContext:

    private readonly MyContext _db = new MyContext();

    How to query this Couple Table for retrieve persons not in a couple?

    Saturday, July 21, 2012 2:55 PM

Answers

  • Hi tranky;

    Not all SQL queries can be translated into a Linq query. In your SQL query there is no Linq equivalent to doing a join with an 'OR' operator. The following query should give you the results you want. It is broken down into two statements but because of deferred execution it is sent to SQL server as one complete query.

    MyContext _db = new MyContext();
    
    // Get a concatinated list of PersonID for all him and her
    var coupleList = (from c in _db.Couple
                      select c.Him.PersonId).Concat(from c in _db.Couple select c.Her.PersonId);
    
    // Query to get all Persons who are NOT in the Couple table
    var result = from p in _db.Persons
                 where !coupleList.Contains(p.PersonId)
                 select p;

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Friday, August 3, 2012 2:23 AM
    Saturday, July 21, 2012 6:00 PM