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

Question

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 ?

Answer 1

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),

Answer 2

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),
};