CODEWITHSUNDEEP
linqentity-framework-core
John Kinane
John Kinane

Reputation: 466

LINQ Aggregate results with Many to Many Relationship

I am currently working with this schema enter image description here

This is how my LINQ currently looks

var regionResults = (
                    from p in _context.Projects
                    from pr in p.Regions
                    where (data.RegionId == null || pr.RegionId == data.RegionId)

                    group p by pr.RegionId into g
                    join q in _context.Regions on g.Key equals _context.Regions.First().Id  
                    select new Models.ViewModels.ProjectBreakdownViewModel.Regions
                    {
                        RegionName = q.Name,
                        TotalCount = g.Count(),
                        RejectedCount = g.Count(e => e.SubmissionStatusId == 2),
                        DeniedCount = g.Count(e => e.SubmissionStatusId == 3)
                    });

this is what it is currently producing, albeit incorrect enter image description here

This is what I need it to be...

enter image description here

I know the problem is with this line, essentially

join q in _context.Regions on g.Key equals _context.Regions.First().Id

I don't know how to do this without the use of .First(), there doesn't seem to be a way to do it. I'm close I just don't know how to finish this.

Upvotes: 2

Views: 160

Answers (1)

ocuenca
ocuenca

Reputation: 39326

If you have an collection of ProjectRegions in you Region entity, you can do this:

var result= context.Regions
                   .Where(r=> data.RegionId == null || r.Id == data.RegionId)
                   .Select(r=> new  
                               {
                                 RegionName = r.Name,
                                 TotalCount = r.ProjectRegions.Count(),
                                 RejectedCount = r.ProjectRegions.Count(e => e.Project.SubmissionStatusId == 2),
                                 DeniedCount = r.ProjectRegions.Count(e => e.Project.SubmissionStatusId == 3)
                               });

ProjectRegion entity should have two nav properties, Project and Region, use them to navigate and create the corresponding conditions

Upvotes: 1

Related Questions