Right a simple problem this, with a fairly simple solution, but not entirely intuitive or obvious.
Supposing we have a data context called context which is a model of a SQL database which includes a table called Country.
Now we want to create a dropdown for those countries, based on a visibility flag and ordered alpahbetically, simple right?
var countries = from c in context.Countries
where c.Visible
orderby c.Name
select new SelectListItem
{
Value = c.Id.ToString(),
Text = c.Name
};
Well no. Doing that will garner you the exception:
LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression
This is because the entire Linq statement must be translated to a store expression to be executed against the underlying data store, ToString() is not a supported method for this. (On a personal note I have no idea why ToString() is not supported against a SQLServer data store, I hope they fix that up in a future release.)
The solution is not particularly elegant but it is incredibly simple.
var countries = from c in context.Countries
where c.Visible
orderby c.Name
select c;
var countriesList = from c in countries.AsEnumerable<Country>()
select new SelectListItem
{
Value = c.Id.ToString(),
Text = c.Name
};
In this particular example (which could be contracted a little, it just makes more sense formatted this way) we set up the Linq query to get the data we want in the order we want. Then we use that query in a Linq to objects statement beneath. The important point to note is the use of the AsEnumerable<>() method. This forces the original Linq query to actually execute and return the list of relevant countries. This then frees the second Linq query from the Linq to entities restrictions and turns it into a simple Linq to objects query.
Not particularly elegant, but it is simple and it works.
It is worth noting that any method that causes the first Linq to execute would have sufficed in the second query, AsEnumerable just makes the most sense in this particular example.
