Why LINQ to Entities does not recognize the method 'System.String ToString()?
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.
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
).
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() });
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.