CODEWITHSUNDEEP
linqlinq-to-sqlentity-framework-core
John Kinane
John Kinane

Reputation: 466

LINQ Aggregate Results in Entityframework Core

I am trying to recreate this sql into linq.

select a.Name Agency, 
      COUNT(CASE when p.AssignedAgencyId = a.Id then 1 end) Submissions,
      COUNT(CASE when p.AssignedAgencyId = a.Id AND p.SubmissionStatusId= 2 then 1 end) Rejected,
      COUNT(CASE when p.AssignedAgencyId = a.Id AND p.SubmissionStatusId= 3 then 1 end) Denied
FROM Agencies a
join projects p on p.AssignedAgencyId = a.Id
Group By  a.Name

this is what I've come up with but I don't understand how to get a value from a subquery in this way

var agencyResults = (
                    from a in _context.Agencies
                    join p in _context.Projects on a.Id equals p.AssignedAgencyId
                    where (data.AgencyId == null || a.Id == data.AgencyId)
                    group p by p.AssignedAgencyId into g
                    select new 
                    {
                        AgencyName = (from aa in _context.Agencies
                                     where (aa.Id == data.AgencyId)
                                     select aa),
                        TotalCount = g.Count(),
                        RejectedCount = g.Count(e => e.SubmissionStatusId == 2),
                        DeniedCount = g.Count(e => e.SubmissionStatusId == 3)
                    });

enter image description here

this is the result set I'm looking for.

Upvotes: 1

Views: 1217

Answers (2)

John Kinane
John Kinane

Reputation: 466

This was solution. Props to https://stackoverflow.com/a/9173783/823520 for the push I needed.

     var agencyResults = (
            from p in _context.Projects
            where (data.AgencyId == null || p.AssignedAgencyId == data.AgencyId)
            group p by p.AssignedAgencyId into g
            join a in _context.Agencies on g.FirstOrDefault().AssignedAgencyId equals a.Id
            select new
            {
                AgencyName = a.Name,
                TotalCount = g.Count(),
                RejectedCount = g.Count(e => e.SubmissionStatusId == 2),
                DeniedCount = g.Count(e => e.SubmissionStatusId == 3)
            });

Upvotes: 0

amiry jd
amiry jd

Reputation: 27585

I'm not sure what you are looking for. But 1. reading the SQL statement, 2. assuming you have full model in C# side and Entities have proper navigation properties, the SQL can be simplified in this LINQ:

var agencyResults = (
    from a in _context.Agencies
    where (data.AgencyId == null || a.Id == data.AgencyId)
    select new {
        Name = a.Name,
        Submissions = a.Projects.Count(),
        Rejected = a.Projects.Count(e => e.SubmissionStatusId == 2),
        DeniedCount = a.Count(e => e.SubmissionStatusId == 3)
    }).ToList();

P.S. Don't know what the data is and what is it for. I just put it there regarding the question's snippet (in the SQL snippet, I can't find it).

Upvotes: 1

Related Questions