We have a list of products that we are trying to get the number of sales for over a given period.
It's not all products and there are several ways to filter them, so each is it's own method so it can be optimised to join on fewer tables and then passes the IQuerable onto the main main method to do the heavy-lifting of getting all the details we need. I've not included that as it's a lot of code that isn't needed to create the issue.
(from bps in branchProductSeasons
join co in _db.CustomerOrders on bps.BranchID equals co.BranchID
into co2
from co3 in co2.Where(o => o.Timestamp >= startDate).DefaultIfEmpty(new CustomerOrder()) // Workaround to allow Moq to left join. It's not happy with a list of nulls
join col in _db.CustomerOrderLines
on new { CustomerOrderID = co3.ID, bps.ProductID } equals new { col.CustomerOrderID, col.ProductID }
into col2
from col3 in col2.DefaultIfEmpty()
where bps.SeasonID == seasonId
select new BranchProduct {
Id = bps.ProductID,
BranchId = bps.BranchID,
...
}
).ToList()
The new CustomerOrder() is needed in the DefaultIfEmpty in order for the second left join to work. Without it a NullReferenceException is thrown in tests. The code will run when compiled though.
This isn't a great solution as the result set will contain a CustomerOrder object for each record, meaning simple Counts now need an equality match.
Also note that the Timestamp Where needs to be on the join, or the result set would count items if they have had sales outside the time period and not within it.
Is there a way to get Moq to work without setting a value on DefaultIfEmpty?