Getting different SQL output for the same LINQ Query since upgrading to .NET 8

[robherman]

The issue

The problem i am facing since .NET 8.0 and Pomelo 8.0 is that the same LINQ query outputs different SQL. The latest SQL output is a query that's much worse in terms of performance than the original one. I cannot see any breaking changes in EF 8.0 that could explain this, is there something that has changed translation wise in Pomelo.MySql?

LINQ:

return (from g in (from item in (from c in _context.GComprobanteRecibido
                                             where (areaId == null || c.GLiquidacionAreaId == areaId)
                                             && (areasOcultas == null || !areasOcultas.Contains(c.GLiquidacionAreaId))
                                             select new
                                             {
                                                 Area = c.GLiquidacionAreaId,
                                                 Monto = c.ComprobanteMonto * c.GComprobanteTipo.Signo,
                                                 Periodo = c.ComprobantesFecha.Year * 100 + c.ComprobantesFecha.Month,
                                             })
                               where (periodo == null || item.Periodo == periodo)
                               group item by new { item.Area, item.Periodo } into grouped
                               select new
                               {
                                   Monto = grouped.Sum(x => x.Monto),
                                   Periodo = grouped.Key.Periodo,
                                   Area = grouped.Key.Area,
                                   Count = grouped.Count()
                               })
                    join area in _context.GLiquidacionArea
                    on g.Area equals area.Id into areas
                    from area in areas.DefaultIfEmpty()
                    select new ComprobantesByPeriodoAndArea
                    {
                        Monto = g.Monto,
                        GLiquidacionArea = area,
                        Periodo = g.Periodo,
                        Count = g.Count
                    });

Output before .NET 8 Pomelo 8:

