left join two tables in same DataContext LinQ in c#

Question

I have two table in the same DataContext as follows.

 Table PersonnelInfo
    { 

        personnelId,
        personnelName ,          
        description,
        deathMonthYear,
        updatedBy,
        updatedAt

    }


Table PersonnelInfoOther
    { 

        personnelId,
        personnelName ,                      
        updatedBy,
        updatedAt

    }

I define a class as follows:

  public class PersonnelInfoAll
    { 

        public short personnelId{get;set;}
        public string personnelName { get; set; }
        public string personnelNameOtherLan { get; set; }
        public string description { get; set; }
        public string deathMonthYear { get; set; }
        public int updatedBy { get; set; }
        public DateTime updatedAt { get; set; }  


    }


I need to left join first table with the second one and retrieve all the data as PersonnelInfoAll format:



public List<PersonnelInfoAllLan> GetPersonnelInfosAll()
    {
        var context = new BookDataClassesDataContext { ObjectTrackingEnabled = false };

        var personnelInfo = from u in context.PersonnelInfos
                            join b in context.PersonnelInfoOtherLans
                           on u.personnelId equals b.personnelId
                            select new PersonnelInfoAllLan
                            {
                                personnelId = u.personnelId,
                                personnelName = u.personnelName,
                                personnelNameOtherLan = b.personnelName,
                                description = u.description,
                                deathMonthYear = u.deathMonthYear,
                                updatedBy = u.updatedBy,
                                updatedAt = u.updatedAt
                            };





        return personnelInfo.ToList();
    }

This gives me only one row which matches with both. But I need all the records from the first table. Is there any one to help.

Answer 1

Use group join:

    var personnelInfo = from p in context.PersonnelInfos
                        join l in context.PersonnelInfoOtherLans
                          on p.personnelId equals l.personnelId into g
                        from l in g.DefaultIfEmpty()
                        select new PersonnelInfoAllLan
                        {
                            personnelId = p.personnelId,
                            personnelName = p.personnelName,
                            personnelNameOtherLan = (l == null ? null : l.personnelName),
                            description = p.description,
                            deathMonthYear = p.deathMonthYear,
                            updatedBy = p.updatedBy,
                            updatedAt = p.updatedAt
                        };

If there no match in lans for some person, then DefaultIfEmpty() will return null from joined group. That's why you need to check l for null.