EF - linq2entities does not recognize .tostring() method?

Question

I'm unable to compile the below code and I'm getting the following error:

LINQ to Entities does not recognize the method 'System.String ToString()

I'm a bit surprised because I was able to do this in Linq2SQL but can't do it in Entity Framework.

Could I please get any help rewriting the below code ? I've seen a few examples related to this error but I couldn't find something that is specific to this scenario. Thanks

 using (ctx)
 {
                   var productResults = (from q in ctx.Products
                                         where q.ProductId == productId && q.Model == productModel
                                         select new Models.ProductDTO
                                         {
                                             Id = q.ProductId,
                                             Name = q.Name.ToString(),
                                             Year = q.Year.ToString("MMM ddd d HH:mm yyyy"),
                                             Model = q.Model,
                                             Description = q.Description.ToString()
                                         }).Distinct().ToList().AsParallel();
                   Department.Products = productResults;
                }

If Description is of type string already, why would you need to call ToString() on it? — Wiktor Zychla, Feb 12 '13 at 13:49
@WiktorZychla, May be he does not need for Name and Description, But He needs for date to string... `(q.Year.ToString("MMM ddd d HH:mm yyyy"))` — AliRıza Adıyahşi, Feb 12 '13 at 13:54
Possible duplicate of [Why would Entity Framework not be able to use ToString() in a LINQ statement?](http://stackoverflow.com/questions/1920775/why-would-entity-framework-not-be-able-to-use-tostring-in-a-linq-statement) — Matt, Mar 06 '15 at 09:13

AliRıza Adıyahşi · Accepted Answer · 2013-02-12T14:42:38.397

First get list from context

var productResults = ctx.Products.where(q => q.ProductId == productId && q.Model == productModel).ToList();

Then query is and select new Type as ProductDTO

var productDTO = (from q in productResults 
                 select new Models.ProductDTO
                 {
                      Id = q.ProductId,
                      Name = q.Name.ToString(),
                      Year = q.Year.ToString("MMM ddd d HH:mm yyyy"),
                      Model = q.Model,
                      Description = q.Description.ToString()
                 }).Distinct().ToList().AsParallel();

AFTER COMMENT

IEnumerable:

IEnumerable<Products> list = context.Products.Take(10);
// after this line data load the memory that fetched from DB.

SQL Output:

 Select * FROM Table

IQuerable:

IQuerable<Products> list = context.Products.Take(10);
// data still not fetch from DB

SQL Output:

 Select Top 10 FROM Table

It works..Thanks a lot..!!.If possible, please see my other question in the answer below, and let me know what you think. Thanks — Ren, Feb 12 '13 at 14:18

daryal · Answer 2 · 2013-02-12T13:59:49.327

1

you may also do this in one query;

            var productResults = ctx.Products.Where(q => q.ProductId == productId && q.Model == productModel).ToList()
                .Select<Product, ProductDTO>(m =>
                {
                    Models.ProductDTO dto= new Models.ProductDTO();
                    dto.Id = m.ProductId;
                    dto.Name = m.Name.ToString();
                    dto.Year = m.Year.ToString("MMM ddd d HH:mm yyyy");
                    dto.Model = m.Model;
                    dto.Description = m.Description.ToString();
                    return dto;
                }).Distinct().ToList().AsParallel();

there may be a better way, but breaking it into two queries may work.

var productResults = (from q in ctx.Products
     where q.ProductId == productId && q.Model == productModel
     select q).ToList();

var temp = from o in productResults
         select new Models.ProductDTO
         {
             Id = q.ProductId,
             Name = q.Name.ToString(),
             Year = q.Year.ToString("MMM ddd d HH:mm yyyy"),
             Model = q.Model,
             Description = q.Description.ToString()
         }).Distinct().ToList();

edited Feb 12 '13 at 13:59

answered Feb 12 '13 at 13:51

daryal

14,643
4
38
54

the trick here is calling to list after first query; then it works exactly in the same way any Linq query works since the data is fetched to memory. – daryal Feb 12 '13 at 14:04
Better is to use `.AsEnumerable()` instead of your first `.ToList()`: there is no point in saving all `Product`s in a list if you're going to discard that list. The only use `.ToList()` has is to force `.Select()` to resolve to `Enumerable.Select` instead of `Queryable.Select`, and `.AsEnumerable()` achieves that same result. – Feb 12 '13 at 14:07
It works. Thanks a lot.!!.I'm only a beginner, and got one more question. I tried both your way and AliRiza's(answer above) way of creating the list. Both work perfectly but which one is better ? is there any difference ? Which is the good practice ? - Thanks – Ren Feb 12 '13 at 14:17
@user1980311, check this : http://stackoverflow.com/questions/2876616/returning-ienumerablet-vs-iqueryablet – AliRıza Adıyahşi Feb 12 '13 at 14:20
@AliRızaAdıyahşi this link does not seem to directly related; in both answers using ToList call, both is converted to IEnumerable. I see no direct performance difference between both answers (may be Jon Skeet may have an answer). – daryal Feb 12 '13 at 14:29
So, If I have a view Model such as 'public IEnumerable Products { get; set; }', Is it better to change it to public IQueryable ?, will it still work if I change .ToList to .AsQueryable() ? – Ren Feb 12 '13 at 14:33
@user1980311 calling ToList results in the query to run in the database; you may refer to the question provided in one of the comments (or you may google for it as "deferred execution" etc. You may change ToList to AsEnumerable which will result a slight performance increase. – daryal Feb 12 '13 at 14:37
1

A more layman explanation on why you had the problem is that EF tries to translate the expression in the select clause to SQL. It doesn't know how to convert the `ToString()` method into SQL. By using `ToList()` or `AsEnumerable()`, the latter being the better option, you stop using EF and start using actual functions (executable code), at which point the `ToString()` method will no longer be interpreted, but instead will be invoked. If you want to minimize the data being pulled over the network (and into memory), select into an anonymous type, then use that type to fill your DTO. – Brian Ball Feb 12 '13 at 14:39
Thanks a lot everyone for your help..!!..I will start looking into deferred execution now.. – Ren Feb 12 '13 at 14:57

EF - linq2entities does not recognize .tostring() method?

2 Answers2