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