Add columns with linq

Question

Add columns with linq

Navigation

#1 by (2 votes)

0

I need to add several columns and return them in a list. But in two columns I will add adding a condition.

SQL

SELECT 
SUM(MontoInicial)AS MontoInicial, 
SUM(CASE WHEN TipoMovimiento = 1 THEN Ingreso END) AS Ingreso,
SUM(CASE WHEN TipoMovimiento = 2 THEN Ingreso END) AS Devolucion,
SUM(Egreso)AS Egreso    
FROM MovimientoCajas
WHERE CAST(Fecha as date) = CAST('20181219' as date) AND PuntoEmisionId = 4
GO

I try to replicate it in LINQ

var result = from m in context.MovimientoCajas
                where DbFunctions.TruncateTime(m.Fecha) == DbFunctions.TruncateTime(fecha)
                      && m.PuntoEmisionId == puntoEmision
                select new MontoCajaExtend()
                {
                    MontoInical = Convert.ToDouble(m.MontoInicial),
                    Ingreso = m.TipoMovimiento == MovimientoCajaType.Ingreso
                        ? Convert.ToDouble(m.Ingreso)
                        : Convert.ToDouble(0),
                    Devolucion = m.TipoMovimiento == MovimientoCajaType.Devolucion
                        ? Convert.ToDouble(m.Ingreso)
                        : Convert.ToDouble(0),
                    Egreso = Convert.ToDouble(m.Egreso)
                };

I still need to apply SUM I'm going to return a list.

Applying the recommendation of Leandro

var result = from m in context.MovimientoCajas
                where DbFunctions.TruncateTime(m.Fecha) == DbFunctions.TruncateTime(fecha)
                      && m.PuntoEmisionId == puntoEmision
                group m by 1
                into g
                select new MontoCajaExtend()
                {
                    MontoInical = g.Sum(x => x.MontoInicial),
                    Ingreso = g.Sum(x => x.TipoMovimiento == MovimientoCajaType.Ingreso ? x.Ingreso : 0),
                    Devolucion = g.Sum(x => x.TipoMovimiento == MovimientoCajaType.Devolucion ? x.Ingreso : 0),
                    Egreso = g.Sum(x => x.Egreso)
                };

It gives me the expected result.

Tracking in the Profile

exec sp_executesql N'SELECT 
[GroupBy1].[K1] AS [C1], 
[GroupBy1].[A1] AS [C2], 
[GroupBy1].[A2] AS [C3], 
[GroupBy1].[A3] AS [C4], 
[GroupBy1].[A4] AS [C5]
FROM ( SELECT 
    [Filter1].[K1] AS [K1], 
    SUM([Filter1].[A1]) AS [A1], 
    SUM([Filter1].[A2]) AS [A2], 
    SUM([Filter1].[A3]) AS [A3], 
    SUM([Filter1].[A4]) AS [A4]
    FROM ( SELECT 
        1 AS [K1], 
        [Extent1].[MontoInicial] AS [A1], 
        CASE WHEN (1 = [Extent1].[TipoMovimiento]) THEN [Extent1].[Ingreso] ELSE cast(0 as decimal(18)) END AS [A2], 
        CASE WHEN (2 = [Extent1].[TipoMovimiento]) THEN [Extent1].[Ingreso] ELSE cast(0 as decimal(18)) END AS [A3], 
        [Extent1].[Egreso] AS [A4]
        FROM [dbo].[MovimientoCajas] AS [Extent1]
        WHERE (((convert (datetime2, convert(varchar(255), [Extent1].[Fecha], 102) ,  102)) = (convert (datetime2, convert(varchar(255), @p__linq__0, 102) ,  102))) OR ((convert (datetime2, convert(varchar(255), [Extent1].[Fecha], 102) ,  102) IS NULL) AND (convert (datetime2, convert(varchar(255), @p__linq__0, 102) ,  102) IS NULL))) AND ([Extent1].[PuntoEmisionId] = @p__linq__1)
    )  AS [Filter1]
    GROUP BY [K1]
)  AS [GroupBy1]',N'@p__linq__0 datetime2(7),@p__linq__1 int',@p__linq__0='2018-12-20 12:54:56.4477337',@p__linq__1=4

Indeed, as Leandro says, it does not group and I did not expect to group, but it seems to me that it is a trick to get Sum in g.

I need to make those conditions so that I can filter the exact data. How should my query be?

c# linq entity-framework-6

asked by Pedro Ávila 19.12.2018 в 23:28

source

1 answer

How to skew a div with css? [duplicate] Unfortunately the app stopped problem with imageview Android kit kat (API 19)

score 2 · Accepted Answer

You can apply the filter on the data and then only add the properties individually

var result = (from m in context.MovimientoCajas
              where DbFunctions.TruncateTime(m.Fecha) == DbFunctions.TruncateTime(fecha)
                      && m.PuntoEmisionId == puntoEmision
              select m).ToList();

var MontoInical = result.Sum(x=> x.MontoInical);
var Ingreso = result.Where(m=>m.TipoMovimiento == MovimientoCajaType.Ingreso)
                    .Sum(x=> x.Ingreso);
var Devolucion = result.Where(m=> m.TipoMovimiento == MovimientoCajaType.Devolucion)
                       .Sum(x=>x.Devolucion);
var Egreso = result.Sum(x=>x.Egreso);

Like something sounds strange to me because I do not apply filter by type of movement for the initial motorcycle and egress, but I want to think that these fields allow null so they are completed as appropriate