What's the best way to create custom sorting (for data like "07/2010") in EF 4.0?

Question

I have some table that contains annual sequence data in column like "1/2010", "2/2010" or "13/2010". I want to order data from this table by this column. So I should create order value for sorting like that the following SQL expression.

-- This code should work if amount of data is less than 1000 record per year.
CAST(SUBSTRING(ReceiveID, 0, CHARINDEX('/', ReceiveID)) AS INT) + 1000 * CAST(SUBSTRING(ReceiveID, CHARINDEX('/', ReceiveID) + 1, 4) AS INT)  AS OrderReceiveID

However, I want to write this code with EF 4.0 but I got some a few problems.

First, I try to create order expression in LINQ query. But I cannot call string to integer convert function like “int.Parse” because it cannot convert this to SQL statement.

Secondly, I create scalar-value function in database for converting string of annual sequence data to integer like “1/2010” to 20100001. But I cannot import it to edmx file because it supports only stored procedure.

Finally, I create custom view for this table, including custom order column (for data like 2010001) and I use additional column to order this table. Everything works well. But I don't like this because I don't need to create one view per one table.

Do you any idea for solving this problem without touch the database or create shared function for converting sequence data to integer value like my second idea?

Thanks,

Answer 1

You can try to use the ExecuteStoreQuery method.
In this case you will be able to use the mentioned method for sorting (and execute any other native SQL).
The code will look like:

  var q = context.ExecuteStoreQuery("SELECT ReceiveID, SomeField, SomeOtherField, ...  from Model1.MyEntity order by CAST(SUBSTRING(ReceiveID, 0, CHARINDEX('/', ReceiveID)) AS INT) + 1000 * CAST(SUBSTRING(ReceiveID, CHARINDEX('/', ReceiveID) + 1, 4) AS INT)");