MySQL Bugs: #82991: Entity Framework 7 - LINQ - .Take() causes MySqlException: Undeclared variable:

Bug #82991	Entity Framework 7 - LINQ - .Take() causes MySqlException: Undeclared variable:
Submitted:	14 Sep 2016 12:33	Modified:	27 Sep 2022 21:52
Reporter:	Vaclav Elias	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	7.0.4, 7.0.5	OS:	Windows (Any)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	.Net Core, Entity Framework 7

Description:
Hi, when I do a very basic query 

var model = _context.wp_posts.Take(10).ToList(); 

I get this error: MySqlException: Undeclared variable: __p_0 

It seems that you do use LIMIT __p_0 in the query but you don't define __p_0. 

Is this a bug or not implemented? If it is a bug, do we need to escalate it here http://bugs.mysql.com/ or you can take care of it? 

A detailed log below 

Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1] 
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] 
SELECT `w`.`ID`, `w`.`comment_count`, `w`.`comment_status`, `w`.`guid`, `w`.`menu_order`, `w`.`ping_status`, `w`.`pinged`, `w`.`post_author`, `w`.`post_content`, `w`.`post_content_filtered`, `w`.`post_date`, `w`.`post_date_gmt`, `w`.`post_excerpt`, `w`.`post_mime_type`, `w`.`post_modified`, `w`.`post_modified_gmt`, `w`.`post_name`, `w`.`post_parent`, `w`.`post_password`, `w`.`post_status`, `w`.`post_title`, `w`.`post_type`, `w`.`to_ping` 
FROM `wp_posts` AS `w` 
LIMIT __p_0 
fail: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[1] 
An exception occurred in the database while iterating the results of a query. 
MySql.Data.MySqlClient.MySqlException: Undeclared variable: __p_0 
at MySql.Data.MySqlClient.MySqlStream.ReadPacket() 
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) 
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) 
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) 
at MySql.Data.MySqlClient.MySqlDataReader.NextResult() 
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) 
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection) 
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection) 
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext() 
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext() 
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext() 
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() 

How to repeat:
Make any Linq query and use Take()

Suggested fix:
No suggestions but I guess you are not transferring the values from Skip(x) and Take(y) to LIMIT x,y

I was testing with these packages:

"MySql.Data.Core": "7.0.4-IR-191",
"MySql.Data.EntityFrameworkCore": "7.0.5-IR21",

Hello Vaclav Elias,

Thank you for the bug report.
Could you please provide repeatable test case (sample project, code, create table statements/database etc. - please make it as private if you prefer) to
confirm this issue at our end?

Thanks,
Chiranjeevi.

Dear Chiranjeevi,

Thank you for your quick reply an interest.

The repeatable test case would take 1+ hour to set up and there is no time to do it as you can set it up yourselves in 5 minutes.

If you work yourselves on this project you can try to query any MySQL database and table with this.

var rowCount = 10; // or any number
var skipCount = 2; // or any number

var result = yourContext.yourTable.Take(rowCount).ToList();

var result2 = yourContext.yourTable.Skip(skipCount).Take(rowCount).ToList();

You should get a reported error in both cases. Reported also by another person 

http://forums.mysql.com/read.php?38,650020,650020#msg-650020

Regards, Vaclav.

This still appears to be a bug with version 7.0.6-IR31, is there any timeline on when this will be fixed, what is the complexity of the problem?

This is the limit/offset code used for SQLite, hopefully it can help.

https://github.com/aspnet/EntityFramework/blob/master/src/Microsoft.EntityFrameworkCore.Sq...

Hello Vaclav Elias,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.

This is a duplicated bug of http://bugs.mysql.com/bug.php?id=84453

Posted by developer:
 
Connector/NET no longer have support for EF 7, instead we have for EFCore which do not present this bug.