locked
Left outer join with three tables in the Entity Framework RRS feed

  • Question

  • select distinct * from dbo.Profiles profiles 
    left join
    ProfileSettings pSet on pSet.ProfileKey = profiles.ProfileKey
    left join
    PlatformIdentities pId on pId.ProfileKey = profiles.Profilekey

    I need to convert it to a LinqToEntities expression. I have tried the following:-

    from profiles in _dbContext.ProfileSet
                                let leftOuter
    = (from pSet in _dbContext.ProfileSettingSet
                                                 
    select new
                                                           
    {
                                                                pSet
    .isInternal
                                                           
    }).FirstOrDefault()
     
    select new
                                           
    {
                                               profiles
    .ProfileKey,
                                               
    Internal = leftOuter.isInternal,
                                               profiles
    .FirstName,
                                               profiles
    .LastName,
                                               profiles
    .EmailAddress,
                                               profiles
    .DateCreated,
                                               profiles
    .LastLoggedIn,                                              
                                           
    };

    The above query works fine because I haven't considered the third table "PlatformIdentities". Single left outer join works with what I have done above. How do I include PlatformIdentities (the 3rd table) ? I basically want to translate the SQL query I specified at the beginning of this post (which gives me exactly what I need) in to LinqToEntities.

    Thanks

    Wednesday, March 31, 2010 12:02 AM

Answers

  • Hello,

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

    For LEFT OUTER JOIN, please use the extension method DefaultIfEmpty():
    ===================================================================
    var query = from p1 in context.P1s
                      join p2 in context.P2s on p1.Key equals p2.Key into list1
                      from l1 in list1.DefaultIfEmpty()
                      join p3 in context.P3s on l1.Key equals p3.Key into list2
                      from l2 in list2.DefaultIfEmpty()
                      select new { p1.....}
    ===================================================================

    http://geekswithblogs.net/SudheersBlog/archive/2009/06/11/132758.aspx

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 6, 2010 8:14 AM

All replies

  • Hello,

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

    For LEFT OUTER JOIN, please use the extension method DefaultIfEmpty():
    ===================================================================
    var query = from p1 in context.P1s
                      join p2 in context.P2s on p1.Key equals p2.Key into list1
                      from l1 in list1.DefaultIfEmpty()
                      join p3 in context.P3s on l1.Key equals p3.Key into list2
                      from l2 in list2.DefaultIfEmpty()
                      select new { p1.....}
    ===================================================================

    http://geekswithblogs.net/SudheersBlog/archive/2009/06/11/132758.aspx

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 6, 2010 8:14 AM
  • its good

     

    Thursday, July 7, 2011 10:31 AM
  • Hello,

    What if l1 is null for some conditions in the above query ? Doesnt it throw a null exception on the join condition

       join p3 in context.P3s on l1.Key equals p3.Key into list2 ?

     


    rajesh
    Tuesday, September 20, 2011 12:59 AM