c# - Linq2SQL group-by and sum optimisation -

i've been banging head on 1 while now. want achieve this:

select [t2].[nartkey], [t2].[value] [nqty] (     select sum([t0].[nqty]) [value], [t1].[nartkey]         [vdatstocktransactions] [t0]         inner join [regartsku] [t1] on [t0].[nsku] = [t1].[nsku]         group [t1].[nartkey]         ) [t2]     inner join [regarticles] [t3] on [t2].[nartkey] = [t3].[nartkey]     inner join [reggroupconnector] [t4] on [t2].[nartkey] = [t4].[nartkey] [t2].[value] > @p0 

what have far linq gives me pretty want, exept quantity...

from trans in context.vdatstocktransactions join sku in context.regartskus on trans.nsku equals sku.nsku group trans new { sku.nartkey } grp grp.sum(g => g.nqty) > 0 join art in context.regarticles on grp.key.nartkey equals art.nartkey        join ca in context.reggroupconnectors on grp.key.nartkey equals ca.nartkey select new {     nartkey = grp.key.nartkey,     //nqty = grp.sum(g => g.nqty) }; 

however, if uncomment nqty this:

select [t7].[nartkey], [t7].[value] [nqty] (     select [t3].[nartkey], (         select sum([t5].[nqty])         [vdatstocktransactions] [t5]         inner join [regartsku] [t6] on [t5].[nsku] = [t6].[nsku]         [t2].[nartkey] = [t6].[nartkey]         ) [value], [t2].[value] [value2]     (         select sum([t0].[nqty]) [value], [t1].[nartkey]         [vdatstocktransactions] [t0]         inner join [regartsku] [t1] on [t0].[nsku] = [t1].[nsku]         group [t1].[nartkey]         ) [t2]     inner join [regarticles] [t3] on [t2].[nartkey] = [t3].[nartkey]     inner join [reggroupconnector] [t4] on [t2].[nartkey] = [t4].[nartkey]     ) [t7] [t7].[value2] > @p0 

why create subquery , there way avoid it? makes quite big difference in performance figure out. want use quantity ([t2].[value]) , select statement.

that subquery exists when there more 1 join after group if remove either query generates expected sql.

note: stripped down original query keep parts generating behavior easier follow.

i haven't tested this, might trick:

var quantities =     trans in context.vdatstocktransactions     group trans trans.sku.nartkey grp     select new     {         nqty = grp.sum(g => g.nqty),          nartkey = grp.key      };  var results =     quantity in quantities     join art in context.regarticles         on quantity.nartkey equals art.nartkey            join ca in context.reggroupconnectors          on quantity.nartkey equals ca.nartkey     quantity.nqty > 0     select quantity;