Unknown column 'Project2.Name' in 'where clause'

Question

I have a simple statement to get data from my mySQL database but it get the following error:

[MySqlException (0x80004005): Unknown column 'Project2.Name' in 'where clause'] MySql.Data.MySqlClient.MySqlStream.ReadPacket() +272
MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) +68
MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) +17
MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +110 MySql.Data.MySqlClient.MySqlDataReader.NextResult() +761 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1557
MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +33
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +12 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +435

The Statement:

using (myEntities ctx = new myEntities())
{
    var Result = ctx.Items.Where(x => x.Contact.Country == Country)
                .GroupBy(p => p.Name).Where(x => !x.Any(y => y.Value == "X"))

                .Select(g => g.OrderByDescending(p => p.Date).FirstOrDefault()) //<- Error
                .Select(g => g.FirstOrDefault()) // <- no Error

                .ToList();
}

When I use the first Select I get this error, with the second, the code is working fine. Anyone knows the reason?

Same Error found here

I'm using .NET Connector 6.7.4 so it can't be bug #68513

Answer 1

Let see. You have a perfectly valid LINQ to Entities query, it works with SqlServer provider and does not work with MySQL provider. Sounds like a MySQL provider bug to me, what else it could be? But which one? I don't see how that helps, but put my bet on #78610(initiated by ASP MVC MsSql to MySQL migration SO post), marked as duplicate of #76663. Or #77543 etc.

So MySQL connector has issues with OrderBy in subqueries. As a workaround, I could suggest (when possible) the alternative way of implementing MaxBy, i.e. (in pseudo code) instead of seq.OrderByDescending(col).FirstOrDefault() use the seq.FirstOrDefault(col == seq.Max(col)) pattern which works:

var Result = ctx.Items
    .Where(x => x.Contact.Country == Country)
    .GroupBy(p => p.Name)
    .Where(g => !g.Any(x => x.Value == "X"))
    .Select(g => g.FirstOrDefault(e => e.Date == g.Max(e1 => e1.Date)))
    .ToList();

Answer 2

Sort before grouping. The query providers can only translate a limited amount of expression trees and the way you have it apparently is one of them. Sorting first will have equivalent behavior.

var query = ctx.Items
    .Where(x => x.Contact.Country == Country)
    .OrderByDescending(x => x.Date)
    .GroupBy(p => p.Name)
    .Where(g => !g.Any(x => x.Value == "X"))
    .Select(g => g.FirstOrDefault());