score:0

Accepted answer

thanks to ivan in the comments i came up with this solution:

var fouryearsago = datetime.now.addyears(-4).year;

var datawithoutgrouping = from m in maindata
                              where m.cid == "334r" && m.stmtdt.value.year > fouryearsago
                              join a in adjustments
                                on new {m.stmtdt, m.stmtno} equals new {a.stmtdt, a.stmtno} into grp
                              from ja in grp.defaultifempty()
                              select new {
                                                dt = m.stmtdt,
                                                no = m.stmtno,
                                                fee = m.payamount,
                                                adjustment = ja.amount
                                            };

    var data = (from b in datawithoutgrouping
                group b by new {b.dt, b.no }into grp
                select new {
                   statmentfee = grp.sum(x => x.fee),
                   statementadjustments = grp.sum(x => x.adjustment),
                   statementdate = grp.key.dt,
                   statementno = grp.key.no
                   }).tolist();

i just needed to use grp.key as the firstordefault() was creating a select for each record.

also, here is the answer in vb(i translated my question into c# as vb isn't that popular here and i translated it back) if anyone needs it:

 dim fouryearsago = datetime.now().addyears(-4).year

    dim datawithoutgrouping = from m in dbcontext.maindatas
                              where m.cid = "334r" andalso m.stmtdt.value.year > fouryearsago
                              group join a in dbcontext.adjustments
                                on new with {m.stmtdt, m.stmtno} equals new with {a.stmtdt, a.stmtno} into group
                              from ja in group.defaultifempty()
                              select new with {
                                                .dt = m.stmtdt,
                                                .no = m.stmtno,
                                                .fee = m.payamount,
                                                .adjustment = ja.amount
                                            }

    dim data = (from b in datawithoutgrouping
                group b by grpkeys = new with {b.dt, b.no} into group
                select new with {
                   .statmentfee = group.sum(function(x) x.fee),
                   .statementadjustments = group.sum(function(x) x.adjustment),
                   .statementdate = grpkeys.dt,
                   .statementno = grpkeys.no
                   }).tolist()

it generates this sql:

declare @p0 varchar(1000) = '334r'
declare @p1 int = 2014
-- endregion
select sum([t2].[payamount]) as [statmentfee], sum([t2].[value]) as [statementadjustments], [t2].[stmtdt] as [statementdate], [t2].[stmtno] as [statementno]
from (
    select [t0].[stmtdt], [t0].[stmtno], [t0].[payamount], [t1].[amount] as [value], [t0].[cid]
    from [maindata] as [t0]
    left outer join [adjustments] as [t1] on ([t0].[stmtdt] = [t1].[stmtdt]) and ([t0].[stmtno] = [t1].[stmtno])
    ) as [t2]
where ([t2].[cid] = @p0) and (datepart(year, [t2].[stmtdt]) > @p1)
group by [t2].[stmtdt], [t2].[stmtno]

score:0

this is the result i got:

if you have already your ef for maindata and adjustment

i created a list:

    list<maindata> mdata = new list<maindata>();
    list<adjustments> adj = new list<adjustments>();
    list<result> resfinal = new list<result>();

and the linq

var gety = datetime.now.addyears(-4);
            var res = from h in mdata
                      join j in adj
                      on h.stmtno equals j.stmtno
                      select new result { stmtdate = h.stmtdate, stmtno = h.stmtno, fee = h.fee, adj = j.adj, total = (h.fee * j.adj) };
            resfinal = res.cast<result>().where(x=>x.stmtdate > gety).tolist();

the final class holder as a result

 public class result
    {
        public datetime stmtdate { get; set; }
        public int stmtno { get; set; }
        public double fee { get; set; }
        public double adj { get; set; }
        public double total { get; set; }
    }

Related Query

More Query from same tag