I have the following LINQ query:

    var resultData = from audit in _context.audittable
                  join region in _context.Regions on audit.RegionCode equals region.RegionCode
                  where audit.SearchDate >= loieStartDate && audit.SearchDate <= loieEndDate
                  select new SearchCriteriaAuditDTO()
                  {
                      AuditId = audit.DbLagTestSearchCriteriaAuditId, //this primary key is hidden in the grid
                      SearchDate = audit.SearchDate.ToShortDateString(),//this is Date column just renamed on grid but the grid field is binded to this
                      OverAllAvgLagDays = audit.OverAllAvgLagDays,//binded to grid field just changed the name
                      RegionName = region.RegionName,//this is coming from a reference table which is a foreign key in audit table 
                      CategoryId = 
Convert.ToInt32(audit.CategoryId),//this returns id &is mapped to an enum of categories in backend&then mapped on grid based on id
                  };

This query gives the following table structure:

I want to modify this query to display averages in the grid as follows, where I calculate the average when the Region is “ALL” and the Category appears 3 times (not necessarily consecutively):

Can anyone help me modify the query to include this logic for calculating averages when the region is “ALL” and the category appears 3 times, regardless of whether they are consecutive or not?

There are 2tables (audit& region) in this query.Region is foreign key reference & Category is an enum .