String.Format or toString not allowed in linq

Question

The code below is not liked and throw the following error.

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

I was able to do it previously before I change the cost of my shipping method from decimal? to decimal. I changed it to decimal, and it started throwing an error, and then I changed it back and now it still doesn't like this. What is being done incorrectly here. I am simply trying to make the text a concatenation of the name of a shipping method with a - and the cost as the text.

  var ShippingMethods = db.ShippingMethods.Select(x => new SelectListItem()
                    {
                        Text = x.Name + " - " + String.Format("c", x.Cost),
                        Value = x.Cost.ToString()
                    }).ToList();

score 3 · Accepted Answer · edited May 23 '17 at 12:32

3

You have to use .AsEnumerable() and the reason you can find from here

 db.ShippingMethods.AsEnumerable().Select(x => new SelectListItem()
                    {
                        Text = x.Name + " - " + String.Format("c", x.Cost),
                        Value = x.Cost.ToString()
                    }).ToList();

edited May 23 '17 at 12:32

Community

1
1

answered Sep 10 '16 at 15:22

Sajeetharan

216,225
63
350
396

1

can you add a reason for that too ? I mean why we need `.AsEnumerable()` ? – Sampath Sep 10 '16 at 15:33
Yeah what is the reason behind this. It works, I just don't understand what's wrong with the type if you're going to be formatting properties. – ddeamaral Sep 10 '16 at 15:34
2

`String.Format` cannot be converted to a valid SQL expression while EF trying to run the query to get data. See [What is the effect of AsEnumerable() on a LINQ Entity?](http://stackoverflow.com/questions/5311034/what-is-the-effect-of-asenumerable-on-a-linq-entity). When you start to enumerate the results, It gets the (raw data) and the string manipulations can be done in memory. – Shyju Sep 10 '16 at 15:40
`Raw data` means will it retrieve `all the columns` of the `ShippingMethods` table ? If `Yes` then It's **HORRIBLE** no (i.e. performance wise ) ? @Shyju – Sampath Sep 10 '16 at 15:47
You can run the SQL profiler to see what happens when you execute that code. You can do a `Select` call first and the `AsEnumerable` after that if you want only few columns. – Shyju Sep 10 '16 at 15:48
@Shyju Thanks for the comment – Sajeetharan Sep 10 '16 at 15:50
@Shyju is this fine then ? I mean performance wise ? http://stackoverflow.com/a/39427904/1077309 – Sampath Sep 10 '16 at 16:02
I posted a comment in your answer – Shyju Sep 10 '16 at 16:08
@Shyju you don't even need profiler, just do `db.Database.Log = Console.Write;` and it [will write out the SQL to the console when you perform the queries](https://msdn.microsoft.com/en-us/data/dn469464.aspx). – Scott Chamberlain Sep 10 '16 at 16:14

Sampath · Answer 2 · 2016-09-10T19:23:09.163

If you consider the performance then you can use it as shown below.B'cos if we retrieve all the row data related to the ShippingMethods table from db and then do the projection on memory (after .AsEnumerable()) means it degrades the performance heavily.Below query is suitable for the devs who are thinking about the performance of EF query.

AsEnumerable()

Load EVERY record into application memory, and then do the filtering and etc. (e.g. Where/Take/Skip).It will select * from MyTable, into thememory, then select thefirst X elements`.

db.ShippingMethods.Select(x => new 
                    {
                        Name = x.Name,
                        Cost = x.Cost,
                    }).AsEnumerable().Select(y => new SelectListItem()
                    {
                        Text = y.Name + " - " + String.Format("c", y.Cost),
                        Value = y.Cost.ToString()
                    }).ToList();

This code will not even compile ! In your second Select `y` represents a `SelectListItem` which does not have a `Cost` or `Name` property. Change the property names or even better ,do an anonymous projection in first Select `.Select(x => new { Name = x.Name, Cost = x.Cost}).AsEnumerable()` — Shyju, Sep 10 '16 at 16:07
Aha...Thanks a lot :D Done that.Hope now it's fine no ? @Shyju — Sampath, Sep 10 '16 at 16:10

score 0 · Answer 3 · answered Sep 10 '16 at 15:48

Calling AsEnumerable (or ToList or ToArray) pulls the contents of the dbset into memory where any sort of operation can be performed on them. The message about translating into a store expression means that the linq provider cannot translate String.Format into SQL and run it at the database level.

In this case calling AsEnumerable on the dbset is likely fine, but in a situation where you didn't want to pull an entire table into memory you would filter it, then to list it, then transform it with a select.

String.Format or toString not allowed in linq

3 Answers3