• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 692
  • Last Modified:

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
0
shore-support
Asked:
shore-support
  • 6
  • 4
1 Solution
 
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
 
shore-supportAuthor Commented:
Hi sedqwick,
i am getting syntax error.
not able to build even.

Thanks,
Shashi
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
shore-supportAuthor Commented:
Hi,
i tried implementing with two quries, and i succeded.
thank you,
0
 
Meir RivkinFull stack Software EngineerCommented:
can u post your solution?
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now