CODEWITHSUNDEEP
entity-framework
Neil Thompson
Neil Thompson

Reputation: 6425

GroupBy SQL to EF Lambda syntax

I'm currently defeated in my attempts to map the following sql to an EF lambda style query:

sql:

SELECT        ResellerId, Name, Month, SUM(MonthTotal) AS MonthTotal
FROM            dbo.View_ResellerYearMonthBase
WHERE        (Year = 2015) AND (EventId > 0) AND (ValidationResponse IS NOT NULL)
GROUP BY ResellerId, Month, Name

I've tried

public JsonResult GetResellerAnnualReportData(int year, bool includeUnValidated, bool includeUnbooked)
{
    var qry = _reportsDal.View_ResellerYearMonthBase.AsQueryable();
    qry = qry.Where(x => x.Year == year);

    if (!includeUnValidated) { qry = qry.Where(x => x.ValidationResponse.Length > 0); }

    if (!includeUnbooked) { qry = qry.Where(x => x.EventId > 0); }

   qry = qry.GroupBy(x => new { x.ResellerId, x.Month, x.Name }).Select(y => new ResellerAnnualReportDto
            {
                ResellerId = y.Key.ResellerId,
                Month = y.Key.Month.Value,
                Name = y.Key.Name,
                SumMonthTotal = y.Sum(z => z.MonthTotal.Value)
            });

    throw new NotImplementedException();//keep the compiler happy for now
}

How should I go about achieving the SQL Query with the function parameters (year, includeUnValidated etc)

Upvotes: 1

Views: 71

Answers (2)

Alexander Derck
Alexander Derck

Reputation: 14498

.GroupBy(key => new { key.ResellerId, key.Month, key.Name},
         el => el.MonthTotal,
         (key, el) => new ResellerAnnualReportDto
         {
            ResellerId = key.ResellerId,
            Month = key.Month,
            Name = key.Name,
            MonthTotal = el.Sum(s => s.MonthTotal)
         }).ToList();

This uses the overload with keyselector, elementselector and resultselector. This way you avoid making the IGrouping<key,value> and get the results you want immediately. Couldn't test though.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Here is how to do this:

var result = qry.GroupBy(x => new { x.ResellerId, x.Month, x.Name }).
                 Select(y => new  { 
                                      y.Key.ResellerId, 
                                      y.Key.Month, 
                                      y.Key.Name, 
                                      SumMonthTotal = y.Sum(z => z.MonthTotal) 
                                  }).ToList();

Upvotes: 0

Related Questions