How to perform SUM operation in Entity Framework

Question

I have a table.

create table tblCartItem(
pkCartItemId int primary key identity,
CartId int not null,
ProductId int not null,
Quantity int not null,
Price nvarchar(15)
)

and I want to perform sum opeartion on that like as

Select SUM(Price) from tblCartItem where CartId='107'

and I am trying to following code but its not working

ObjTempCart.CartTotal = (from c in db.tblCartItems where c.CartId == cartId select c.Price).Sum();

Any one help me to do this using Entity Framework. I am using MVC 4 Razor.

Solution 1

May be You can use lambda Expression

var total=db.tblCartItems.Where(t=>t.CartId == cartId).Sum(i=>i.Price);

Solution 2

its working try this..

use Decimal.Parse to convert price.

ObjTempCart.CartTotal =  db.tblCartItems.Where(t=>t.CartId == cartId).Select(i=>Decimal.Parse(i.Price)).Sum();

Solution 3

Finally I have a solution of that but its not exactly from Entity Framework, But its working...

 private double CartItemTotalPrice(Int32 CartID)
        {
            List<string> pricelst = new List<string>();
            pricelst = (from c in db.tblCartItems where c.CartId == CartID select c.Price).ToList();

            double Total = 0;

            if (pricelst != null)
            {
                for (int i = 0; i < pricelst.Count; i++)
                {
                    Total += Convert.ToDouble(pricelst[i]);
                }
            }

            return Total;
        } 

Comments

[Ivan Stoev]

Oops, I think I saw it. Why Price is string (nvarchar(15))?

[Durgesh Pandey]

Can not implicitly convert to 'string' to 'decimal' because in database my Price column is in NVARCHAR, to I do var total = db.tblCartItems.Where(t => t.CartId == cartId).Sum(i => Convert.ToDecimal(i.Price));

[Durgesh Pandey]

but now, LINQ to Entities does not recognize the method 'System.Decimal ToDecimal(System.String)' method, and this method cannot be translated into a store expression.

[hemantsharma]

its fine, so what values you have in price column? you may specify select first before that like var total = db.tblCartItems.Where(t => t.CartId == cartId).Select(p=>CovertToDecial(p)).Sum(i => i);

[Durgesh Pandey]

same problem, LINQ to Entities does not recognize the method 'System.Decimal ToDecimal(System.Object)' method, and this method cannot be translated into a store expression.

[Álvaro García]

With this solution you are getting all the information to the client and do the work in the client, so it could be a problem with the performance depending on some cases. But it works, sure.