Why would Entity Framework not be able to use ToString() in a LINQ statement?

Question

This works in LINQ-to-SQL:

var customersTest = from c in db.Customers
                select new
                {
                    Id = c.Id,
                    Addresses = from a in db.Addresses where c.Id.ToString() == 
                        a.ReferenzId select a
                };

foreach (var item in customersTest)
{
    Console.WriteLine(item.Id);
}

But a similar example in Entity Framework gets an error message that says basically that it can't "translate it to SQL", here is the original error message in German:

"'LINQ to Entities' erkennt die Methode 'System.String ToString()' nicht, und diese Methode kann nicht in einen Speicherausdruck übersetzt werden."

Translation:

"'LINQ to Entities' does not recognize Method 'System.String ToString()', this method can not be translated into a memory expression.

Can anyone shed any light on how we could get this kind of statement to work in Entity Framework or explain why it gets this error?

Answer 1

Updated answer:

If you followed the link I gave you at the beginning of my answer, this missing feature has meanwhile received 75 votes and is now (finally!) implemented by Microsoft in EF 6.1. To all who participated: Thank you for voting! Your voice was heard.

For example:

var query = from e in context.Employees where e.EmployeeID.ToString() == "1" select e;

will now be translated to:

DECLARE @p0 NVarChar(1000) = '1'
SELECT [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], [t0].[Title], 
    [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address],[t0].[City],
    [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[HomePhone], [t0].[Extension], 
    [t0].[Photo], [t0].[Notes], [t0].[ReportsTo], [t0].[PhotoPath]
FROM [Employees] AS [t0]
WHERE (CONVERT(NVarChar,[t0].[EmployeeID])) = @p0

i.e. e.EmployeeID.ToString() translates to (CONVERT(NVarChar,[t0].[EmployeeID])).

---

Original answer:

It makes no sense to me why Linq2EF does not translate .ToString() into a proper SQL statement, as Linq2SQL does, only the Microsoft dev team knows the reason why they did not implement it yet. :-(

But you can raise the priority to implement it if you vote for this feature by following this link.

Luckily there are also 2 workarounds available, both of them I used recently in EF queries:

---

I) What helped me to get around this limitation was to change the query into a list, like so:

var customersList = (from c in db.Customers
             select c).ToList(); // converts to IEnumerable<T> ...
var customersTest = (from c in customersList 
             select new {Id=c.ID.ToString()}); // ... which allows to use .ToString()

The statement .ToList() converts to IEnumerable<T>, where .ToString() is available. Note that, Depending on the requirements, you can use .AsEnumerable() as well, which has the advantage that deferred execution is supported which is better if you have multiple linq queries depending on each other or if you're using different parameter values (many thanks to Divega for this hint!).

Afterwards you can use this query as you wish , e.g.:

var customersTest2 = from c in customersTest                 
    select new
            {
                Id = c.Id,
                Addresses = from a in db.Addresses where c.Id == a.ReferenzId select a
            };

Of course if you need you can add more properties to the objects of customersTest as required. You can also optimize the query above, I have only used 3 steps for readability of this example.

---

II) For simple conversions, and if you have to reuse the generated query in further subqueries (and it needs to remain IQueryable), use SqlFunctions from System.Data.Objects.SqlClient, they will be translated into SQL queries correctly.

Example 1: Date conversion (you have to use dateparts as below shown)

var customersTest = from c in db.Customers
    select new {
        strDate=SqlFunctions.DateName("dd", c.EndDate)
            +"."+SqlFunctions.DateName("mm", c.EndDate)
            +"."+SqlFunctions.DateName("yyyy", c.EndDate) 
    }

Example 2: Numeric to string conversion

var customersTest = from c in db.Customers
    select new {
        strID=SqlFunctions.StringConvert((double)c.ID)
    }

---

This should help you out of most situations where conversions into strings are required.

---

Answer 2

Simply put: LINQ to Entities doesn't know about the conversion from your ID type to a string.

What is the type of c.ID? Is there any reason why it's one type for ID, but another for ReferenzId? If at all possible, make them the same type, at which point you won't have a problem any more. I don't know if there are other ways of performing conversions in LINQ to Entities - there may be - but aligning the types would be cleaner.

By the way, this really looks like it's a join:

var query = from c in db.Customers
            join a in db.Addresses on c.Id equals a.ReferenzId into addresses
            select new { Id = c.Id, Addresses = addresses };

EDIT: To respond to your comment - ToString appears in IntelliSense because the compiler has no real idea what your query is going to mean or how it will be translated. It's perfectly valid C#, and can generate a valid expression tree - it's just that EF doesn't know how to convert that expression tree into SQL.

You could try using Convert.ToString(c.Id) instead of just calling c.Id.ToString()...

Answer 3

Entity Framework 6.1 RTM which was just released now support .ToString()

Answer 4

LINQ to Entities as far as I understand it (for v1) is very primative. In otherwords it doesn't know how to take the extension method "ToString()" and generate the SQL for it.

In LINQ to SQL, it executes the extension method "ToString()" before generating the SQL. The difference is that LINQ to Entities uses IQueryable instead of IEnumerable.

BUT, from what I remember casting should work (because casting is a data type and SQL knows about CAST()).

So

c.Id.ToString() should really be (string)c.Id

(also, make sure it is (string) and not (String)).

One of the downfalls I would say about using Lambda (in Entity Framework) to generate the SQL expression instead of pure LINQ.

Keep in mind too, that using CAST on the left side of the equals sign in SQL is a bit ill performing :-)