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.