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

Question

Hello,
I try to convert my existing Linq to SQL project to an Linq to Entities project. I created a new EF data model that contains all tables and fields as the original Linq to SQL context (that I deleted before).
I try to run following statement which could be executed without any problems in LinqToSQL:

    var _PSPItems = (from x in Context.view_Overview
                             orderby x.PSP_ID
                             select new
                                        {
                                            x.NR,
                                            x.ID,
                                            x.Name,
                                            x.Description,
                                            Amount=
                                 (from i in Context.BDTA
                                  where i.PSP_ID == x.PSP_ID
                                  select i.Amt).Sum()
                                        });
 
 
 
                _PSPItems = (from x in _PSPItems
                             where
                                 x.NR.ToString().Substring(3, 1) == "5" || x.NR.ToString().Substring(3, 1) == "6"
                             select x);
        




But I receive followign exception: LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

Can anybody tell me how to convert that statement?

Thank you

Answer 1

 
                _PSPItems = (from x in _PSPItems
                             where
                                 x.NR.ToString().Substring(3, 1) == "5" || x.NR.ToString().Substring(3, 1) == "6"
                             select x);
        




But I receive followign exception: LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

If "NR" is already a string, why do you need to call ToString() on it? Just removing the ToString part should work in that case...

x.NR.Substring(3, 1)

If it is not a string, then what data type is it, and does it use some specific format?

---

Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)

Answer 2

Hi Kristopfer,

NR is data type int. The query compiles without any problems. I am wondering why that exception is thrown, because it works without any troubles in Linq to SQL.
Do you have any idea why that is happening or how I can solve it?

Answer 3

NR is data type int. The query compiles without any problems. I am wondering why that exception is thrown, because it works without any troubles in Linq to SQL.
Do you have any idea why that is happening or how I can solve it?

The reason you get an exception is that the expression -> sql query translation in Linq-to-SQL currently supports more features than the Linq-to-Entities one. It compiles since the expression tree behind the query is valid in itself, but throws a runtime error when it tries to translate that into SQL at runtime.

If you're converting an app that is currently using L2S into L2E/EF v1 you will probably encounter a number of query constructs that are either not supported at all, or where the query generated by EF is vastly different from the one generated by Linq-to-SQL.

In this particular case, one alternative way of expressing x.NR.ToString().Substring(3, 1) == "5" || x.NR.ToString().Substring(3, 1) == "6" would be something like x.NR-((x.NR/10)*10)>=5 && x.NR-((X.nr/10)*10)<=6 . Not knowing the format of the data in your 'NR' field I just made the assumption that you're checking the last digit of a three digit number but the idea was to just change it to work with the int value rather than with a string...

Now, a separate issue: if the underlying table is large-ish, a where clause predicate like this one will result in a table scan both under L2S and L2E. If possible you may want to consider db-side changes instead of computations on the lookup fields to avoid that...