LINQ Aggregate results with Many to Many Relationship

entity-framework-core linq

Question

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.

1
2
12/6/2018 8:33:55 PM

Accepted Answer

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

1
12/6/2018 9:58:46 PM


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