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

5
votes

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

3
c#mysqlentity-frameworklinq
That looks awful, too. Why exactly are you Ordering in a Grouping? – DevilSuichiro
@DevilSuichiro I need the newest one – fubo
It actually looks like the error is occurring at .GroupBy(p => p.Name) to me. – Barry O'Kane
Could you please send the model (Item and Content classes, mapping and context)? – bubi
PS: looking other issues similar to this, it looks like a bug of the EF provider that generates a wrong HAVING clause – bubi

3 Answers

4
votes

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

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

You can capture the SQL that the code generates, see https://stackoverflow.com/a/20751723/3572241 and other answers to that question.

Then try running the SQL in mysql to isolate the problem.