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
});
}
}
