Linq to SQL .Sum() without group ... into

Question

I have something like this:

var itemsInCart = from o in db.OrderLineItems
                  where o.OrderId == currentOrder.OrderId
                  select new { o.OrderLineItemId, ..., ..., o.WishListItem.Price}

is there any way to do a

itemsCart.Sum() //not sure what to pass into the function

to get the sum of o.WishListItem.Price or do i have to get another iQueryable< T> from the database with group ... into?

Elastic

Answer 1

What about:

itemsInCart.AsEnumerable().Sum(o=>o.Price);

AsEnumerable makes the difference, this query will execute locally (Linq To Objects).

Calenture

Answer 2

you can:

itemsCart.Select(c=>c.Price).Sum();

To hit the db only once do:

var itemsInCart = (from o in db.OrderLineItems
                  where o.OrderId == currentOrder.OrderId
                  select new { o.OrderLineItemId, ..., ..., o.WishListItem.Price}
                  ).ToList();
var sum = itemsCart.Select(c=>c.Price).Sum();

The extra round-trip saved is worth it :)

Solute

Could not translate expression '(...).Select(o => o.Price).Sum()' into SQL and could not treat it as a local expression. – Elastic

Try itemsCard.ToList().Select(c=>c.Price).Sum(); – Irreducible

@rm if you are getting the info anyway, do as Jonathan mentioned (save the list on a variable, so you only get the data once). – Solute

Jonathan - i'd accept your comment as answer .. you can post it here if you want the rep :) – Elastic

@rm updated version will hit the db only once and doesn't have the issue (instead of twice as the other solutions) – Solute

Answer 3

Try:

itemsCard.ToList().Select(c=>c.Price).Sum();

Actually this would perform better:

var itemsInCart = from o in db.OrderLineItems
              where o.OrderId == currentOrder.OrderId
              select new { o.WishListItem.Price };
var sum = itemsCard.ToList().Select(c=>c.Price).Sum();

Because you'll only be retrieving one column from the database.

Irreducible

I need other columns for other things, that's the whole reason i didn't want to get another IQueryable< T> from the DB – Elastic

Answer 4

I know this is an old question but why can't you do it like:

db.OrderLineItems.Where(o => o.OrderId == currentOrder.OrderId).Sum(o => o.WishListItem.Price);

I am not sure how to do this using query expressions.

Ecru

it will be nullreference error If no rows found, so you have to use .AsEnumerable().Sum(o=>o.Price) – Letishaletitia

@EugeneBosikov As has been noted in other answers, adding AsEnumerable will cause LINQ to retrieve the whole data set from the server and aggregate it locally, which is much less efficient than having the database do the work. Also, the way LINQ works you will not get a null reference error because it is building an execution path, and does not actually do any work until you output the results to a new collection. – Ecru

Answer 5

Try this:

var itemsInCart = from o in db.OrderLineItems
                  where o.OrderId == currentOrder.OrderId
                  select o.WishListItem.Price;

return Convert.ToDecimal(itemsInCart.Sum());

I think it's more simple!

Outsider