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?

I am using SQLCe (compact edition) 4.0 embedded with entity framework. — Astro, Oct 02 '13 at 23:36
So, you have only one option here - move calculations to client side [SQL CE 4 EF provider cannot translate this into SQL query](http://stackoverflow.com/questions/5771299/linq-to-entities-stringconvertdouble-cannot-be-translated-to-convert-int-to-s) — Sergey Berezovskiy, Oct 02 '13 at 23:39

Sergey Berezovskiy · Answer 1 · 2013-10-02T23:40:54.203

19

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.

edited Oct 02 '13 at 23:40

answered Oct 02 '13 at 22:27

Sergey Berezovskiy

232,247
41
429
459

It is given me the following error: The specified method 'System.String StringConvert(System.Nullable`1[System.Double])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression. – Astro Oct 02 '13 at 22:58
@Rodri looks like you are not using Microsoft SQL Server Entity Framework provider, and your provider does not support these functions. If provider does not support converting numbers to strings on server side, then you can only move conversion to client (e.g. with `AsEnumerable()`) – Sergey Berezovskiy Oct 02 '13 at 23:16
The provider I am using is SQLCe (compact edition) 4.0, so in this case as you have said the only possible solution is using AsEnumerable(). – Astro Oct 02 '13 at 23:39
@Rodri unfortunately yes, but I recommend you to select only required data before moving to client: `Select(w => new { w.Name, w.Id }).AsEnumerable().Select(x => new { Alias = x.Name + x.Id, x.Name })` – Sergey Berezovskiy Oct 02 '13 at 23:42
SqlFunctions are very interesting. Never used it before. But how to get data converted in a culture sensitive way? I get the dot as decimal separator when I expect a comma. Thanks. – jacktric Nov 16 '17 at 08:39

score 19 · Accepted Answer · answered Oct 02 '13 at 23:02

19

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

answered Oct 02 '13 at 23:02

Esteban Elverdin

3,552
1
17
21

Yes, that works ... but take care that you narrow down the resultset **before** the `.AsEnumerable()`, as you did in the example by using `Where(...)`. Otherwise things might become incredibly slow, because you're doing that in memory rather than in the database. – Matt Mar 05 '15 at 15:49
Small optimization: you might want to put the `.AsEnumerable()` behind the `.OrderBy(...)` so the sorting will be done on the DB side as well. – Rob Vermeulen Feb 20 '20 at 08:56

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

2 Answers2

Linked

Related