CleanCode NZ

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: LINQ