Exception handling in Linq to SQL for customers without orders

Question

I have the following code to retrieve customer name, total (orders ), sum (order details) for reach customer in Northwind database. The problem with below code is that it raises an exception since a few customers dont have any entry in orders table.

I know using the query syntax (join) the exception can be avoided. I want to know if the same can be handled with the extension method syntax.

 var customerOrders = db.Customers
    .Select(c => new 
    { 
        CompanyName = c.CompanyName, 
        TotalOrders = c.Orders.Count(), 
        TotalQuantity = c.Orders
            .SelectMany(o => o.Order_Details).Sum(o=>o.Quantity) 
    });

Answer 1

I don't see the exception here except customerOrders could be null. What did I miss? Edit: OK i see that the problem is with the SelectMany operator

The problem might be that TotalQuantity is not nullable.

Answer 2

The query syntax is only eye candy and is translated into extension methods during compilation. Further the extension methods are strictly more powerful because there is no query syntax for all extension methods. You can just use Enumerable.Join to perform a join with extension methods.

But I am unable to spot the problem - I would not expect your code to cause an exception. What is the actual exception and where does it occur?

Answer 3

I think the problem is that in SQL, the SUM function can return null and Ling-to-SQL is expecting an int. You can get around this by doing this:

  TotalQuantity = 
    (int?)c.Orders.SelectMany(o => o.Order_Details).Sum(o=>o.Quantity)

and then checking for null values. I think this will also work

  TotalQuantity = 
    ((int?)c.Orders.SelectMany(o => o.Order_Details).Sum(o=>o.Quantity)) ?? 0

if you want it to default to 0.

The reason this happens is because the SQL that Linq-to-SQL generates results in a null value for the quantity column when there are no details. An easy way to determine what this issue is with this kind of error is to set db.Log to Console.Out and copy the generated SQL into SSMS and see what the results are. You'll likely see that the generated SQL creates a left join which results in one or more null values in the Quantity column. Either that, or there will be an subquery that results in the null value.

Answer 4

Is Quantity nullable? I suspect that's actually where the exception is happening, since all the other cases ought to just be an empty enumeration which will not cause a null exception.

The exception details would certainly help!