Applying aggregates to empty collections causes exception in Linq To SQL

I spent nearly two hours trying to figure out what was wrong with my Linq to SQL query. Basically I was trying to calculate the sum of sales generated by each sales agent. In order to accomplish that I had to travel from employee table to Orders which had the employeeid linked to it. From the Order table I navigated to its order details and calculated the sum of Quantity ordered multiplied by Unit Price. The query works fine when each agent has put in an order in the database. But when there is no order record for an agent the sum operation performed on SQL server returns null. When Linq to SQL tries to assign null values to data types that is not defined as null, it throws an exception. Below is an example that demonstrates this issue.

In the above query, I am displaying the Employee's First name and for each employee getting its orders. For every order, I grab its order details and than flatten all order details for all orders for a particular customer using SelectMany operator. Once I have all the OrderDetails, I apply sum operator operator to get TotalRevenue generated by each employee. The above query causes exception because there are some employees that do not have any orders and Linq to SQL raises exception complaining that null value cannot be assigned to a member with type System.Decimal which is non-nullable. What this means is in our lambda expression od.UnitPrice and od.Quantity is defined as decimal and short where as employees that don’t have orders and thus does not have any order details, the value for unit price and quantity is null. Since you cannot assign null value to a data type that is non-nullable, Linq to SQL throws an exception.

To solve the problem, simply do an explicit cast to nullable type and your query would execute fine. Here is the corrected version of the query written in two different ways. Both ways require an explicit cast to nullable type.

In the above example, I have two different queries that return the same result. I was simply trying to force Linq to SQL to generate two different query plans and sure enough modifying the query generates unique SQL queries but they both met the met same fate and caused exception unless you do a correct cast to nullable type.