Had some fun with this – rubbish formatting below – but paste into Visual Studio and autoformat it (ctrl+e+d).
I’ll write some details on this later – but the basic principle is that if you have a nullable foreign key – i.e. you are not sure if you have matches at the end of your table relation and you would normally do a SQL LEFT JOIN, what you need to do in Linq is use the into clause to declare a collection for the results of your join – the reason being that you can then use the DefaultIfEmpty() method in a subsequent from clause to return empty objects where there is no match.
Couple of examples below – first creates a anonymous object on the fly – here you have to null check to set values. The second example beforehand declares a little helper class with Nullable id’s – which gets you round the null checking.
Note that the below uses multiple from clauses to handle the mutiple left join quires (so basically we select from each declared sub-collection).
from p in Persons
join l in Languages on p.PrimaryLanguageId equals l.Id into tempResult
join jt in JobTitles on p.JobTitleId equals jt.Id into anotherTempResult
from tr in tempResult.DefaultIfEmpty()
from atr in anotherTempResult.DefaultIfEmpty()
orderby p.Id
where p.Deleted == null
select new {
PersonId = p.Id,
LanguageId = tr == null ? new Nullable() : tr.Id,
LanguageName = tr.Name,
JobTitleId = atr == null ? new Nullable() : atr.Id,
JobTitleName = atr.Name
}
And a more extensive example:
///
/// Person Grid Helper.
///
///
/// Linq joins would normally create an anonymous type
/// – however we cannot use anon types as the grid collection is passed to another method when searching (LoadPersonForSearch).
///
private class PersonGridHelper
{
public int Id { get; set; }
public string GivenName { get; set; }
public string FamilyName { get; set; }
public string OfficeLocation { get; set; }
public Nullable OfficeLocationId { get; set; }
public string JobTitle { get; set; }
public Nullable JobTitleId { get; set; }
public string Email { get; set; }
public string EmailDomain { get; set; }
public int EmailDomainId { get; set; }
public string AlternateEmail { get; set; }
public string Mobile { get; set; }
public string Telephone { get; set; }
public string Fax { get; set; }
public string CVFile { get; set; }
public string ProfilePictureFile { get; set; }
public string PrimaryLanguage { get; set; }
public Nullable PrimaryLanguageId { get; set; }
}
///
/// Loads the main browse grid.
///
public override void LoadGrid()
{
IQueryable coll = from person in this.CurrentDataContext.Persons
join officelocation in this.CurrentDataContext.OfficeLocations
on person.OfficeLocationId equals officelocation.Id
join jobtitle in this.CurrentDataContext.JobTitles
on person.JobTitleId equals jobtitle.Id into jobtitles //Nullable FK => left join, DefaultIfEmpty() below
join language in this.CurrentDataContext.Languages
on person.PrimaryLanguageId equals language.Id into languages //Nullable FK => left join, DefaultIfEmpty() below
join firmemaildomain in this.CurrentDataContext.FirmEmailDomains
on person.EmailDomainId equals firmemaildomain.Id
from jobtitle in jobtitles.DefaultIfEmpty() //Left join, records from jobtitles collection, if missing default constructor is called
from language in languages.DefaultIfEmpty() //Left join, records from jobtitles collection, if missing default constructor is called
orderby person.FamilyName
where person.Deleted == null
select new PersonGridHelper()
{
Id = person.Id
, GivenName = person.GivenName
, FamilyName = person.FamilyName
, OfficeLocation = officelocation.Name
, OfficeLocationId = person.OfficeLocationId
, JobTitle = jobtitle.Name
, JobTitleId = person.JobTitleId
, Email = person.Email
, PrimaryLanguage = language.Name
, PrimaryLanguageId = person.PrimaryLanguageId
, EmailDomain = firmemaildomain.Domain
, EmailDomainId = person.EmailDomainId
};
………………
