I have moved my active blog over to tumblr. I've maintained this blog for reference but will be posting to http://www.robustsoftware.co.uk instead. I've pointed my Feedburner feed to tumblr so if you're subscribed already you should already have switched with me.

Making LINQ to SQL associations perform inner joins

Today I had a problem whereby I had created an association but when the data for my object was retrieved it was joining the tables through a left outer join.

As these joins were non-nullable I wanted to make them perform an inner join to take advantage of the greater performance. In order to do this you need to set the ForeignKey attribute to "true" and CanBeNull to "false". The omission or reversal of either of these attributes will mean LINQ to SQL will assume that the parent object may not exist and will instead perform a left outer join

This will mean you end up with a XML mapping such as:

<Association Name=”SomeAssociation” Member=”Project” ThisKey=”ProjectId”
OtherKey=”Id” ForeignKey=”true” CanBeNull=”false” />

Of course you also need to create a DataLoadOption so that the parent object gets retrieved along with the child (eagerly):

var options = new DataLoadOptions();

options.LoadWith<Job>(j => j.Project);

context.LoadOptions = options;