Linq Sum() Question

[Olivier.Paesbrugghe]

I'm having a problem with a Linq statement.
I made a console application that makes use of Northwind database.
Made a dbml file of the database whith the designer and now I want to
see the total of all orders of the customers.
I used this statement:

var custTotalOrders = db.Customers
.Select( p =>
new { c.ContactName,
TotalOrders = c.Orders.Sum( o => o.Order_Details.Sum( od =>
od.Quantity * od.UnitPrice))});

(Un)fortunately there is 1 client who hasn't made any orders yet and
so I get an error:

Unhandled Exception: System.InvalidOperationException: The null value
cannot be
assigned to a member with type System.Decimal which is a non-nullable
value type.

Is there something wrong with my expression or is this an error due to
the fact LINQ is still in beta?

kind regards,

Olivier

 

[Nicholas Paldino [.NET/C# MVP]]

Oliver,

I'm going to assume that Quantity and Price are double/int types, and
not their nullable counterparts. If you use the nullable counterparts, it
should work, like so:

var custTotalOrders = db.Customers
.Select( p =>
new { c.ContactName,
TotalOrders = c.Orders.Sum( o => o.Order_Details.Sum( od =>
(double?) od.Quantity * (double?) od.UnitPrice))});

Of course, this means that you will have to account in your code for
when TotalOrders is null, or assign it to 0.

 

[Willy Denoyette [MVP]]

I'm having a problem with a Linq statement.
I made a console application that makes use of Northwind database.
Made a dbml file of the database whith the designer and now I want to
see the total of all orders of the customers.
I used this statement:

var custTotalOrders = db.Customers
.Select( p =>
new { c.ContactName,
TotalOrders = c.Orders.Sum( o => o.Order_Details.Sum( od =>
od.Quantity * od.UnitPrice))});

(Un)fortunately there is 1 client who hasn't made any orders yet and
so I get an error:

Unhandled Exception: System.InvalidOperationException: The null value
cannot be
assigned to a member with type System.Decimal which is a non-nullable
value type.

Is there something wrong with my expression or is this an error due to
the fact LINQ is still in beta?

kind regards,

Olivier

Supposing UnitPrice is a 'decimal' type...
TotalOrders = c.Orders.Sum( o => (decimal?) o.Order_Details.Sum( od =>...

or only select the customer who have orders:

db.Customers
.Where(q => q.Orders.Count > 0)
.Select(...


Willy.

 

[Frans Bouma [C# MVP]]

I'm having a problem with a Linq statement.
I made a console application that makes use of Northwind database.
Made a dbml file of the database whith the designer and now I want to
see the total of all orders of the customers.
I used this statement:

var custTotalOrders = db.Customers
.Select( p =>
new { c.ContactName,
TotalOrders = c.Orders.Sum( o => o.Order_Details.Sum( od =>
od.Quantity * od.UnitPrice))});

(Un)fortunately there is 1 client who hasn't made any orders yet and
so I get an error:

Unhandled Exception: System.InvalidOperationException: The null value
cannot be
assigned to a member with type System.Decimal which is a non-nullable
value type.

Is there something wrong with my expression or is this an error due to
the fact LINQ is still in beta?

As the others said, you have to convert the fields to a nullable type.
The real reason this happens is that the result of the SUM aggregate
results in NULL and it can't put null into TotalOrders.

The query this results in is interesting though: it's not that
efficient. One would otherwise use a groupby with 2 joins which is far
more efficient, however that one is pretty hard to write into Linq
syntaxis (if not impossible, I haven't managed to achieve it). (select
c.contactname, SUM(quantity * unitprice) as total
from customers c inner join orders o
on c.customerid = o.customerid
inner join [order details] od
on o.orderid = od.orderid
group by c.contactname)

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[OlivierStudent]

I'm having a problem with a Linq statement.
I made a console application that makes use of Northwind database.
Made a dbml file of the database whith the designer and now I want to
see the total of all orders of the customers.
I used this statement:

var custTotalOrders = db.Customers
.Select( p =>
new { c.ContactName,
TotalOrders = c.Orders.Sum( o => o.Order_Details.Sum( od =>
od.Quantity * od.UnitPrice))});

(Un)fortunately there is 1 client who hasn't made any orders yet and
so I get an error:

Unhandled Exception: System.InvalidOperationException: The null value
cannot be
assigned to a member with type System.Decimal which is a non-nullable
value type.

Is there something wrong with my expression or is this an error due to
the fact LINQ is still in beta?

kind regards,

Olivier

Yup, it works now
Thanks for the quick responses and the helpfull information.

I'll be posting questions about linq here more frequently
Have to study it and make a thesis about it, this discussiongroup will
be a great help for me.

Olivier