method witch calculate sum of several fields in linq query with different condition C#?

fahime abouhamze 126 Reputation points
2021-07-29T16:24:00.603+00:00

hi ,
I wrote a method and I intend to calculate count and sum of multiple fields with different conditions for each fields :
here is my method

           public List<SmsTypeV2> listAmout2(int smsType)

    {
        if (smsType == 2)
        {
            var amounts = (from p in db.AllBN_Dam
                           where p.SystemId == 1 & p.ToDate == yesterday()

                           select new SmsTypeV2
                           {
                               BNCnt = (from t in db.AllBN_Dam where p.TransactionTypeId == 1 & p.ElhKind_HvKind == 0 select t).Count(),
                               BNAmount = (from t in db.AllBN_Dam where p.TransactionTypeId == 1 & p.ElhKind_HvKind == 0 select p.Sum(x => x.Amount)),
                               ExteraCnt = (from t in db.AllBN_Dam where p.TransactionTypeId == 1 & p.ElhKind_HvKind == 212 select t).Count(),
                               ExteraAmount = (from t in db.AllBN_Dam where p.TransactionTypeId == 1 & p.ElhKind_HvKind == 212 select p.Sum(x => 
                               x.EzafiAmount)),
                               ReverseCnt = (from t in db.AllBN_Dam where p.TransactionTypeId == 1 & p.ElhKind_HvKind == 213 select t).Count(),
                               ReverseAmount = (from t in db.AllBN_Dam where p.TransactionTypeId == 1 & p.ElhKind_HvKind == 213  select t).Count(),
                               DamageAmount = (from t in db.AllBN_Dam where p.TransactionTypeId == 2  select p.Sum(x =>x.DamageAmount))    



                           }).ToList();

            return amounts;
        }
        else
        {
            return null;
        }
    } 

but it through bellow error :
'AllBN_Dam' does not contain a definition for 'Sum' and no accessible extension method 'Sum' accepting a first argument of type 'AllBN_Dam' could be found (are you missing a using directive or an assembly reference?)

would anyone please help me ?

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,381 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 112.9K Reputation points
    2021-07-29T17:23:45.937+00:00

    Maybe you should use t instead of p and adjust the code, for example:

    BNAmount = (from t in db.AllBN_Dam where t.TransactionTypeId == 1 && t.ElhKind_HvKind == 0).Sum(t => t.Amount)),
    ExteraAmount = (from t in db.AllBN_Dam where p.TransactionTypeId == 1 && p.ElhKind_HvKind == 212).Sum(t => t.EzafiAmount),
    

1 additional answer

Sort by: Most helpful
  1. Michael Taylor 49,246 Reputation points
    2021-07-29T17:29:51.07+00:00

    You are trying to run subqueries inside a larger query. Even in SQL that is a bad idea. Performance will be awful. I believe the correct approach here is to use grouping since you only seem to care about 2 fields in your dataset. Maybe I'm missing something here but it seems like your query is going to return back a single object for each row in your AllBN_Dam collection but every row will have the same value and it appears that the results would be for the single row so I'd expect most counts to be 1. Additionally you're using bitwise and (&) and I assume you meant logical and (&&).

    I'm wondering here if you actually just want 1 result that is a summary for the specific system ID(?) I'm breaking the query up so it is easier to understand.

    var group = from p in db.AllBN_Dam
                where p.SystemId == 1 && p.ToDate == yesterday()
                group p by new { TransactionType = p.TransactionTypeId, Kind = p.ElhKind_HvKind } into g
                select new { TransactionType = g.Key.TransactionType, Kind = g.Key.Kind, ItemCount = g.Count(), TotalAmount = g.Sum(x => x.Amount) };
    
    //To simplify the expression, create temp variables to filter data
    var bnItems = group.Where(x => x.TransactionType == 1 && x.Kind == 0);
    var exteraItems = group.Where(x => x.TransactionType == 1 && x.Kind == 212);
    var reverseItems = group.Where(x => x.TransactionType == 1 && x.Kind == 213);
    var damageItems = group.Where(x => x.TransactionType == 2);
    
    //One record for all rows
    var result = new SmsTypeV2() {
        BNCnt = bnItems.Max(x => x.ItemCount),
        BNAmount = bnItems.Sum(x => x.TotalAmount),
        ExteraCnt = exteraItems.Max(x => x.ItemCount),
        ExteraAmount = exteraItems.Sum(x => x.TotalAmount),
        ReverseCnt = reverseItems.Max(x => x.ItemCount),
        ReverseAmount = reverseItems.Sum(x => x.TotalAmount),
        DamageAmount = damageItems.Sum(x => x.TotalAmount),
    };