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

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();
}