fubo fubo - 3 months ago 10
MySQL Question

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

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

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