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

Question

Getting error inside MVC3 web application. LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

when i try to fetch values using EF from query :

public class DataRepository
    {
        public mydataEntities1 dbContext = new mydataEntities1();

        public List<SelectListItem> GetPricingSecurityID()
        {
        var pricingSecurityID = (from m in dbContext.Reporting_DailyNAV_Pricing
                                     select new SelectListItem
                                         {
                                                Text = m.PricingSecurityID.ToString(),
                                                Value = m.PricingSecurityID.ToString()
                                         });

        return pricingSecurityID.ToList();
        }
    }

Answer 1

That can't be converted to SQL. I guess, in theory, it could, but isn't implemented.

You just need to perform your projection after you have your results:

var pricingSecurityID = (from m in dbContext.Reporting_DailyNAV_Pricing
                                     select m.PricingSecurityID).AsEnumerable()
    .Select(x => new SelectListItem{ Text = x.ToString(), Value = x.ToString() });

Answer 2

If it's already a string, why are you bothering to call ToString in the first place? I suspect a translation wasn't included in LINQ to Entities because it's pointless. Change your select clause to:

select new SelectListItem
{
    Text = m.PricingSecurityID,
    Value = m.PricingSecurityID
}

If you really need to do something which isn't supported by LINQ to Entities, use AsEnumerable to transition from a database query to in-process:

public List<SelectListItem> GetPricingSecurityID()
{
    return dbContext.Reporting_DailyNAV_Pricing
                    .Select(m => m.PricingSecurityID)
                    .AsEnumerable() // Rest of query is local
                    // Add calls to ToString() if you really need them...
                    .Select(id => new SelectListItem { Text = id, Value = id })
                    .ToList();
}

I agree with Jason's objections too, btw. You'd be better off returning a List<string> which is rendered elsewhere.

Also note that if you're just going to use a single select clause or just a where clause, query expressions really don't add much - calling the LINQ extension methods can end up with less clutter, particularly if you want to call methods which aren't supported in query expressions (such as ToList).

Answer 3

Because it's trying to convert it to SQL, and it can't. Drop off the call to ToString, and do a projection before you return to the caller. So, replace your select clause with

select m.PricingSecurityID

and then say

return pricingSecurityID
           .AsEnumerable()
           .Select(x => x.ToString())
           .Select(x => new SelectListItem { Text = x, Value = x })
           .ToList();

Also, I note that you're mixing UI concerns and data querying concerns. This is generally a bad practice. Really, you should just be returning the list of IDs and let the UI portion of your code worry about finagling it into the right form.

Answer 4

How about this. In this example, both the VDN field in the db and the Skill field are integers. I'm looking for matches from both fields so I have 2 compares.

Include this:

using System.Data.Objects.SqlClient; // needed to convert numbers to strings for linq

When comparing numbers do this:

        // Search Code
            if (!String.IsNullOrEmpty(searchString))
            {
                depts = depts.Where(d => SqlFunctions.StringConvert((double)d.VDN).Contains(searchString.ToUpper())
                || SqlFunctions.StringConvert((double)d.Skill).Contains(searchString.ToUpper()));
            }
        // End Search Code

Workie.

Answer 5

Sadly EF does not know how to convert .ToString() You must use embedded function SqlFunctions.StringConvert: http://msdn.microsoft.com/en-us/library/dd466292.aspx Also there is no overload for int so you must typecast to double :-(

var vendors = 
   from v in Vendors  
   select new
   {             
       Code = SqlFunctions.StringConvert((double)v.VendorId)
   }; 

Answer 6

I understand that this question is answered and I agree that using AsEnumerable() is the way to go. However I would like to highlight a common scenario that I usually come across where AsEnumerable() is used inefficiently to resolve this error.

From .NET Language-Integrated Query for Relational Data

The AsEnumerable() operator, unlike ToList() and ToArray(), does not cause execution of the query. It is still deferred. The AsEnumerable() operator merely changes the static typing of the query, turning a IQueryable into an IEnumerable, tricking the compiler into treating the rest of the query as locally executed.

