How to implement a left outer join in the Entity Framework.

Question

I have the following SQL query:-

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

Answer 1

Let me know if you want to select something different, but a true join is below

  from p in _dbContext.ProfileSet
                join ps in _dbContext.ProfileSettings on p.ProfileKey = ps.ProfileKey  into a
                join pi in _dbContext.PlatformIdentities on p.ProfileKey = pi.ProfileKey  into b

                select new
                {
                   profiles.ProfileKey,
                   profiles.FirstName,
                   profiles.LastName,
                   profiles.EmailAddress,
                   profiles.DateCreated,
                   profiles.LastLoggedIn, 
                   PlatformSettings = a.Select(x=>x),
                   PlatformIdentities = b.Select(y=>y)
                }