c# - 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.