[Solved]-Conditional combined sum in LINQ-LINQ,C#

how about this?

from r in rows
group r by new { r.date.year, r.date.month }
into g
let type2days = g.where( a => a.type == 2 ).select( a => a.date.day ).distinct()
let filtered = g.where( a => a.type == 2 || type2days.contains(a.date.day) == false )
select
    new
        {
            date = new datetime(g.key.year, g.key.month, 1),
            hours = filtered.sum(a => a.amount)
        };

how would this look to you?

var query =
    from r in rows.toarray()
    group r by new { r.date.year, r.date.month } into g
    let lookup = g.tolookup(x => x.type, x => x.amount)
    let hours = lookup[2].any() ? lookup[2].sum() : lookup[1].sum()
    select new
    {
        date = new datetime(g.key.year, g.key.month, 1),
        hours,
    };

note the .toarray() as you need to bring your data into memory to make this work.

i assumed that type was an integer with values of either 1 or 2.

on further thought i don't think it is possible to do this kind of grouping in a single query without bringing it into memory.

so the best alternative is to minimize the memory. if this doesn't work then you'll need to break it down into several queries.

var query1 =
    from r in rows
    group r.amount by new
    {
        r.date.year,
        r.date.month,
        r.type,
    } into g
    select new
    {
        g.key.year,
        g.key.month,
        g.key.type,
        amount = g.sum(),
    };

var query2 =
    from r in query1.toarray()
    group r by new
    {
        r.year,
        r.month,
    } into g
    let lookup = g.tolookup(x => x.type, x => x.amount)
    let hours = lookup[2].any() ? lookup[2].sum() : lookup[1].sum()
    select new
    {
        date = new datetime(g.key.year, g.key.month, 1),
        hours,
    };

is this a option a possibility?

okay, i think i've got this figured out. i've tested it with some data, and that works.

testdata and sql testquery:

declare @table table (
    datum datetime,
    amount int,
    [type] int
)

insert into @table (datum, amount, [type]) values
('2012-01-01',200,1),
('2012-01-01',100,2),
('2012-01-02',500,1),
('2012-03-01',200,1),
('2012-03-01',100,1),
('2012-03-02',500,2)

select month(datum), year(datum), count(*), sum(amount)
from @table t
inner join (
    select day (datum) as _day, month(datum) as _month, year(datum) _year,
    max([type]) as _type
    from @table
    group by day (datum), month(datum), year(datum)
) x
on _month = month (t.datum)
and _year = year(t.datum)
and _day = day(t.datum)
and _type = t.[type]
group by month(datum), year(datum)

result:

(no column name)    (no column name)    (no column name)    (no column name)
1   2012    2   600
3   2012    3   800

the translated linq query, tested with l2s and a 'real' test-table with test-data.

using (dataclasses1datacontext ctx = new dataclasses1datacontext()) {
    var rows = ctx.tests;

    var query = rows
        .join(
            rows.groupby(rr =>
                new { rr.datum.day, rr.datum.month, rr.datum.year },
                (key, data) => new { year = key.year, month = key.month, day = key.day, maxtype = data.select(xxx => xxx.type).max() }
            ),
            rr => new { day = rr.datum.day, month = rr.datum.month, year = rr.datum.year, type = rr.type },
            rr => new { day = rr.day, month = rr.month, year = rr.year, type = rr.maxtype },
            (r, r1) => r

        )
        .groupby(r =>
            new { year = r.datum.year, month = r.datum.month },
            (key, data) => new { year = key.year, month = key.month, amount = data.select(xx => xx.amount).sum() }
        )
        .tolist();
}

this returns the same results.

and, for the fun of it, the sql-query that l2s generated from the linq query.

