Multiple Left Joins in Linq to SQL

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
                                                 };
                                                 ………………

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: