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

T-SQL TO DROP/CREATE IDENTITY SETTING ON ALL TABLES


/*
---------------------------------------------------------------------------------------------------------
PRINTS T-SQL TO DROP/CREATE IDENTITY SETTING ON ALL TABLES

BACKUP BEFORE RUNNING THE GENERATED SQL! - DROPS AND RECREATES EVERYTHING YOU SELECT.
ALSO PLEASE READ THE BELOW - THIS IS A SIMPLIFIED PURPOSE BUILT SCRIPT - YOU WILL NEED TO MODIFY TO YOUR REQUIREMENTS.

THERE IS NO WAY OF REMOVING THE IDENTITY(START, SEED) SETTING ON TABLES -
AND AS IDENTITY INSERT ON/OFF DOES NOT DO THE JOB FOR ALL ORMs (LIKE LINQ FOR EXAMPLE) WE'RE STUCK WITH THE
SQL SERVER MANAGEMENT IDE SOLUTION: CREATE TEMP TABLES - WITH/WITHOUT THE IDENTITY SETTING, MOVE THE DATA, DROP ORIGINAL AND RENAME TEMP TO ORG.

THIS IS A SIMPLIFIED VERSION THAT DOES NOT TAKE INTO CONSIDERATION FKs OR PKs - SCRIPTS TO DROP AND RECREATE THESE ARE AVAILABLE ELSEWHERE
ALSO IT ASSUMES THE PK IDENTITY IS AN INT COLUMN NAMED Id!

SQL2005 AND ABOVE ONLY (SYS VIEWS)

CONFIG:
@ADD_IDENTITY => set to true to ADD this, set to false to REMOVE this.

---------------------------------------------------------------------------------------------------------
*/

DECLARE @ADD_IDENTITY BIT
SET @ADD_IDENTITY = 0 --!!!!! SEE ABOVE!

DECLARE @TABLENAME NVARCHAR(250)
DECLARE @TABLESCHEMA NVARCHAR(250)
DECLARE @TSQL NVARCHAR(MAX)
DECLARE @COLS NVARCHAR(MAX)

DECLARE DBO_TABLE_CURSOR CURSOR

READ_ONLY
FOR SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
    WHERE
            TABLE_NAME NOT LIKE 'Version%'
        AND TABLE_NAME NOT LIKE 'sys%'
        AND TABLE_SCHEMA = 'dbo'
        AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY TABLE_NAME

OPEN DBO_TABLE_CURSOR
FETCH NEXT FROM DBO_TABLE_CURSOR INTO @TABLENAME, @TABLESCHEMA
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
        SET @TSQL = ''
        
        --Get rid of default constraints:
        SELECT @TSQL = @TSQL + '    
        ALTER TABLE dbo.' + @TABLENAME +'
            DROP CONSTRAINT ' + DF.[NAME] + '
        GO' FROM
            SYS.DEFAULT_CONSTRAINTS DF
                INNER JOIN SYS.ALL_COLUMNS AC
                    ON        DF.PARENT_OBJECT_ID = AC.OBJECT_ID
                        AND DF.PARENT_COLUMN_ID = AC.COLUMN_ID
        WHERE OBJECT_NAME(PARENT_OBJECT_ID) = @TABLENAME

        --Create temp table:
        SET @TSQL = @TSQL + '
        
        CREATE TABLE dbo.Tmp_' + @TABLENAME + '
        (
        '        
        SELECT @TSQL = @TSQL + '    [' + COLUMN_NAME + '] ' + UPPER(DATA_TYPE)
            + ISNULL(('(' + CONVERT(NVARCHAR,CHARACTER_MAXIMUM_LENGTH) + ') '), ' ') --Bits and Timestamps have null in all three, something added to null, is null - so we ISNULL check the whole evalutation, and add a space for bits and timestamps.
            + CONVERT(NVARCHAR, REPLACE(REPLACE(IS_NULLABLE, 'YES', 'NULL'), 'NO', 'NOT NULL'))            
            + ISNULL(' CONSTRAINT [DF_' + @TABLENAME + '_' + COLUMN_NAME + '] DEFAULT (' + CONVERT(NVARCHAR, COLUMN_DEFAULT) + ')', '')
            + ',
        '
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
                TABLE_NAME = @TABLENAME
            AND TABLE_SCHEMA = @TABLESCHEMA
        ORDER BY ORDINAL_POSITION ASC

        --Remove trailing comma:
        SET @TSQL = SUBSTRING(@TSQL, 1, LEN(@TSQL) -5) --remove trailing tabs, linebreak and finally the comma
        
        SET @TSQL = @TSQL + '    
        ) ON [PRIMARY]
        GO'
        
        --So Microsoft seem to have hacked the old NVARCHAR(MAX) - the CHARACTER_MAXIMUM_LENGTH is a int column - so they've added -1 here
        SET @TSQL = REPLACE(@TSQL, '(-1)', '(MAX)')

        --Finally - are we adding or removing the IDENTITY setting?
        IF @ADD_IDENTITY = 1
        BEGIN
            SET @TSQL = REPLACE(@TSQL, '[Id] INT NOT NULL,', '[Id] INT NOT NULL IDENTITY (1,1),')
        END

        PRINT @TSQL -- Half way there! Got temp tables, now let's move the data, drop the original, and rename the temp to replace the original... what could possibly go wrong?
        
        --RESET @TSQL:
        SET @TSQL = '
        IF EXISTS(SELECT * FROM dbo.' + @TABLENAME + ')
        BEGIN '
        IF @ADD_IDENTITY = 1--If so we need to set "IDENTITY_INSERT ON" on the temp tables.
        BEGIN
        SET @TSQL = @TSQL + '    
            SET IDENTITY_INSERT dbo.Tmp_' + @TABLENAME + ' ON'
        END

        --Now get all the column names:
        SET @COLS = ''
        SELECT @COLS = @COLS + '[' + COLUMN_NAME + '],'
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE
                TABLE_NAME = @TABLENAME
            AND TABLE_SCHEMA = @TABLESCHEMA
            AND DATA_TYPE 'timestamp'
        ORDER BY ORDINAL_POSITION ASC
    
        --Remove trailing comma:
        SET @COLS = SUBSTRING(@COLS, 1, LEN(@COLS) -1)
        SET @TSQL = @TSQL + '
            INSERT INTO dbo.Tmp_' + @TABLENAME + ' (' + @COLS + ')
                SELECT ' + @COLS + '
            FROM dbo.' + @TABLENAME + ' WITH (HOLDLOCK TABLOCKX)
        '        

        IF @ADD_IDENTITY = 1 --If we're adding this, then we have switched IDENTITY_INSERT ON - now switch this back off (only one table at any one time in each DB)
        BEGIN
        SET @TSQL = @TSQL + '    
            SET IDENTITY_INSERT dbo.Tmp_' + @TABLENAME + ' OFF'
        END
        
        SET @TSQL = @TSQL + '
        END'

        SELECT @TSQL = @TSQL + '
        DROP TABLE dbo.' + @TABLENAME + '
        GO'
        SELECT @TSQL = @TSQL + '
        EXECUTE sp_rename N''dbo.Tmp_' + @TABLENAME +''', N''' + @TABLENAME +''', ''OBJECT''
        GO'

        PRINT @TSQL
        
END
FETCH NEXT FROM DBO_TABLE_CURSOR INTO @TABLENAME, @TABLESCHEMA
END

CLOSE DBO_TABLE_CURSOR
DEALLOCATE DBO_TABLE_CURSOR