How to limit a LINQ left outer join to one row

This will do the job for you.

from i in db.itemslet p = db.photos.Where(p2 => i.id == p2.item_id).FirstOrDefault()orderby i.date descendingselect new{  itemName = i.name,  itemID = i.id,  id = i.id,  photoID = p == null ? null : p.PhotoID.ToString();}

I got this sql when I generated it against my own model (and without the name and second id columns in the projection).

SELECT [t0].[Id] AS [Id], CONVERT(NVarChar,(    SELECT [t2].[PhotoId]    FROM (        SELECT TOP (1) [t1].[PhotoId]        FROM [dbo].[Photos] AS [t1]        WHERE [t1].[Item_Id] = ([t0].[Id])        ) AS [t2]    )) AS [PhotoId]FROM [dbo].[Items] AS [t0]ORDER BY [t0].[Id] DESC

When I asked for the plan, it showed that the subquery is implemented by this join:

<RelOp LogicalOp="Left Outer Join" PhysicalOp="Nested Loops">

What you want to do is group the table. The best way to do this is:

    var query = from i in db.items                join p in (from p in db.photos                           group p by p.item_id into gp                           where gp.Count() > 0                           select new { item_id = g.Key, Photo = g.First() })            on i.id equals p.item_id into tempPhoto            from tp in tempPhoto.DefaultIfEmpty()            orderby i.date descending             select new            {                itemName = i.name,                itemID = i.id,                id = i.id,                photoID = tp.Photo.PhotoID.ToString()            };

Edit: This is Amy B speaking. I'm only doing this because Nick asked me to. Nick, please modify or remove this section as you feel is appropriate.

The SQL generated is quite large. The int 0 (to be compared with the count) is passed in via parameter.

SELECT [t0].X AS [id], CONVERT(NVarChar(MAX),(    SELECT [t6].Y    FROM (        SELECT TOP (1) [t5].Y        FROM [dbo].[Photos] AS [t5]        WHERE (([t4].Y IS NULL) AND ([t5].Y IS NULL)) OR (([t4].Y IS NOT NULL) AND ([t5].Y IS NOT NULL) AND ([t4].Y = [t5].Y))        ) AS [t6]    )) AS [PhotoId]FROM [dbo].[Items] AS [t0]CROSS APPLY ((        SELECT NULL AS [EMPTY]        ) AS [t1]    OUTER APPLY (        SELECT [t3].Y        FROM (            SELECT COUNT(*) AS [value], [t2].Y            FROM [dbo].[Photos] AS [t2]            GROUP BY [t2].Y            ) AS [t3]        WHERE (([t0].X) = [t3].Y) AND ([t3].[value] > @p0)        ) AS [t4])ORDER BY [t0].Z DESC

The execution plan reveals three left joins. At least one is trivial and should not be counted (it brings in the zero). There is enough complexity here that I cannot clearly point to any problem for efficiency. It might run great.

You could do something like:

var q = from c in          (from s in args           select s).First()        select c;

Around the last part of the query. Not sure if it will work or what kind of wack SQL it will produce :)