select [t7].[value] as [year], [t7].[value2] as [month], (
    select sum([t8].[amount])
    from [dbo].[test] as [t8]
    inner join (
        select [t11].[value3], [t11].[value2], [t11].[value], (
            select max([t12].[type])
            from [dbo].[test] as [t12]
            where ((([t11].[value] is null) and (datepart(day, [t12].[datum]) is null)) or (([t11].[value] is not null) and (datepart(day, [t12].[datum]) is not null) and ((([t11].[value] is null) and (datepart(day, [t12].[datum]) is null)) or (([t11].[value] is not null) and (datepart(day, [t12].[datum]) is not null) and ([t11].[value] = datepart(day, [t12].[datum])))))) and ((([t11].[value2] is null) and (datepart(month, [t12].[datum]) is null)) or (([t11].[value2] is not null) and (datepart(month, [t12].[datum]) is not null) and ((([t11].[value2] is null) and (datepart(month, [t12].[datum]) is null)) or (([t11].[value2] is not null) and (datepart(month, [t12].[datum]) is not null) and ([t11].[value2] = datepart(month, [t12].[datum])))))) and ((([t11].[value3] is null) and (datepart(year, [t12].[datum]) is null)) or (([t11].[value3] is not null) and (datepart(year, [t12].[datum]) is not null) and ((([t11].[value3] is null) and (datepart(year, [t12].[datum]) is null)) or (([t11].[value3] is not null) and (datepart(year, [t12].[datum]) is not null) and ([t11].[value3] = datepart(year, [t12].[datum]))))))
            ) as [value4]
        from (
            select [t10].[value], [t10].[value2], [t10].[value3]
            from (
                select datepart(day, [t9].[datum]) as [value], datepart(month, [t9].[datum]) as [value2], datepart(year, [t9].[datum]) as [value3]
                from [dbo].[test] as [t9]
                ) as [t10]
            group by [t10].[value], [t10].[value2], [t10].[value3]
            ) as [t11]
        ) as [t13] on (datepart(day, [t8].[datum]) = [t13].[value]) and (datepart(month, [t8].[datum]) = [t13].[value2]) and (datepart(year, [t8].[datum]) = [t13].[value3]) and ([t8].[type] = [t13].[value4])
    where ((([t7].[value] is null) and (datepart(year, [t8].[datum]) is null)) or (([t7].[value] is not null) and (datepart(year, [t8].[datum]) is not null) and ((([t7].[value] is null) and (datepart(year, [t8].[datum]) is null)) or (([t7].[value] is not null) and (datepart(year, [t8].[datum]) is not null) and ([t7].[value] = datepart(year, [t8].[datum])))))) and ((([t7].[value2] is null) and (datepart(month, [t8].[datum]) is null)) or (([t7].[value2] is not null) and (datepart(month, [t8].[datum]) is not null) and ((([t7].[value2] is null) and (datepart(month, [t8].[datum]) is null)) or (([t7].[value2] is not null) and (datepart(month, [t8].[datum]) is not null) and ([t7].[value2] = datepart(month, [t8].[datum]))))))
    ) as [amount]
from (
    select [t6].[value], [t6].[value2]
    from (
        select datepart(year, [t0].[datum]) as [value], datepart(month, [t0].[datum]) as [value2]
        from [dbo].[test] as [t0]
        inner join (
            select [t3].[value3], [t3].[value2], [t3].[value], (
                select max([t4].[type])
                from [dbo].[test] as [t4]
                where ((([t3].[value] is null) and (datepart(day, [t4].[datum]) is null)) or (([t3].[value] is not null) and (datepart(day, [t4].[datum]) is not null) and ((([t3].[value] is null) and (datepart(day, [t4].[datum]) is null)) or (([t3].[value] is not null) and (datepart(day, [t4].[datum]) is not null) and ([t3].[value] = datepart(day, [t4].[datum])))))) and ((([t3].[value2] is null) and (datepart(month, [t4].[datum]) is null)) or (([t3].[value2] is not null) and (datepart(month, [t4].[datum]) is not null) and ((([t3].[value2] is null) and (datepart(month, [t4].[datum]) is null)) or (([t3].[value2] is not null) and (datepart(month, [t4].[datum]) is not null) and ([t3].[value2] = datepart(month, [t4].[datum])))))) and ((([t3].[value3] is null) and (datepart(year, [t4].[datum]) is null)) or (([t3].[value3] is not null) and (datepart(year, [t4].[datum]) is not null) and ((([t3].[value3] is null) and (datepart(year, [t4].[datum]) is null)) or (([t3].[value3] is not null) and (datepart(year, [t4].[datum]) is not null) and ([t3].[value3] = datepart(year, [t4].[datum]))))))
                ) as [value4]
            from (
                select [t2].[value], [t2].[value2], [t2].[value3]
                from (
                    select datepart(day, [t1].[datum]) as [value], datepart(month, [t1].[datum]) as [value2], datepart(year, [t1].[datum]) as [value3]
                    from [dbo].[test] as [t1]
                    ) as [t2]
                group by [t2].[value], [t2].[value2], [t2].[value3]
                ) as [t3]
            ) as [t5] on (datepart(day, [t0].[datum]) = [t5].[value]) and (datepart(month, [t0].[datum]) = [t5].[value2]) and (datepart(year, [t0].[datum]) = [t5].[value3]) and ([t0].[type] = [t5].[value4])
        ) as [t6]
    group by [t6].[value], [t6].[value2]
    ) as [t7]

i have no idea how efficient this sql is, you're going to have to try it.