SqlServer 2005 Ok, MSLocalDb 2016 Error : LINQ to Entities only supports casting EDM primitive or enumeration types

Question

I have two server : production server SQL Server 2005 and my test server (LocalDb)\MSSQLLocalDB 2016

I use .Net Framework 4.5.2 and EntityFramework 6, in a Linq request I do :

query.Select(l => l.s.Field1 + l.s.Field2).ToList();

Field1 : string
Field2 : Nullable< int >

On the production server it works, the sql generated is :

SELECT CASE WHEN ([Extent1].[Field1] IS NULL) THEN N''
            ELSE [Extent1].[Field1] END +
       CASE WHEN ([Extent1].[Field2] IS NULL) THEN N''
            ELSE  CAST( [Extent1].[Field2] AS nvarchar(max))
       END AS [C1]

but on the local server I have this error :

Unable to cast the type 'System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

I know how to solve my problem.

I put my fields in a anonymous object, ToList() and I concatenate the fields after the ToList()

another solution is :

query.Select(l => l.s.Field1 + SqlFunctions.StringConvert((decimal?)l.s.Field2).Trim())

But I would like to understand why I have this difference :

I have put the compatibiliy on my local server to 2008 same thing.

If I do :

Field2.Value error :

Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.
Field2.Value.ToString() error :

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.**

This is a limitation of (LocalDb)\MSSQLLocalDB ?
After 2005 this kind of thing is not allowed ?
There is an option to activate/desactivate the implicit conversion ?

Any clues are welcome.

code passionate code passionate · Accepted Answer · 2017-06-15T13:13:21

0

votes

This may help :

query.Select(l => l.s.Field1 + (l.s.Field2 ?? "").ToString()).ToList();

SqlServer 2005 Ok, MSLocalDb 2016 Error : LINQ to Entities only supports casting EDM primitive or enumeration types

2 Answers