LINQ Aggregate Results in Entityframework Core

Question

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

this is the result set I'm looking for.

How is the relationship between Agency and Project? Do they have navigation properties (on C# side)? — amiry jd, Nov 29 '18 at 22:10

score 1 · Answer 1 · answered Nov 29 '18 at 22:16

1

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

answered Nov 29 '18 at 22:16

amiry jd

27,021
30
116
215

data is an incoming object into the method with the params I need, AgencyId being one of them. – John Kinane Nov 30 '18 at 00:10
In this case "a" refers to Agencies. What I'm looking for is way to account for the join, "p" in this case. I don't know how to reference it in the select new {} – John Kinane Nov 30 '18 at 13:49
@JohnKinane I didn't get it ): what property of `p` you want to be in `select new`? – amiry jd Nov 30 '18 at 15:12
if it were possible I'd want what you have above by referencing "p". I need to count the number of totalSubmissions, rejectedSubmissions, deniedSubmissions from the Project table using AgencyId as a filter while naming what those Agencies are in the agency column. – John Kinane Nov 30 '18 at 15:23
also, my linq above does give me the counts, I really just need to find a way to get the Agency Name from the agency table. – John Kinane Nov 30 '18 at 16:05

John Kinane · Accepted Answer · 2018-11-30T20:02:30.897

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

LINQ Aggregate Results in Entityframework Core

2 Answers2