LINQ Aggregate Results in Entityframework Core

entity-framework-core linq linq-to-sql

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

enter image description here

this is the result set I'm looking for.

1
1
11/30/2018 3:57:08 PM

Accepted Answer

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)
            });   
0
11/30/2018 8:02:30 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow