Linq Sum() Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Olivier.Paesbrugghe@gmail.com - 13 Sep 2007 12:42 GMT

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

Reply to this Message

Nicholas Paldino [.NET/C# MVP] - 13 Sep 2007 13:32 GMT

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.

Signature

- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

> I'm having a problem with a Linq statement.
> I made a console application that makes use of Northwind database.
[quoted text clipped - 22 lines]
>
> Olivier

Reply to this Message

Willy Denoyette [MVP] - 13 Sep 2007 21:03 GMT

> I'm having a problem with a Linq statement.
> I made a console application that makes use of Northwind database.
[quoted text clipped - 22 lines]
>
> 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.

Reply to this Message

Frans Bouma [C# MVP] - 14 Sep 2007 12:26 GMT

> I'm having a problem with a Linq statement.
> I made a console application that makes use of Northwind database.
[quoted text clipped - 18 lines]
> 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

Signature

------------------------------------------------------------------------
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#)
------------------------------------------------------------------------

Reply to this Message

OlivierStudent - 14 Sep 2007 12:50 GMT

On 13 sep, 13:42, Olivier.Paesbrug...@gmail.com wrote:

> I'm having a problem with a Linq statement.
> I made a console application that makes use of Northwind database.
[quoted text clipped - 22 lines]
>
> 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

Reply to this Message

Linq Sum() Question

Free Magazines