CODEWITHSUNDEEP
asp.netentity-frameworklinqgroup-by
hphp
hphp

Reputation: 2272

ASP NET CORE Entity Framework Select with GroupBy Id

I build a service to call query like this

select quotaId, sum(quota)
From leaveQuota
group by quotaId

I getting problem in sum and group. How to do it in Entity Framework i tried this

public async Task<List<LeaveTypeModel>> GetLeaveQuota(string employeeId)
{
  var leaveQuota = await DB.EmployeeLeaveQuota
    .Where(Q => Q.EmployeeId == employeeId && Q.ValidUntil >= DateTime.UtcNow)
    .Select(Q => new LeaveTypeModel
    {
        EmployeeLeaveQuotaTypeId = Q.EmployeeLeaveQuotaTypeId,
        QuotaDays = Q.QuotaDays
    })
    .ToListAsync();

    return leaveQuota;
  }

my model

public class LeaveTypeModel
{
    public int EmployeeLeaveQuotaTypeId { set; get; }
    public int QuotaDays { set; get; }
}

if i use .GroupBy i cant return it as my model and i have no idea to sum my quotadays column

i know it is a basic query, but i'm not familiar yet with entity framework and asp.net core to return its data

Upvotes: 4

Views: 5812

Answers (1)

Slava Utesinov
Slava Utesinov

Reputation: 13498

var leaveQuota = await DB.EmployeeLeaveQuota
    .Where(Q => Q.EmployeeId == employeeId && Q.ValidUntil >= DateTime.UtcNow)
    .GroupBy(x => x.EmployeeLeaveQuotaTypeId).Select(x => 
    new LeaveTypeModel
    {
        EmployeeLeaveQuotaTypeId = x.Key,
        QuotaDays = x.Sum(y => y.QuotaDays)
    }).ToListAsync();

Upvotes: 7

Related Questions