Databases
StackOverflow2013
Postsdbo
POEntityFramework 4.0: Method cannot be translated into a store expression
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEntityFramework 4.0: Method cannot be translated into a store expression
    text
    copied!<p>I have this piece of code where Customers is <code>IQueryable&lt;Customer&gt;</code></p> <pre><code>Customers = Customers.Where ( C =&gt; Int32.Parse(C.Salary) &gt; 5000); </code></pre> <p>When I run that I get error <code>Method cannot be translated into a store expression</code>. 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.</p> <p><strong>Note:</strong> 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.</p> <p><strong>UPDATE:</strong> I am using Sql Server 2008. Just though of including it if it can be of any help.</p> <p><strong>UPDATE2:</strong> Aducci has provided a real tricky and clever solution in his answer. However the Sql generated from it is very ugly. As an example:</p> <pre><code>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) &gt; 5000 ) AS [GroupBy1] </code></pre> <p>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.</p> <p>Here is the equivalent Sql produced by the link which I have mentioned:</p> <pre><code>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) &gt; cast(5000 as float(53)) ) AS [GroupBy1] </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload