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

Solution 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() });

Solution 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).

Solution 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.