Linq To Entity string to int conversion in order by

Question

There is a table in our database the stores year and month, each being a char. The existing Linq to Entity query looks like this:

from mc in repository.table.AsQueryable()
orderby mc.Year descending, mc.Month descending
select mc).FirstOrDefault()

The issue with this is that it is ordering by string and not int, leading to the incorrect row being returned from the query. I have tried to convert the year and month to int and datetime, both throwing an error saying:

"LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression."

(from mc in repository.table.AsQueryable()
 orderby mc.Year descending, Convert.ToDateTime(mc.Month) descending
 select mc).FirstOrDefault()

OR

"LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression."

(from mc in repository.table.AsQueryable()
 orderby mc.Year descending, Convert.ToInt32(mc.Month) descending
 select mc).FirstOrDefault();

As you can see, I cannot cast the month and year into INTs first because it is using the data from the table, not external data.

PS: I do not have the authority to change the tables to make the two columns INTs.

Anyone know how to approach this?

Answer 1

I would recommend that you take a look at the SqlFunctions class, most notably about the DatePart method, which can be used with Entity Framework (with no exceptions), and will convert strings into the dates for you to be properly ordered by.

Answer 2

As @IronMan84 noticed solution below works only if you have .edmx file.

1. Add the following xml to your .edmx file. (Just do a ‘Find in Files’ in Visual Studio </Schema> and place it before this node):

   <Function Name="IntParse" ReturnType="Edm.Int32"> <Parameter Name="stringvalue" Type="Edm.String" /> <DefiningExpression> cast(stringvalue as Edm.Int32) </DefiningExpression> </Function>

2. Add the following static function to your auto-generated Entity Framework class file:

   public partial class YourObjectContext { [EdmFunction("YourModel", "IntParse")] public static double IntParse(string val) { return int.Parse(val); } }

3. You are ready to go, just add the new function to your LINQ Query:

   from mc in repository.table.AsQueryable() orderby YourObjectContext.IntParse(mc.Year) descending, YourObjectContext.IntParse(mc.Month) descending select mc).FirstOrDefault()

Answer 3

Did you tried:

(from mc in repository.table.AsQueryable() orderby ((int)mc.Year) descending, ((int)mc.Month) descending select mc).FirstOrDefault();