EntityFramework 4.0: Method cannot be translated into a store expression

Question

I have this piece of code where Customers is IQueryable<Customer>

Customers = Customers.Where ( C => Int32.Parse(C.Salary) > 5000);

When I run that I get error Method cannot be translated into a store expression. Now I know I need to use EntityFunctions (i.e Canonical functions for EF) for this but when I checked the list in EntityFunctions, there is no method available for converting string to integer.

Note: I know if I do Linq to Objects i.e do a ToList() on IQueryable and then filter, it would all work. But I do not want to do that since that would degrade my performance because Customers table's rows count is too large. Also it is not possible to convert my field of database into int.

UPDATE: I am using Sql Server 2008. Just though of including it if it can be of any help.

UPDATE2: Aducci has provided a real tricky and clever solution in his answer. However the Sql generated from it is very ugly. As an example:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM ( SELECT 
        [Project2].[C1] AS [C1], 
        (SELECT TOP (1) [top].[C1] AS [C1]
            FROM ( SELECT TOP (1) 
                 CAST( [Project2].[PostalCode] AS int) AS [C1]
                FROM [dbo].[Customers] AS [c]
            )  AS [top]) AS [C2]
        FROM ( SELECT 
            [Extent1].[PostalCode] AS [PostalCode], 
            (SELECT TOP (1) [top].[C1] AS [C1]
                FROM ( SELECT TOP (1) 
                     CAST( [Extent1].[PostalCode] AS int) AS [C1]
                    FROM [dbo].[Customers] AS [c]
                )  AS [top]) AS [C1]
            FROM [dbo].[Customers] AS [Extent1]
        )  AS [Project2]
    )  AS [Project4]
    WHERE (CASE WHEN ([Project4].[C1] IS NULL) THEN 0 ELSE [Project4].[C2] END) > 5000
)  AS [GroupBy1]

The only difference is it uses postal code instead of salary. If you are not really worried much about this ugly Sql you can surely use but if you are then I have provided 1 link in my comment which works in very nice and performant manner.

Here is the equivalent Sql produced by the link which I have mentioned:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Customers] AS [Extent1]
    WHERE  CAST(  CAST( [Extent1].[PostalCode] AS int) AS float) > cast(5000 as float(53))
)  AS [GroupBy1]

I found the link to my answer after hours of searching. Don't know why it didn't pop up in google in first few pages. http://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-meth .Bit painful but works sweetly. — TCM, Sep 14 '11 at 17:11
I like your solution and it produces more efficient sql. My solution would only be viable in situations where you can't modify your edmx file or in a code-first approach where you dont have one. — Aducci, Sep 14 '11 at 17:50

Aducci · Accepted Answer · 2011-09-14T17:10:42.760

3

This will work if all string values are valid integers

var query = from c in Customers
            let salaryInt = Customers.Take(1).Select(x => c.Salary).Cast<int>().FirstOrDefault()
            where salaryInt > 5000
            select c;

edited Sep 14 '11 at 17:10

answered Sep 14 '11 at 16:19

Aducci

26,101
8
63
67

I don't think this will work. The new which I suppose I will get is : "Can't translate context.Customers into store expression" – TCM Sep 14 '11 at 16:31
@Anthony - I removed the context in front of Customers. Give that a try – Aducci Sep 14 '11 at 17:11
@Anthony - Not exactly sure what you are trying to do, but if you update your answer with a sample usage it would be more clear – Aducci Sep 14 '11 at 17:24
sorry, I made a mistake. It works :). However read my updated question after few seconds. – TCM Sep 14 '11 at 17:38

EntityFramework 4.0: Method cannot be translated into a store expression

1 Answers1