How to limit a LINQ left outer join to one row

Question

I have a left outer join (below) returning results as expected. I need to limit the results from the 'right' table to the 'first' hit. Can I do that somehow? Currently, I get a result for every record in both tables, I only want to see one result from the table on the left (items) no matter how many results I have in the right table (photos).

        var query = from i in db.items                 join p in db.photos                 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.PhotoID.ToString()                 };       GridView1.DataSource = query;     GridView1.DataBind(); 

Answer 1

This will do the job for you.

from i in db.items let p = db.photos.Where(p2 => i.id == p2.item_id).FirstOrDefault() orderby i.date descending select 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"> 

Answer 2

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.