CODEWITHSUNDEEP
c#linqlinq-to-sqlexceptionnull
George Duckett
George Duckett

Reputation: 32428

LINQ to SQL and null values

I have the following LINQ to SQL query:

var inTransitStocks = orderHistories.Where(oh => oh.Shipped_Qty > 0)
                                    .Select(oh => oh.Shipped_Qty); //.ToList();
var inTransitStock = (int)inTransitStocks.Sum();

Without the ToList call I get the exception below on the Sum() line:

The null value cannot be assigned to a member with type System.Double which is a non-nullable value type.

If I add a .ToList() before sum (as shown in the comment) I don't get the error.

Why do I get the error in the first place? (Shipped_Qty is not null and no null data in that field exists in the db)

Why is adding ToList() a fix?

The sql query executed is below (there is more to the query than above):

SELECT [t0].[Shipped Qty]
FROM [dbo].[Order History] AS [t0]
WHERE ([t0].[Shipped Qty] > @p0) AND ([t0].[CUST_ID] = @p1) AND ([t0].[SHIP_TO_ID] = @p2) AND ([t0].[Item] = @p3) AND (([t0].[DT_LST_SHP] >= @p4) OR (UNICODE([t0].[LN_STA]) = @p5))

No results are returned.

Upvotes: 5

Views: 821

Answers (3)

tschmit007
tschmit007

Reputation: 7800

all was said but let me reformulate : this is because of the magic of var!

Without ToList() var <=> DbQuery
With ToList() var <=> List<Double>

The Sum function does not have the same behaviour on the two types...

Upvotes: 0

Jodrell
Jodrell

Reputation: 35716

After you do the ToList() you are using the Linq-To-Objects implementation of Sum.

Before you do ToList() the Sum operation is being aggregated into the Linq-To-Sql query.

To find out why the Linq-To-Sql fails, follow Daniel Hilgath's approach.

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

The reason is the following:

Without ToList the following query gets executed against the database:

select SUM(Shipped_Qty) from orderHistories where Shipped_Qty > 0;

If there are no rows matching this criteria, the result of this query is not 0 but NULL.

With ToList the following query gets executed:

select Shipped_Qty from orderHistories where Shipped_Qty > 0;

The result (no rows) will be put into a list. The result is an empty list. On that empty list you execute the LINQ to Objects extension method Sum. The sum of an empty list is 0 and NOT null.

So basically: Different semantics lead to different results.

Upvotes: 6

Related Questions