Howto: SQL / LINQ JOIN on TOP 1 row

Problem: you want to do a LEFT or INNER JOIN between two tables but include only one record from the other table: that is, you don’t want the join to create duplicate records. Interestingly enough, I found the solution to this through LINQ. In LINQ, you can do this without really thinking about it:

from cmp in ctx.Companies join pers in ctx.Persons on cmp.Persons.First().ID equals pers.ID

Surprisingly for me, this query gets translated into working SQL, which looks something like this (note that I cleaned it up quite a bit for readability):

FROM Company INNER JOIN Person ON ( SELECT TOP (1) top1Person.ID FROM Person AS top1Person WHERE top1Person.CompanyID = Company.ID ) = Person.ID

Once you think about it, the solution is quite simple. All you need to remember is that a JOIN can contain subselects (even subselects with their own JOINs).