SELECT `t`.`Monto`, `g1`.`id`, `g1`.`acepta_asociacion_cuil_pedido_ok`, `g1`.`acepta_auditoria_facturas`, `g1`.`acepta_carga_facturas`, `g1`.`activo`, `g1`.`admite_trazabilidad`, `g1`.`creado`, `g1`.`facturado_orden_de_activo`, `g1`.`imputa_en_liquidaciones`, `g1`.`is_discapacidad`, `g1`.`liquida_solo_gerenciadores_virtuales`, `g1`.`nombre`, `g1`.`pedido_externo`, `g1`.`plataforma_hospitales`, `t`.`Periodo`, `t`.`Count`
FROM (
    SELECT COALESCE(SUM(`g`.`comprobante_monto` * CAST(`g0`.`signo` AS decimal(65,30))), 0.0) AS `Monto`, (EXTRACT(year FROM `g`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g`.`comprobante_fecha`) AS `Periodo`, `g`.`g_liquidacion_areaId` AS `Area`, COUNT(*) AS `Count`
    FROM `g_comprobante_recibido` AS `g`
    INNER JOIN `g_comprobante_tipo` AS `g0` ON `g`.`g_comprobante_tipoId` = `g0`.`id`
    GROUP BY `g`.`g_liquidacion_areaId`, (EXTRACT(year FROM `g`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g`.`comprobante_fecha`)
) AS `t`
LEFT JOIN `g_liquidacion_area` AS `g1` ON `t`.`Area` = `g1`.`id`

Output after .NET 8

SELECT `t0`.`Monto`, `g2`.`id`, `g2`.`acepta_asociacion_cuil_pedido_ok`, `g2`.`acepta_auditoria_facturas`, `g2`.`acepta_carga_facturas`, `g2`.`activo`, `g2`.`admite_trazabilidad`, `g2`.`creado`, `g2`.`facturado_orden_de_activo`, `g2`.`imputa_en_liquidaciones`, `g2`.`is_discapacidad`, `g2`.`liquida_solo_gerenciadores_virtuales`, `g2`.`nombre`, `g2`.`pedido_externo`, `g2`.`plataforma_hospitales`, `t0`.`Periodo`, `t0`.`Count`
FROM (
    SELECT (
        SELECT COALESCE(SUM(`t1`.`comprobante_monto` * CAST(`g0`.`signo` AS decimal(65,30))), 0.0)
        FROM (
            SELECT `g1`.`id`, `g1`.`archivado`, `g1`.`CUIT`, `g1`.`cae_invalidado_mensaje`, `g1`.`cae_validado`, `g1`.`cargado_comprobantes_masivos`, `g1`.`cargado_desde_mis_comprobantes`, `g1`.`cargado_modulo_hospitales`, `g1`.`cargado_por_dev_ok`, `g1`.`codigo_sss`, `g1`.`comentario`, `g1`.`comentario_privado`, `g1`.`comentario_publico`, `g1`.`comprobante_cae`, `g1`.`comprobante_link`, `g1`.`comprobante_monto`, `g1`.`comprobante_numero`, `g1`.`comprobante_punto_de_venta`, `g1`.`comprobante_fecha`, `g1`.`confirmado`, `g1`.`confirmado_fecha`, `g1`.`confirmado_userId`, `g1`.`creado`, `g1`.`exportado_s3`, `g1`.`fecha_vencimiento`, `g1`.`g_auditoriaId`, `g1`.`g_comprobante_tipoId`, `g1`.`g_gerenciadorId`, `g1`.`g_integracion_expedienteId`, `g1`.`g_liquidacion_areaId`, `g1`.`hospital_nombre`, `g1`.`i_integracion_dr_envio_detalleId`, `g1`.`integracion_periodo_prestacion`, `g1`.`leidoQR`, `g1`.`nro_liquidacion`, `g1`.`recibido`, `g1`.`referencia_externa`, `g1`.`refes`, `g1`.`sur_expedienteId`, `g1`.`s_userId`, (EXTRACT(year FROM `g1`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g1`.`comprobante_fecha`) AS `Periodo`
            FROM `g_comprobante_recibido` AS `g1`
        ) AS `t1`
        INNER JOIN `g_comprobante_tipo` AS `g0` ON `t1`.`g_comprobante_tipoId` = `g0`.`id`
        WHERE (`t`.`g_liquidacion_areaId` = `t1`.`g_liquidacion_areaId`) AND ((`t`.`Periodo` = `t1`.`Periodo`) OR (`t`.`Periodo` IS NULL AND (`t1`.`Periodo` IS NULL)))) AS `Monto`, `t`.`Periodo`, `t`.`g_liquidacion_areaId` AS `Area`, COUNT(*) AS `Count`
    FROM (
        SELECT `g`.`g_liquidacion_areaId`, (EXTRACT(year FROM `g`.`comprobante_fecha`) * 100) + EXTRACT(month FROM `g`.`comprobante_fecha`) AS `Periodo`
        FROM `g_comprobante_recibido` AS `g`
    ) AS `t`
    GROUP BY `t`.`g_liquidacion_areaId`, `t`.`Periodo`
) AS `t0`
LEFT JOIN `g_liquidacion_area` AS `g2` ON `t0`.`Area` = `g2`.`id`

Further technical details

MySQL version: 8 Operating system: Ubuntu 22.04 Pomelo.EntityFrameworkCore.MySql version: 8.0.2 Microsoft.AspNetCore.App version: 8.0.2

[lauxjpn]

I cannot see any breaking changes in EF 8.0 that could explain this, is there something that has changed translation wise in Pomelo.MySql?

There have been many translation changes between Pomelo 7.0.x and 8.0.x and EF Core 7.0.x and 8.0.x. The issue you are experiences does not seem to be a breaking change (since it still works, just slower, if I understand you correctly). Most likely, it is a translation change in EF Core, but we can't say for sure.

@robherman If you want us to track down the changes ones responsible for your complex query in question, please post an MRE, so we can reproduce the exact query on our end.

Also, please update the OP with the exact MySQL version you are using, post the Pomelo and EF Core version that you were using before the upgrade to Pomelo 8.0.2 and the average duration of the query in ms before and after the upgrade.

Thanks!

[lauxjpn]

I cannot see any breaking changes in EF 8.0 that could explain this, is there something that has changed translation wise in Pomelo.MySql?

There have been many translation changes between Pomelo 7.0.x and 8.0.x and EF Core 7.0.x and 8.0.x. The issue you are experiences does not seem to be a breaking change (since it still works, just slower, if I understand you correctly). Most likely, it is a translation change in EF Core, but we can't say for sure.

@robherman If you want us to track down the changes ones responsible for your complex query in question, please post an MRE, so we can reproduce the exact query on our end.

Also, please update the OP with the exact MySQL version you are using, post the Pomelo and EF Core version that you were using before the upgrade to Pomelo 8.0.2 and the average duration of the query in ms before and after the upgrade.

Thanks!

@robherman Any update on this?