I have a SQL table which holds a parent-child relationship in it own schema via a ParentId column. There are some other tables (Sale, etc.) linked to it via the LocationId column. I am after grouping data based on the second level parents (Code: xxxxx.xxxxx) and then apply some aggregate function such as SUM and COUNT to its children and itself.

Expectation Result for seeing the total sales for 2 regions(Victoria, South Australia) would be something like:

DisplayName      TotalSale
Victoria         $25,000
South Australia  $30,000

enter image description here


I managed to solve the above situation through the Code column on the table:

var t = await _jobRepository.GetAll().Include(p => p.Applications)
                                           .Include(p => p.Location)
                                           .Include(p => p.InvoiceItem)
                                           .Where(p => p.PublishDate.HasValue)
                                           .Where(p => p.PublishDate.HasValue)
                                           .Where(p => p.PublishDate.Value.Year == Clock.Now.Year && p.PublishDate.Value.Month == Clock.Now.Month)
                                           .Where(p => p.Location.Code.Length >= 11)
                                           .GroupBy(p => p.Location.Code.Substring(0, 11), p => p, (parentLocation, job) => new { parentLocation, job })
                                           .Select(p => new RegionalStatCountryToBeChanged()
                                               RegionName = p.job.Select(q => q.Location).FirstOrDefault(q => q.Code == p.parentLocation).DisplayName,
                                               NumberOfAds = p.job.Count(),
                                               NumberOfApplications = p.job.SelectMany(q => q.Applications).Count(),
                                               Sales = p.job.Select(q => q.InvoiceItem).Sum(q => q.Price)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.