none
Linq Sum on a Grouped Column not quite right RRS feed

  • Question

  • Hello,

    I am really close but can't figure out what I am missing as to why my grouping is getting blown apart by my sum function....well I mean I know *I* am the one making the mistake somewhere.

    T-SQL Equiv:

    SELECT TransactionDate, LoanType, COUNT(LoanType) AS 'CountLoans', Sum(LoanCurrPrincipal) AS 'LoanValue'
    FROM Loan_CSM7 with(nolock)
    WHERE Sourceid = 7
    AND LoanType = 'DLSS'
    GROUP BY TransactionDate, LoanType
    ORDER BY TransactionDate ASC
    

    Results:

    TransactionDate       LoanType     CountLoans           LoanValue
    2010-06-22                 DLSS          3381                  6878441.00

    Linq Query which works perfectly until I add the sum operation:

    (from LoanStatistics in Loan_CSM7s
    where LoanStatistics.SourceID.Equals(7)
    && LoanStatistics.LoanType == "DLSS"
    group LoanStatistics.LoanType by
    new { TransDate = LoanStatistics.TransactionDate, LoanTyp = LoanStatistics.LoanType, LoanVal = LoanStatistics.LoanCurrPrincipal} into grpLoanType
    orderby grpLoanType.Key.TransDate
    select new { LoanType = grpLoanType.Key.LoanTyp, TransDate = grpLoanType.Key.TransDate, DailyLoanCount = grpLoanType.Count(), DailyLoanValue = grpLoanType.Sum(LoanStatistics => grpLoanType.Key.LoanVal) })
    

    Results:

    LoanType     TransDate      DailyLoanCount   DailyLoanValue
    DLSS           06/22/2010     1                       164.00
    DLSS           06/22/2010     1                       192.00
    DLSS           06/22/2010     1                       407.00
    DLSS           06/22/2010     1                       484.00

    I was expecting it to sum all the values like the original T-SQL so any help greatly appreciated.


     

    Friday, July 30, 2010 8:51 PM

Answers

  • Here is the correct LINQ

     

    (from LoanStatistics in context.Loan_CSM7s
    
                             where LoanStatistics.Sourceid.Equals(7)
    
                             && LoanStatistics.LoanType == "DLSS"
    
                             group LoanStatistics by
    
                            new
    
                            {
    
                                TransDate = LoanStatistics.TransactionDate,
    
                                LoanTyp = LoanStatistics.LoanType
    
                            } into grpLoanType
    
                             orderby grpLoanType.Key.TransDate
    
                             select new
    
                             {
    
                                 LoanType = grpLoanType.Key.LoanTyp,
    
                                 TransDate = grpLoanType.Key.TransDate,
    
                                 DailyLoanCount = grpLoanType.Count(),
    
                                 DailyLoanValue = grpLoanType.Sum(x => x.LoanCurrPrincipal)
    
                             });
    I have put in italic the changes I have made.

     

     


    Ali Hamdar (alihamdar.com)
    • Marked as answer by zzpluralza Monday, August 2, 2010 3:55 PM
    Sunday, August 1, 2010 12:59 AM

All replies

  • Here is the correct LINQ

     

    (from LoanStatistics in context.Loan_CSM7s
    
                             where LoanStatistics.Sourceid.Equals(7)
    
                             && LoanStatistics.LoanType == "DLSS"
    
                             group LoanStatistics by
    
                            new
    
                            {
    
                                TransDate = LoanStatistics.TransactionDate,
    
                                LoanTyp = LoanStatistics.LoanType
    
                            } into grpLoanType
    
                             orderby grpLoanType.Key.TransDate
    
                             select new
    
                             {
    
                                 LoanType = grpLoanType.Key.LoanTyp,
    
                                 TransDate = grpLoanType.Key.TransDate,
    
                                 DailyLoanCount = grpLoanType.Count(),
    
                                 DailyLoanValue = grpLoanType.Sum(x => x.LoanCurrPrincipal)
    
                             });
    I have put in italic the changes I have made.

     

     


    Ali Hamdar (alihamdar.com)
    • Marked as answer by zzpluralza Monday, August 2, 2010 3:55 PM
    Sunday, August 1, 2010 12:59 AM
  • DailyLoanValue = grpLoanType.Sum(x => x.LoanCurrPrincipal.Value

    This was great and set me straight on what I was doing wrong. Just an FYI for anyone reading this thread down the road. I had to add .Value to the last line otherwise it errored out of course trying to sum a string field name.

     

    Thank You Ali.

    Monday, August 2, 2010 3:55 PM