Limit Linq Outer Join Left To One Row

When we use DefaultIfEmpty we are doing an outer join, and if there is no match on right table, there will be null for the right table fields in joined big table, we are happy about this, but if sometime the right table has multiple matches for one left table entry, this will eventually multiply left table entries in the final big table, so we do not want this, if there are more matches in right table we want it to limit to one.

From here I found a good example using ‘let’ in Linq which essentially is using sub queries.

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();
}

But here I am going to present another workaround, using group and pick one usually last or first, then left join this data set as usual.

Let us just use the same table as the ‘let’ example

 from i in db.items

 join oneorno_p in 
   from p in db.photos
   orderby p.date descending
   group p by p.item_id   into g_item_p
   select g_item_p.First()
on i.id equals oneorno_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 =tempPhoto==null?"No Photo Found ": tp.PhotoID.ToString()
  };

Happy coding

Tags:

This entry was posted on Saturday, October 9th, 2010 at 6:13 am and is filed under ASP.NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Limit Linq Outer Join Left To One Row”

  1. Hrafnkell says:

    Nice code snippet. I was trying it with EF 5 and Oracle database and linq generates a sql statement that isn’t legal for oracle and uses apply. But still this might be usefully to those that are using EF6 or not using oracle.

Leave a Reply

*