LINQ to Entities does not recognize the method 'Double Parse(System.String)' cannot be translated into a store expression C# asp.net

Question

I try to order Departments by number, but since the departement number stored as a string in database, I can't just use Order by number. I try to parse it as float but it did not work. Then I tried to parse it as Double. That does not work either. Any suggestions? The following is my code.

using (var db = new Context())
{
    var datasource = (from x in db.Departments 
                      orderby double.Parse(x.DepartmentNumber) 
                      select x).ToList();
    lvData.DataSource = datasource;
    lvData.DataBind();
}

Thanks for the answer, I did the following, but the results are not entirly correct.

 var datasource = (from x in db.Departments orderby x.DepartmentNumber select x).ToList().OrderBy(Department => float.Parse(Department.DepartmentNumber));

I'm bit supreised that `DepartmentNmber` is not type of duoble. — Maciej Los, Jun 13 '17 at 20:30
We can't guess what your definition of "correct" is. Do you mean you want `209.01` to appear before `222`? The sorting shown in the screenshot would seem to suggest your locale uses a comma as decimal point, then parse for that. — CodeCaster, Jun 13 '17 at 21:08
Thanks for the comment, I just figured it out and it works now. Please read my answer further down. — Mindan, Jun 13 '17 at 21:10
Yeah see the linked duplicates. It helps to break up a problem in smaller parts, then you'll find each of them has been asked and answered before. :) — CodeCaster, Jun 13 '17 at 21:11

Christos · Answer 1 · 2019-07-03T17:05:46.290

7

One solution it would be the following:

var datasource = db.Departments 
                   .ToList()
                   .OrderBy(department => double.Parse(department.DepartmentNumber));

You fetch all the data from the database in the memory of your application (by calling ToList, which requests the immediate execution of the query.) and then you make the ordering in memory.

The reason why the query below doesn't work:

var datasource = (from x in db.Departments 
                  orderby double.Parse(x.DepartmentNumber) 
                  select x).ToList();

is the fact that double.Parse cannot be translated to an equivalent function in sql and then the whole sql query be submitted to the database for execution.

edited Jul 03 '19 at 17:05

answered Jun 13 '17 at 20:20

Christos

53,228
8
76
108

In a Short: the magic is in `.ToList()` method before `OrderBy()`. – Maciej Los Jun 13 '17 at 20:25
Thanks for the answer please read my edit in the original question – Mindan Jun 13 '17 at 20:59
@Mindan You are welcome. I saw your edit and as it seems you didn't follow the approach I proposed above. Could you please make a try and let me known ? Thanks – Christos Jun 13 '17 at 21:03
@Mindan You mean the suggested approach. Correct? – Christos Jun 13 '17 at 21:07

score 1 · Accepted Answer · answered Jun 13 '17 at 21:09

Actually the following solved the problem and provided the required results:

 var datasource = (from x in db.Departments orderby x.DepartmentNumber select x).ToList().OrderBy(Department => Convert.ToDouble(Department.DepartmentNumber, System.Globalization.CultureInfo.InvariantCulture));

LINQ to Entities does not recognize the method 'Double Parse(System.String)' cannot be translated into a store expression C# asp.net

2 Answers2

Linked

Related