References

1. Am I misunderstanding LINQ to SQL .AsEnumerable()?
2. Understanding .AsEnumerable() in LINQ to SQL

Inefficient way

IEnumerable<InvoiceDTO> inefficientEnumerable = 
     (from a in db.Invoices
     where a.Practice_Key == practiceKey.FirstOrDefault()
     select a
     ).AsEnumerable().
     Select(x => new InvoiceDTO
                             {
                                 InvoiceID = x.InvoiceID,
                                 PracticeShortName = x.Dim_Practice.Short_Name,
                                 InvoiceDate = x.InvoiceDate,
                                 InvoiceTotal = x.InvoiceAmount,
                                 IsApproved = x.IsApproved,
                                 InvoiceStatus = (
                                                  x.IsApproved == null ? "Pending" :
                                                  x.IsApproved == true ? "Approved" :
                                                  x.IsApproved == false ? "Rejected" : "Unknown"
                                                ),
                                 InvoicePeriodStartDateText = x.InvoicePeriodStart.ToShortDateString(),
                                 InvoicePeriodEndDateText = x.InvoicePeriodEnd.ToShortDateString(),
                                 InvoicePeriodStartDate = x.InvoicePeriodStart,
                                 InvoicePeriodEndDate = x.InvoicePeriodEnd
                             }
                             );

            invoices = inefficientEnumerable.ToList();

Here the AsEnumerable is used for the entire table. All the columns are getting selected eventhough they are not needed.

Better Way

 IQueryable<InvoiceDTO> invoicesQuery = 
   (from a in db.Invoices
   where a.Practice_Key == practiceKey.FirstOrDefault()
   select new InvoiceDTO
            {
             InvoiceID = a.InvoiceID,
             PracticeShortName = a.Dim_Practice.Short_Name,
             InvoiceDate = a.InvoiceDate,
             InvoiceTotal = a.InvoiceAmount,
             IsApproved = a.IsApproved,
             InvoiceStatus = (
                               a.IsApproved == null ? "Pending" :
                               a.IsApproved == true ? "Approved" :
                               a.IsApproved == false ? "Rejected" :"Unknown"
                               ),
             InvoicePeriodStartDate = a.InvoicePeriodStart,
             InvoicePeriodEndDate = a.InvoicePeriodEnd
          });


          IEnumerable<InvoiceDTO> betterEnumerable = invoicesQuery.AsEnumerable().
          Select(x => new InvoiceDTO
                                 {
                                     InvoiceID = x.InvoiceID,
                                     PracticeShortName = x.PracticeShortName,
                                     InvoiceDate = x.InvoiceDate,
                                     InvoiceTotal = x.InvoiceTotal,
                                     IsApproved = x.IsApproved,
                                     InvoiceStatus = x.InvoiceStatus,
                                     InvoicePeriodStartDateText = x.InvoicePeriodStartDate.ToShortDateString(),
                                     InvoicePeriodEndDateText = x.InvoicePeriodEndDate.ToShortDateString(),
                                     InvoicePeriodStartDate = x.InvoicePeriodStartDate,
                                     InvoicePeriodEndDate = x.InvoicePeriodEndDate
                                 }
                                 );

Answer 7

Try This using VB.NET, Important Point is you need to Get the results AsEnumerable as mentioned in Answers.

Dim _EventsDaysResult = From ED In TAdbContext.EventPolicies.AsEnumerable
                                    Where ED.EventID = EID
                                    Select New With {ED.EventID, 
                                    .DayInfo = 
                                    ED.EventDay.GetValueOrDefault.ToShortDateString & " ( " & ED.EventDayTitle & " ) "}

Answer 8

return dbContext.Reporting_DailyNAV_Pricing.AsEnumerable().Select(x => new SelectListItem
{
    Text = x.PricingSecurityID.ToString(),
    Value = x.PricingSecurityID.ToString()
}).ToList();