Where clause operator missing in generated SQL for bit(1) column

[t-bzhan]

We have a MySQL table with a column IsActive with type bit(1), it is mapped to boolean type in our object model. Prior to upgrading to entityframework core 7.0.5. Below Linq query

            var result = this.unitOfWork.PipeDeploymentMaps.GetQueryable()
               .Where(m => m.IsActive && deploymentIds.Any(d => d == m.DeploymentId) &&
               !((m.ValidTo != null && m.ValidTo <= startUTCTime) || (m.ValidFrom > endUTCTime)))
               .GroupBy(m => m.DeploymentId)

will translate to:

SELECT `m`.`Id`, `m`.`CreatedBy`, `m`.`CreatedDate`, `m`.`DeploymentId`, `m`.`EndpointId`, `m`.`IsActive`, `m`.`ModifiedBy`, `m`.`ModifiedDate`, `m`.`PipeId`, `m`.`ProviderId`, `m`.`ValidFrom`, `m`.`ValidTo`
      FROM `pipe_deployment_mapping` AS `m`
      WHERE ((`m`.`IsActive` = TRUE) AND `m`.`DeploymentId` IN ('decb0ccf-4f64-48b2-a319-9123d54dcad5')) AND ((`m`.`ValidTo` IS NULL OR (`m`.`ValidTo` > timestamp('2023-06-08 07:37:00.000000'))) AND (`m`.`ValidFrom` <= timestamp('2023-06-08 08:37:00.000000')))
      ORDER BY `m`.`DeploymentId`

However entityframework core 7.0.5 will translate the query to :

SELECT `p`.`DeploymentId`, `p`.`Id`, `p`.`CreatedBy`, `p`.`CreatedDate`, `p`.`EndpointId`, `p`.`IsActive`, `p`.`ModifiedBy`, `p`.`ModifiedDate`, `p`.`PipeId`, `p`.`ProviderId`, `p`.`ValidFrom`, `p`.`ValidTo`
      FROM `pipe_deployment_mapping` AS `p`
      WHERE (`p`.`IsActive` AND (`p`.`DeploymentId` = 'decb0ccf-4f64-48b2-a319-9123d54dcad5')) AND ((`p`.`ValidTo` IS NULL OR (`p`.`ValidTo` > timestamp('2023-06-08 07:37:00.000000'))) AND (`p`.`ValidFrom` <= timestamp('2023-06-08 08:37:00.000000')))
      ORDER BY `p`.`DeploymentId`

(`m`.`IsActive` = TRUE) is replace by p`.`IsActive, without the operator, the composite indexes (IsActive, DeploymentId) is not used and cause full scan.

Compare to the previous explain output:

Further technical details

MySQL version: 5.6 Operating system: Pomelo.EntityFrameworkCore.MySql version: 7.0.0 Microsoft.AspNetCore.App version: 6.0.16

Other details about my project setup:

[austinpapritz]

Perhaps this discussion helps? https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1104

Especially this comment: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1104#issuecomment-645631872

Essentially adding EnableIndexOptimizedBooleanColumns() to the optionsBuilder.

The wiki also states

EnableIndexOptimizedBooleanColumns

Configures the context to optimize System.Boolean mapped columns for index usage, by translating e.BoolColumn to BoolColumn = TRUE and !e.BoolColumn to BoolColumn = FALSE.