MySQL Bugs: #103049: MySql.EntityFrameworkCore generates invalid query for comparison

Bug #103049	MySql.EntityFrameworkCore generates invalid query for comparison
Submitted:	19 Mar 2021 19:59	Modified:	25 Mar 2021 13:17
Reporter:	Ivan Petrov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.0.0+MySQL8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bug, comparison, query generation

Description:
MySql.EntityFrameworkCore generates invalid query for comparison between int and long typed values

How to repeat:
Create a simple model

  class MyEntity {
    int? Id {get;set;}
    int? MyValue {get;set;}
  }

and context for it.

  class MyContext: DbContext {
    public DbSet<MyEntity> MyEntities {get;set;}
  }

then run a query:

  const long magic = 42;
  context.MyEntities.Where(e => e.MyValue == magic).SingleOrDefault();

The invalid query is generated:

SELECT `m`.`my_value`
FROM `my_entities` AS `m`
WHERE (CAST(`m`.`my_value` AS bigint) = 42)

Similar query to proof within the DB:

  SELECT x FROM (select 42 as x) t WHERE cast(x as bigint) = 42

Suggested fix:
Either support cast for "AS bigint" in SQL or remove that query building issue with C# connector.

Hello Ivan Petrov,

Thank you for the bug report.
This is most likely duplicate/related to Bug #101294, please see Bug #101294.

Regards,
Ashwini Patil