async Task <AccessorApi.SiteVariableAmounts> AccessorApi.ISiteVariableAmountsAccessor.GetSiteVariableAmountsAsync(AccessorApi.SiteVariableAmountsFilters filters, int startIndex, int recordCount) { using (var db = new EntityFramework.WaDEContext(Configuration)) { var sw = Stopwatch.StartNew(); var query = db.SiteVariableAmountsFact.AsNoTracking(); if (filters.TimeframeStartDate != null) { query = query.Where(a => a.TimeframeStartNavigation.Date >= filters.TimeframeStartDate); } if (filters.TimeframeEndDate != null) { query = query.Where(a => a.TimeframeEndNavigation.Date <= filters.TimeframeEndDate); } if (!string.IsNullOrWhiteSpace(filters.VariableCv)) { query = query.Where(a => a.VariableSpecific.VariableCv == filters.VariableCv); } if (!string.IsNullOrWhiteSpace(filters.VariableSpecificCv)) { query = query.Where(a => a.VariableSpecific.VariableSpecificCv == filters.VariableSpecificCv); } if (!string.IsNullOrWhiteSpace(filters.BeneficialUseCv)) { query = query.Where(a => a.PrimaryUseCategoryCV == filters.BeneficialUseCv || a.SitesBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.Name == filters.BeneficialUseCv)); } if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCv)) { query = query.Where(a => a.PrimaryBeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv || a.SitesBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv)); } if (!string.IsNullOrWhiteSpace(filters.SiteUuid)) { query = query.Where(a => a.Site.SiteUuid == filters.SiteUuid); } if (!string.IsNullOrWhiteSpace(filters.SiteTypeCv)) { query = query.Where(a => a.Site.SiteTypeCv == filters.SiteTypeCv); } 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))); } if (!string.IsNullOrWhiteSpace(filters.HUC8)) { query = query.Where(a => a.Site.HUC8 == filters.HUC8); } if (!string.IsNullOrWhiteSpace(filters.HUC12)) { query = query.Where(a => a.Site.HUC12 == filters.HUC12); } if (!string.IsNullOrWhiteSpace(filters.County)) { query = query.Where(a => a.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.SiteVariableAmountId) .Skip(startIndex) .Take(recordCount) .ProjectTo <SiteVariableAmountHelper>(Mapping.DtoMapper.Configuration) .ToListAsync(); var siteVariableAmountIds = results.Select(a => a.SiteVariableAmountId).ToList(); var orgIds = results.Select(a => a.OrganizationId).ToHashSet(); var orgsTask = db.OrganizationsDim .Where(a => orgIds.Contains(a.OrganizationId)) .ProjectTo <AccessorApi.SiteVariableAmountsOrganization>(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 beneficialUseTask = db.SitesBridgeBeneficialUsesFact .Where(a => siteVariableAmountIds.Contains(a.SiteVariableAmountId)) .Select(a => new { a.SiteVariableAmountId, a.BeneficialUse }) .ToListAsync(); var beneficialUses = (await beneficialUseTask).Select(a => (a.SiteVariableAmountId, a.BeneficialUse)).ToList(); var waterSources = await waterSourceTask; var variableSpecifics = await variableSpecificTask; var methods = await methodTask; var siteVariableAmountsOrganizations = new List <AccessorApi.SiteVariableAmountsOrganization>(); foreach (var org in await orgsTask) { ProcessSiteVariableAmountsOrganization(org, results, waterSources, variableSpecifics, methods, beneficialUses); siteVariableAmountsOrganizations.Add(org); } sw.Stop(); Logger.LogInformation($"Completed SiteVariableAmounts [{sw.ElapsedMilliseconds } ms]"); return(new AccessorApi.SiteVariableAmounts { TotalSiteVariableAmountsCount = totalCount, Organizations = siteVariableAmountsOrganizations }); } }
async Task<IEnumerable<AccessorApi.SiteVariableAmountsOrganization>> AccessorApi.ISiteVariableAmountsAccessor.GetSiteVariableAmountsAsync(AccessorApi.SiteVariableAmountsFilters filters) { using (var db = new EntityFramework.WaDEContext(Configuration)) { var query = db.SiteVariableAmountsFact.AsNoTracking(); if (filters.TimeframeStartDate != null) { query = query.Where(a => a.TimeframeStartNavigation.Date >= filters.TimeframeStartDate); } if (filters.TimeframeEndDate != null) { query = query.Where(a => a.TimeframeEndNavigation.Date <= filters.TimeframeEndDate); } if (!string.IsNullOrWhiteSpace(filters.VariableCv)) { query = query.Where(a => a.VariableSpecific.VariableCv == filters.VariableCv); } if (!string.IsNullOrWhiteSpace(filters.VariableSpecificCv)) { query = query.Where(a => a.VariableSpecific.VariableSpecificCv == filters.VariableSpecificCv); } if (!string.IsNullOrWhiteSpace(filters.BeneficialUseCv)) { query = query.Where(a => a.SitesBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.BeneficialUseCategory == filters.BeneficialUseCv)); } if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCv)) { query = query.Where(a => a.SitesBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv)); } if (!string.IsNullOrWhiteSpace(filters.SiteUuid)) { query = query.Where(a => a.Site.SiteUuid == filters.SiteUuid); } if (!string.IsNullOrWhiteSpace(filters.SiteTypeCv)) { query = query.Where(a => a.Site.SiteTypeCv == filters.SiteTypeCv); } 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.SiteVariableAmountsOrganization>(Mapping.DtoMapper.Configuration) .ToListAsync(); var allBeneficialUses = results.SelectMany(a => a.BeneficialUses).ToList(); Parallel.ForEach(results.SelectMany(a => a.SiteVariableAmounts).Batch(10000), waterAllocations => { SetBeneficialUses(waterAllocations, allBeneficialUses); }); return results; } }