CODEWITHSUNDEEP
c#linqt-sql
Juan Pablo Gomez
Juan Pablo Gomez

Reputation: 5534

Group by Linq vs Transact Sql

I have this SQL query

select GrupoEmpaque,NumIdConceptoEmpaque,sum(NumCantidadEmpaques) 
    from Movimientos_Pedidos 
    where StrIdDocumento = '009000PV00000000000000599' and (GrupoEmpaque is null or GrupoEmpaque = 0 )
    group by GrupoEmpaque , NumIdConceptoEmpaque  

**It Returns:** 

NULL    338 25

In the other side I have this Linq , Pedido allready has only '009000PV00000000000000599' data

var EmpaquesItemUnico = Pedido.Movimientos_Pedidos
    .GroupBy(x => x.NumIdConceptoEmpaque)
    .Select(x => new { GrupoEmpaque = x.FirstOrDefault().GrupoEmpaque, TipoEmpaque = x.FirstOrDefault().Merlin_ConceptosFacturacionEmpaque, Cantidad = x.Sum(y => y.NumCantidadEmpaques) })
    .Where(x => x.GrupoEmpaque == 0 || x.GrupoEmpaque == null);

But now the results are       
NULL    338 28

Now My questions are:

Why TSQL returns 25 and Linq Returns 28? How to make those sentences return same results?

Upvotes: 1

Views: 73

Answers (1)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62498

You have to filter results first before projecting, and your both groupby statements of t-sql and linq are not same:

var EmpaquesItemUnico = Pedido.Movimientos_Pedidos
    .GroupBy(x => new
                  { 
                    NumIdConceptoEmpaque =x.NumIdConceptoEmpaque,
                    GrupoEmpaque  = x.GrupoEmpaque 
                  }
            )
    .Where(x => x.Key.GrupoEmpaque == 0 || x.Key.GrupoEmpaque == null)
    // now project here
    .Select(x=> new 
                   {
                    NumIdConceptoEmpaque  = x.Key.NumIdConceptoEmpaque,
                    GrupoEmpaque  = x.Key.GrupoEmpaque,
                    Sum = x.Sum(y => y.NumCantidadEmpaques)
                   });

Upvotes: 3

Related Questions