Bug #103049 MySql.EntityFrameworkCore generates invalid query for comparison
Submitted: 19 Mar 19:59 Modified: 25 Mar 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

[19 Mar 19:59] Ivan Petrov
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.
[25 Mar 13:17] MySQL Verification Team
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