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.
Use group join:
If there no match in lans for some person, then
DefaultIfEmpty()
will returnnull
from joined group. That's why you need to checkl
fornull
.