DefaultIfEmpty - LINQ to SQL vs In Memory

For unit testing we use in memory collections to verify the logic of our LINQ queries. But, in the below scenario I'm seeing differences between the results from LINQ to SQL vs In Memory.

For this example we have three tables Customer, Order, Item. I would like the count of all items ordered by a customer. I would like to show customers who have not ordered any items as well. In SQL this would be an outer join. In LINQ to SQL I wrote this...

var itemCounts = 
   from c in Customer
   from o in Order.Where(oo=>o.CustomerId==c.CustomerId).DefaultIfEmpty()
   from i in Item.Where(ii=>i.OrderId==o.OrderId).DefaultIfEmpty()
   group i by new { i.ItemId, c.CustomerId } into ig
   select new ItemCountResult {
     CustomerId = ig.Key.CustomerId,
     Count = ig.Count()
   };

This works fine when we are going against the database. We get customers with and without orders along with counts. When we substitute in memory collections for unit testing we see an object reference not set exception. I've narrowed it down to the line "i.OrderId==o.OrderId" specifically o is null.

Based on how "DefaultIfEmpty" works, this is actually the behavior I would expect. DefaultIfEmpty returns a single element enumerable of null.

So how do I fix this code to work in both scenarios?

UPDATE: While I was simplifying the problem I lost some important pieces of information. So let me restate the problem.

A Customer has 0-n Orders. A Order has 1-n Items. A Item has 1-n Order.

I need the list of Items along with the number of customers that ordered that item. If 0 customers ordered the item I want it to still be returned but with a count of 0.

The problem is the many-to-many between Order and Item which prevents me from using the join-into syntax.

I currently have something like this (hopefully without mis开发者_StackOverflow社区types this time):

var counts =
  from i in Items
  from oi in OrderItems.Where(z=>z.ItemId==i.ItemId).DefaultIfEmpty()
  from o in Orders.Where(z=>z.OrderId==oi.OrderId).DefaultIfEmpty()
  from c in Customers.Where(z=>z.CustomerId==o.CustomerId).DefaultIfEmpty()
  group c by new { i.ItemId, c.CustomerId } into cg
  select new CountResult {
    CustomerId = cg.Key.CustomerId,
    Count = cg.Count()
  };

Your query is pooched to start with. This:

from ...
from o in Order.Where(oo=>o.CustomerId==c.CustomerId).DefaultIfEmpty()
from i in Item.Where(ii=>i.OrderId==o.OrderId).DefaultIfEmpty()

... is trying to use o before it's really in scope. I'm surprised that works at all. It looks like you want:

from ...
from o in Order.Where(oo => oo.CustomerId == c.CustomerId).DefaultIfEmpty()
from i in Item.Where(ii => ii.OrderId == o.OrderId).DefaultIfEmpty()

However, that still has the same problem - o will be null if there are no customers for c.CustomerId. The SQL translation may well not exhibit the same behaviour, but it's frankly a little odd to start with IMO.

Try this instead, assuming you have the right relationship set up:

from c in Customer
join i in Items on c.CustomerId equals i.Order.OrderId into items
select new { CustomerId = c.CustomerId, Count = items.Count() };

Here's another alternative, back to using explicit joins:

from c in Customer
join oi in (from o in Orders
            join i in Items on o.OrderId equals i.OrderId
            select new { o, i })
on c.CustomerId equals oi.o.CustomerId into ordersForCustomer
select new { CustomerId = c.CustomerId, Count = ordersForCustomer.Count() };