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

That looks awful, too. Why exactly are you Ordering in a Grouping? — DevilSuichiro, Apr 21 '16 at 11:40
It actually looks like the error is occurring at `.GroupBy(p => p.Name)` to me. — Barry O'Kane, Apr 21 '16 at 11:42
Could you please send the model (Item and Content classes, mapping and context)? — bubi, Aug 09 '16 at 07:22
PS: looking other issues similar to this, it looks like a bug of the EF provider that generates a wrong HAVING clause — bubi, Aug 09 '16 at 07:58
@fubo Why I have a feeling I've already seen that query. Or it was another MySQL provider bug. It really doesn't matter what # the bug is, the most important is that it definitely is a bug, so I don't know what are you expecting here. — Ivan Stoev, Aug 12 '16 at 17:49
@IvanStoev if it's a bug i expect at least a bug number which i couldnt find — fubo, Aug 15 '16 at 05:54

score 4 · Accepted Answer · edited May 23 '17 at 11:51

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

score 1 · Answer 2 · answered Aug 08 '16 at 14:58

1

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

answered Aug 08 '16 at 14:58

Jeff Mercado

129,526
32
251
272

2

Actually sort before grouping is ignored by query providers, so although the above does not generate exception, I doubt it returns the intended result. The issue is specific to MySQL provider (bug) - the original query works w/o problem with SqlServer for instance. – Ivan Stoev Aug 12 '16 at 17:45

score 0 · Answer 3 · edited May 23 '17 at 12:14

0

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.

edited May 23 '17 at 12:14

Community

1
1

answered Apr 21 '16 at 15:38

Peter Bill

508
3
12

2

To capture the query you can remove the ToList() and just point the mouse on the object. – bubi Aug 09 '16 at 07:15

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

3 Answers3

Linked