How to use sum in where clause using LINQ

Hi,

i want to use sum in where clause using LINQ.

public static IEnumerable<ResultSetUId> getPct8Holdings(DataTable dtHolding)
        {
            //get the ids satisfying the conditions VRT_01S_px_chk_8_ind = 1,     AND VRT_11A_pct_asset > 25     AND holding_count > 10     AND  SUM(VRT_11A_ActualAmt_d_Calculated_Amt) > 50000000

            IEnumerable<ResultSetUId> rslt8Holdings = (from HoldingRows in dtHolding.AsEnumerable()
                                                       where HoldingRows.Field<Boolean>("VRT_01S_px_chk_8_ind") == true
                                                      && HoldingRows.Field<Int16?>("VRT_11A_pct_asset") > 25
                                                      && HoldingRows.Field<Int64?>("holding_count") > 10
                                                      && sum(HoldingRows.Field<Int64?>("VRT_11A_ActualAmt_d_Calculated_Amt") > 50000000
                                                     
                                                    select new ResultSetUId
                                                    {
                                                        Id = HoldingRows.Field<Int32?>("VRT_11S_Unique_id")
                                                    }
                                  );

            return rslt8Holdings;
        }

Here ResultSetUId is a class.

 public class ResultSetUId
        {
            public Int32? Id { get; set; }
        }


i am not able to use sum(......) > 500000000. in the last line of where clause.

pls reply as soon as possible.
Thanks,
Shashi
shore-supportAsked:
Who is Participating?
 
shore-supportConnect With a Mentor Author Commented:
Hi,
i tried implementing with two quries, and i succeded.
thank you,
0
 
Meir RivkinFull stack Software EngineerCommented:
which error do u get?
0
 
joriszwaenepoelCommented:
Maybe you can try sometime like this:

(((IEnumerable<Int64>)HoldingRows.Field<Int64?>("VRT_11A_ActualAmt_d_Calculated_Amt") ).Sum() > 500000000);

Joris
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
shore-supportAuthor Commented:
Hi sedqwick,
i am getting syntax error.
not able to build even.

Thanks,
Shashi
0
 
Meir RivkinFull stack Software EngineerCommented:
what's the syntax error say?
0
 
shore-supportAuthor Commented:
Hi,

syntax error is...
"The name sum doesnot exists in the current context".


Thanks,
Shashi
0
 
Meir RivkinFull stack Software EngineerCommented:
did u mean to check the sum of the "VRT_11A_ActualAmt_d_Calculated_Amt" field for all the rows that apply for:
   where HoldingRows.Field<Boolean>("VRT_01S_px_chk_8_ind") == true
                                                      && HoldingRows.Field<Int16?>("VRT_11A_pct_asset") > 25
                                                      && HoldingRows.Field<Int64?>("holding_count") > 10
?
0
 
shore-supportAuthor Commented:
ya
0
 
Meir RivkinFull stack Software EngineerCommented:
i couldn't do it with one linq expression.
i figured that i need to check anyway, which row apply to the first 3 expressions.
then take them and sum their RT_11A_ActualAmt_d_Calculated_Amt value.
if the sum is bigger than 500000000, then return all the ID's as a IEnumerable of ResultSetUId,
otherwise return should be en empty list.

so here it is:

var result = (from HoldingRows in dtHolding.AsEnumerable()
                                                       where HoldingRows.Field<Boolean>("VRT_01S_px_chk_8_ind") == true
                                                      && HoldingRows.Field<Int16?>("VRT_11A_pct_asset") > 25
                                                      && HoldingRows.Field<Int64?>("holding_count") > 10
                                                select new {
ID=HoldingRows.Field<Int32?>("VRT_11S_Unique_id"),
ActualAmt=HoldingRows.Field<Int64?>("VRT_11A_ActualAmt_d_Calculated_Amt")
});
   

IEnumerable<ResultSetUId> rslt8Holdings = null;
if (result .Sum(n => n.ActualAmt) > 500000000)
            {
                rslt8Holdings = res.Select(n => new ResultSetUId { Id = n.ID });
            }

return rslt8Holdings;
0
 
Meir RivkinFull stack Software EngineerCommented:
currently if ActualAmt is less than 500000000 i return null, u can change it to return empty eunmerable by changing this line:
IEnumerable<ResultSetUId> rslt8Holdings = null;

to

IEnumerable<ResultSetUId> rslt8Holdings = new List<ResultSetUId>(0);
0
 
Meir RivkinFull stack Software EngineerCommented:
can u post your solution?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.