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.

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.

 

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

Unit Testing Linq To Entities

We have decided that we're going to use Linq to Entities and the Entity Framework Model in our new project. This means that for our model we are going to have to create unit tests that mimic the database environment. We don't want our unit tests to connect to the database itself because that will make the unit tests unnecessarily lengthy and put the developers in a position where they will avoid doing the unit tests where possible. Also when doing unit testing against the database you must make sure that the data int he database is correct before doing the unit test. The solution is to create your own data in code or with XML that you can test against reliably. I came across this blog by Ian Cooper that goes trough a fantastic way to create your own data in exactly this fashion. If you're using Linq to Entities or Linq to SQL and you are trying to follow TDD, I highly recommend reading this blog.