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?
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()
...