Friday 19 December 2008

Testable Left Outer Join in LINQ to Entities

Recently I posted on Testable LINQ to Entities using the repository model and doing table joins using your query items.  I’ve found that while this works great for inner joined table queries it doesn’t work for left outer joins, in fact doing a left outer join in LINQ to Entities is quiet hard entirely.

So in my example, here is the SQL used to retrieve the data.

SELECT * FROM
Item
    LEFT OUTER JOIN ItemLocation il
        on Item.ItemCodeID = il.ItemCodeID
    LEFT OUTER JOIN WarehouseLocation wl
        on il.WarehouseLocationID = wl.WarehouseLocationID
    LEFT OUTER JOIN Warehouse w
        on wl.WarehouseID = w.WarehouseID
    LEFT OUTER JOIN Site s
        on w.SiteID = s.SiteID

In LINQ I can do this far more simply:

from item in _ItemData
select item

But when I try and look at the resulting item.ItemLocation it returns an empty list.  When using LINQ to Entities it will only load the data model for items you tell it to load for, in this case I asked for Item, but not all the ItemLocations as well.  This is shown in the screenshot below, the array of item locations has no items even though they are in the database.

image

I want the item to return even though I don’t necessarily have any locations, and I want to be able to get the site.

Well here is how you do a left outer join in LINQ to Entities:

var results = (from item in _DataSource
               where item.Name.Equals(name)
               // Left outer join into the item locations, warehouses and sites to calculate the data.
               let itemLoc = (from itemLocLeftOuter in item.ItemLocation
                              select new
                              {
                                  itemLocLeftOuter,
                                  itemLocLeftOuter.WarehouseLocation,
                                  itemLocLeftOuter.WarehouseLocation.Warehouse,
                                  itemLocLeftOuter.WarehouseLocation.Warehouse.Site
                              })
               select new
               {
                   item,
                   item.Class,
                   item.ItemLocation,
                   itemLoc
               }).FirstOrDefault();
 
Item item = results.item;

I have to join into the ItemLocations because for each item there can be many locations.  But selecting all the data I need, including joined data, my resulting item will have all it’s dependencies loaded from the database.  The screenshot of the watch below shows that my item.ItemLocation holds all the joined items now.

image 

As with my previous post on testable LINQ to Entities, this code is completely testable and works very well within the repository pattern.

No comments: