async Task <IEnumerable <AccessorApi.WaterAllocationOrganization> > AccessorApi.IWaterAllocationAccessor.GetSiteAllocationAmountsAsync(AccessorApi.SiteAllocationAmountsFilters filters) { using (var db = new EntityFramework.WaDEContext(Configuration)) { var query = db.AllocationAmountsFact .AsNoTracking(); if (filters.StartPriorityDate != null) { query = query.Where(a => a.AllocationPriorityDateNavigation.Date >= filters.StartPriorityDate); } if (filters.EndPriorityDate != null) { query = query.Where(a => a.AllocationPriorityDateNavigation.Date <= filters.EndPriorityDate); } if (!string.IsNullOrWhiteSpace(filters.SiteUuid)) { query = query.Where(a => a.Site.SiteUuid == filters.SiteUuid); } if (!string.IsNullOrWhiteSpace(filters.BeneficialUseCv)) { query = query.Where(a => a.PrimaryBeneficialUse.BeneficialUseCategory == filters.BeneficialUseCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.BeneficialUseCategory == filters.BeneficialUseCv)); } if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCv)) { query = query.Where(a => a.PrimaryBeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv)); } if (!string.IsNullOrWhiteSpace(filters.Geometry)) { var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326); WKTReader reader = new WKTReader(geometryFactory); var shape = reader.Read(filters.Geometry); query = query.Where(a => (a.Site.Geometry != null && a.Site.Geometry.Intersects(shape)) || (a.Site.SitePoint != null && a.Site.SitePoint.Intersects(shape))); } var results = await query .GroupBy(a => a.Organization) .ProjectTo <AccessorApi.WaterAllocationOrganization>(Mapping.DtoMapper.Configuration) .ToListAsync(); var allBeneficialUses = results.SelectMany(a => a.BeneficialUses).ToList(); Parallel.ForEach(results.SelectMany(a => a.WaterAllocations).Batch(10000), waterAllocations => { SetBeneficialUses(waterAllocations, allBeneficialUses); }); return(results); } }
async Task <AccessorApi.WaterAllocations> AccessorApi.IWaterAllocationAccessor.GetSiteAllocationAmountsAsync(AccessorApi.SiteAllocationAmountsFilters filters, int startIndex, int recordCount) { using (var db = new EntityFramework.WaDEContext(Configuration)) { var sw = Stopwatch.StartNew(); var query = db.AllocationAmountsFact.AsNoTracking(); if (filters.StartPriorityDate != null) { query = query.Where(a => a.AllocationPriorityDateNavigation.Date >= filters.StartPriorityDate); } if (filters.EndPriorityDate != null) { query = query.Where(a => a.AllocationPriorityDateNavigation.Date <= filters.EndPriorityDate); } if (!string.IsNullOrWhiteSpace(filters.SiteUuid)) { query = query.Where(a => a.AllocationBridgeSitesFact.Any(s => s.Site.SiteUuid == filters.SiteUuid)); } if (!string.IsNullOrWhiteSpace(filters.BeneficialUseCv)) { query = query.Where(a => a.PrimaryUseCategoryCV == filters.BeneficialUseCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUseCV == filters.BeneficialUseCv)); } if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCv)) { query = query.Where(a => a.PrimaryBeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv)); } if (!string.IsNullOrWhiteSpace(filters.Geometry)) { var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326); WKTReader reader = new WKTReader(geometryFactory); var shape = reader.Read(filters.Geometry); query = query.Where( a => a.AllocationBridgeSitesFact.Any(site => site.Site.Geometry != null && site.Site.Geometry.Intersects(shape)) || a.AllocationBridgeSitesFact.Any(site => site.Site.SitePoint != null && site.Site.SitePoint.Intersects(shape))); } if (!string.IsNullOrWhiteSpace(filters.HUC8)) { query = query.Where(a => a.AllocationBridgeSitesFact.Any(b => b.Site.HUC8 == filters.HUC8)); } if (!string.IsNullOrWhiteSpace(filters.HUC12)) { query = query.Where(a => a.AllocationBridgeSitesFact.Any(b => b.Site.HUC12 == filters.HUC12)); } if (!string.IsNullOrWhiteSpace(filters.County)) { query = query.Where(a => a.AllocationBridgeSitesFact.Any(b => b.Site.County == filters.County)); } if (!string.IsNullOrWhiteSpace(filters.State)) { query = query.Where(a => a.Organization.State == filters.State); } var totalCount = query.Count(); var results = await query .OrderBy(a => a.AllocationAmountId) .Skip(startIndex) .Take(recordCount) .ProjectTo <AllocationHelper>(Mapping.DtoMapper.Configuration) .ToListAsync(); var allocationIds = results.Select(a => a.AllocationAmountId).ToList(); var sitesTask = db.AllocationBridgeSitesFact .Where(a => allocationIds.Contains(a.AllocationAmountId)) .Select(a => new { a.AllocationAmountId, a.Site }) .ToListAsync(); var beneficialUseTask = db.AllocationBridgeBeneficialUsesFact .Where(a => allocationIds.Contains(a.AllocationAmountId)) .Select(a => new { a.AllocationAmountId, a.BeneficialUse }) .ToListAsync(); var orgIds = results.Select(a => a.OrganizationId).ToHashSet(); var orgsTask = db.OrganizationsDim .Where(a => orgIds.Contains(a.OrganizationId)) .ProjectTo <AccessorApi.WaterAllocationOrganization>(Mapping.DtoMapper.Configuration) .ToListAsync(); var waterSourceIds = results.Select(a => a.WaterSourceId).ToHashSet(); var waterSourceTask = db.WaterSourcesDim .Where(a => waterSourceIds.Contains(a.WaterSourceId)) .ProjectTo <AccessorApi.WaterSource>(Mapping.DtoMapper.Configuration) .ToListAsync(); var variableSpecificIds = results.Select(a => a.VariableSpecificId).ToHashSet(); var variableSpecificTask = db.VariablesDim .Where(a => variableSpecificIds.Contains(a.VariableSpecificId)) .ProjectTo <AccessorApi.VariableSpecific>(Mapping.DtoMapper.Configuration) .ToListAsync(); var methodIds = results.Select(a => a.MethodId).ToHashSet(); var methodTask = db.MethodsDim .Where(a => methodIds.Contains(a.MethodId)) .ProjectTo <AccessorApi.Method>(Mapping.DtoMapper.Configuration) .ToListAsync(); var sites = (await sitesTask).Select(a => (a.AllocationAmountId, a.Site)).ToList(); var beneficialUses = (await beneficialUseTask).Select(a => (a.AllocationAmountId, a.BeneficialUse)).ToList(); var waterSources = await waterSourceTask; var variableSpecifics = await variableSpecificTask; var methods = await methodTask; var waterAllocationOrganizations = new List <AccessorApi.WaterAllocationOrganization>(); foreach (var org in await orgsTask) { ProcessWaterAllocationOrganization(org, results, waterSources, variableSpecifics, methods, beneficialUses, sites); waterAllocationOrganizations.Add(org); } sw.Stop(); Logger.LogInformation($"Completed WaterAllocation [{sw.ElapsedMilliseconds } ms]"); return(new AccessorApi.WaterAllocations { TotalWaterAllocationsCount = totalCount, Organizations = waterAllocationOrganizations }); } }