log for me

Left outer joins in linq to sql are somewhat hard to grasp, at least for me. I was very happy when I managed to figure out how to make a left outer join with a parameter, something that is a common procedure in one of the projects we are developing; the system has a lot of language-dependent data in child-tables that needs to be outer joined so that we still get the fields out even though something is missing a translation.

T-Sql

I am starting this posting by showing what I want to accomplish in T-SQL, to set the scene for the DLINQ stuff that comes down below. This is not hard at all to accomplish in T-SQL, but in order to show the example data and how it fits together, I chose to start out with this section.


The data source I will run this example on looks like the following:

Here is a script to create the sample datasource in SQLEXPRESS (I used LINQPad):

CREATE DATABASE LEFTOUTERTEST
USE LEFTOUTERTEST

CREATE TABLE Note (
	Id INT NOT NULL PRIMARY KEY,
	NoteTypeId INT NOT NULL
)
CREATE TABLE NoteType (
	Id INT NOT NULL PRIMARY KEY
)
CREATE TABLE NoteLang (
	NoteId INT NOT NULL,
	LanguageId INT NOT NULL,
	Text NVARCHAR(200),
	CONSTRAINT [PK_NoteLang] PRIMARY KEY CLUSTERED
	(
		NoteId ASC,
		LanguageId ASC
	)
)
CREATE TABLE Language (
	Id INT NOT NULL PRIMARY KEY
)
ALTER TABLE Note  WITH CHECK ADD CONSTRAINT [FK_Note_NoteType] FOREIGN KEY([NoteTypeId]) REFERENCES [NoteType] (Id)
ALTER TABLE NoteLang  WITH CHECK ADD CONSTRAINT [FK_NoteLang_Language] FOREIGN KEY([LanguageId]) REFERENCES [Language] (Id)
ALTER TABLE NoteLang  WITH CHECK ADD CONSTRAINT [FK_NoteLang_Note] FOREIGN KEY([NoteId]) REFERENCES [Note] (Id)
-- insert two note types
INSERT NoteType VALUES (1)
INSERT NoteType VALUES (2)
-- insert three notes
INSERT Note VALUES (1,1)
INSERT Note VALUES (2,1)
INSERT Note VALUES (3,2)
-- insert two languages
INSERT Language VALUES (1)
INSERT Language VALUES (2)

-- insert language-dependent data
INSERT NoteLang VALUES (1, 1, N'Note id 1, language 1')
INSERT NoteLang VALUES (1, 2, N'Note id 1, language 2')
INSERT NoteLang VALUES (2, 1, N'Note id 2, language 1')
INSERT NoteLang VALUES (3, 1, N'Note id 3, language 1')

View of the sample data, note that Notes with ID 2 and 3 are not translated into language with ID=2.

SELECT
	NoteId,
	Text,
	LanguageId
FROM
	NoteLang

Results:

The following is what we are trying to accomplish, join all rows that lacks translation to a specific language:

SELECT
	note.Id,
	note.NoteTypeId,
	noteLang.Text,
	noteType.Id as NoteTypeId
FROM
	Note note
LEFT OUTER JOIN NoteLang noteLang on noteLang.NoteId = note.Id and noteLang.LanguageId = 2
INNER JOIN NoteType noteType on note.NoteTypeId = noteType.Id

Results:

Linq to sql

Using linq to sql, the method of doing a left outer join is documented here among other places. I have not found any example on how to add a parameter (as the example above; LanguageId) to the left outer join query, in order to specifically outer join some of the matching outer data.

from note in Note
join noteLang in NoteLang on note.Id equals noteLang.NoteId into noteLangs
join noteType in NoteType on note.NoteTypeId equals noteType.Id
from noteLangGroup in noteLangs.Where(l => l.LanguageId == 2).DefaultIfEmpty()
select new {
	NoteId = note.Id,
	LanguageId = (noteLangGroup != null ? noteLangGroup.LanguageId : 2),
	Text = (noteLangGroup != null ? noteLangGroup.Text : null),
	NoteTypeId = note.NoteTypeId
}

Results from the LINQ query for the same datasource:

peter Code, LINQ DLINQ, LINQ