Linq to Entities does not recognize string.Format or concatenation '+'

Question

I have below code:

using (DBContext context = new DBContext())
{
    myCollection = context.Items.Where(i => i.Type == 1).OrderBy(k => k.Name).Select(w => new
    {
        Alias = w.Name + string.Format("{0}", w.Id),
        Name = w.Name                        
    }).ToArray();
}

In runtime i get an error when trying to concatenate the strings and trying con convert integer w.Id to string.

Error says:

Linq to entities does not recognize method string.Format

also plus concatenation sign '+' is not supported.

I have solved this by introducing AsEnumerable:

using (DBContext context = new DBContext())
{
    myCollection = context.Items.AsEnumerable().Where(i => i.Type == 1).OrderBy(k => k.Name).Select(w => new
    {
        Alias = w.Name + string.Format("{0}", w.Id),
        Name = w.Name                        
    }).ToArray();
}

but I would like to know if this is the best solution or there is another way more suitable for doing this. Ideas?

Answer 1

EF can't convert String.Format into SQL, but it handles string concatenations without problem. Use SqlFunctions.StringConvert instead of String.Format to convert number into string on server side:

Select(w => new {
    Alias = w.Name + SqlFunctions.StringConvert((double)w.Id),
    Name = w.Name                        
})

It generates something like

SELECT 
[Extent1].[Name] + STR( CAST( [Extent1].[Id] AS float)) AS [C1], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Items] AS [Extent1]

UPDATE: Thus you are using EF provider which does not support this conversion (SQL CE provider cannot cannot translate this query into SQL) you have only one option left - move calculations to client side, as you already have done.

Answer 2

One optimization of your code is to use AsEnumerable() after the Where method. If not, every entity is returned from storage, and the entire table is examined using LINQ to Objects. With this simple modification of your code you let the where clause run on sql and retrieve less records from storage. The general rule is to place any query clauses that are implemented by the LINQ provider first.

using (DBContext context = new DBContext())
{
    myCollection = context.Items.Where(i => i.Type == 1)
       .AsEnumerable().OrderBy(k => k.Name).Select(w => new
        {
            Alias = w.Name + string.Format("{0}", w.Id),
            Name = w.Name                        
        }).ToArray();
}