I'm sure there is a way but I don't know if I can do it:). Try this, though I don't know if it will work:
from rf in yourTable
group rf by RF_ID into rfg
select new {
sum2PFactor = rfg.Sum(x => x.2PFACTOR),
sum8PFactor= rfg.Sum(x => x.8PFACTOR),
minRfVal = rfg.Min(x => x.RF_VAL),
sumWKFactor = (from rf2 in yourTable group by rf2.RF_ID into rfg2 where rfg2.RF_FACTOR == rfg2.min(x => x.RF_VAL) select new {sum = rfg2.Sum(x => x.WK_FACTOR)})
Chithra_Iyer
Member
89 Points
414 Posts
help on linq query to group based on Id and do calculations
Jan 08, 2013 06:14 PM|LINK
I have a data table like one with the below structure .I have converted this to Iqueryable to use linq to group this based on RF_ID
and perform some calculations.. which i have no clues on how to do
MC_ID PK_ID RF_ID 2PFACTOR 8PFACTOR RF_VAL RF_FACTOR WK_FACTOR
234 22 12 21 52 3 2 12
234 31 12 null null 5 3 15
234 441 12 12 9 2 2 19
234 113 19 7 11 17 5 41
235 221 19 117 null 5 3 17
234 212 76 15 null 1 1 70
First i want to group this based on RF_ID so this will return 3 records and also few other calculations has to be done along with this
1. get the min of RF_VAL when grouped based on RF_ID
2. sum of 2PFACTOR when grouped based on RF_ID
3. sum of 8PFACTOR when grouped based on RF_ID
4. Get min of the RF_VAL based on RF_ID
5. If this min RF_VAL value is there in the RF_Factor then corresponding value in the WK_FACTOR needs to be summed up..
and finally should return a list
Based on the above table list should contain 3 elements...
after calculations with in linq it should return like
RF_ID 2PFACTOR 8PFACTOR RF_VAL WK_FACTOR
12 33 61 2 31
19 124 11 5 41
76 15 null 1 70
Please ...could you help me out to solve this ....
Many Thanks
msmk
Participant
776 Points
159 Posts
Re: help on linq query to group based on Id and do calculations
Jan 08, 2013 07:08 PM|LINK
For points 1 to 4:
from rf in yourTable
group rf by rf.RF_ID into rfg
select new {
sum2PFactor = rfg.Sum(x => x.2PFACTOR),
sum8PFactor= rfg.Sum(x => x.8PFACTOR),
minRfVal = rfg.Min(x => x.RF_VAL)
};
For point 5:
from rf in yourTable
group rf by rf.RF_ID into rfg
where RF_FACTOR == rfg.min(x => x.RF_VAL)
select new {
sumWKFactor = rfg.Sum(x => x.WK_FACTOR)
};
Chithra_Iyer
Member
89 Points
414 Posts
Re: help on linq query to group based on Id and do calculations
Jan 08, 2013 07:25 PM|LINK
Thaanks a lot.....
By any chance can we combine these two in to a single result set..
msmk
Participant
776 Points
159 Posts
Re: help on linq query to group based on Id and do calculations
Jan 08, 2013 07:41 PM|LINK
I'm sure there is a way but I don't know if I can do it:). Try this, though I don't know if it will work:
from rf in yourTable
group rf by RF_ID into rfg
select new {
sum2PFactor = rfg.Sum(x => x.2PFACTOR),
sum8PFactor= rfg.Sum(x => x.8PFACTOR),
minRfVal = rfg.Min(x => x.RF_VAL),
sumWKFactor = (from rf2 in yourTable group by rf2.RF_ID into rfg2 where rfg2.RF_FACTOR == rfg2.min(x => x.RF_VAL) select new {sum = rfg2.Sum(x => x.WK_FACTOR)})
};