That happens. EF doesn't support all possible LINQ expressions when querying a DB. It is up to the provider (MySQL in your case) to translate those expression and it may not support everything. LastOrDefault
used to be one of the expressions it didn't like but String.Equals
is another (although it is fine with String.Compare
and "somestring".Equals
. The only way to truly know is to try the expression. If you run across an expression that the MySQL provider doesn't support then you need to submit a request to MySQL to have them consider supporting it. You can start here. EF itself isn't responsible for translating those expressions so the EF team is probably not going to be able to do much.
In the interim you need to modify your query to have it run the query on the DB and then run the unsupported expressions on the results. As the message indicates, the easiest way to do that is to call AsEnumerable
or one of the To...
methods. These methods trigger the DB query to be run if it hasn't yet and return back the query results to .NET which can then use LINQ on the memory objects. As an example suppose that String.Equals
is the expression that isn't supported.
//Run all this on the DB
query = query.Where(x => x.IsActive && !String.Equals(x.Name, "Test"));
You have to break up the query into the pieces that run on the DB and those that don't.
query = query.Where(x => x.IsActive)
//Force DB execution and then filter the rest on the .NET side
var results = query.AsEnumerable()
.Where(x => !String.Equals(x.Name, "Test"));
Note that by breaking up the query you may end up pulling back way more data than expected so if that is an issue you'll have to rewrite your query. For example an alternative approach would be to use equality.
query = query.Where(x => x.IsActive && x.Name != "Test");