How do I properly convert a string to decimal in my linq query?

Question

In my C# MVC 5 application, I am trying to use a linq query to convert a column in my sql server table that is an NVarChar to a decimal and display it in the view.

I know that if I was to do a simple SQL query or was using webforms I could simply do the following:

Select Sum(Cast(GrandTotal as decimal)) as GrandTotal From [myDB].[dbo].[Table] 
WHERE Year = '2017'

How would I do this in linq? This is what I tried:

public ActionResult Total()
{
    var model = (from p in db.Table
                where p.Year.Equals("2017")
                select decimal.Parse(p.GrandTotal)).Sum();

    return View(model);
}

The error is as follows: LINQ to Entities does not recognize the method 'System.Decimal Parse(System.String)' method, and this method cannot be translated into a store expression

Not a duplicate

The title is similar but the solution is different. In addition I was able to resolve the issue by use of the solution given. The question that you marked this as a duplicate of had no clear resolution

I was able to get this to work: `db.Table.Where(m => m.Year.Equals("2017")).Select(m => m.GrandTotal).ToList().ConvertAll(m => decimal.Parse(m)).Sum();` — Gavin, Oct 19 '16 at 20:06
@SamAxe The `.ToList()` executes the query, then we are dealing with LINQ-To-Objects. — Gavin, Oct 19 '16 at 20:08
Oh come on @SamAxe, you cant dog him for providing the exact thing you post as an answer — Mike_G, Oct 19 '16 at 20:10
@Mike_G: I'm certainly not. There's absolutely nothing wrong with Gavin's answer. I was simply pointing out that Gavin was using LINQ-To-Objects to perform the task, and not LINQ-To-Entities. It wasn't mentioned in his comment - so who knows if they knew what they were using, and also making it clear to the OP what technology was being used. The comment back to me is superfluous - it just reiterates what I had literally just commented. — Sam Axe, Oct 19 '16 at 20:14
It's not possible with LINQ to Entities. Why people make the things harder by storing numeric/date/time data as strings is a mistery. — Ivan Stoev, Oct 19 '16 at 20:19
One has to ask why you are storing decimal values as strings in your database. You should be using columns with type `money`. Is this something you can change? — ChrisF, Oct 19 '16 at 20:21

score 2 · Accepted Answer · answered Oct 19 '16 at 20:05

2

I'm unaware of a LINQ-To-Entities function that can do this. Id do it using LINQ-To-Objects:

var result = (from p in db.Table where "2017" == p.Year select p).ToList();
var model = (from p in result select decimal.Parse(p.GranTotal)).Sum();

answered Oct 19 '16 at 20:05

Sam Axe

33,313
9
55
89

This solution gives me a different error when I try to fire up the view `The model item passed into the dictionary is of type 'System.Decimal', but this dictionary requires a model item of type` – Skullomania Oct 19 '16 at 20:25
Well that's an entirely different problem and should be posted as a different question. But before posing the question you should read the words and let them sink in for a moment. You'll get it. – Sam Axe Oct 19 '16 at 20:30
I resolved it. Thanks man! – Skullomania Oct 20 '16 at 19:03

How do I properly convert a string to decimal in my linq query?

1 Answers1