async Task <AccessorApi.AggregatedAmounts> AccessorApi.IAggregatedAmountsAccessor.GetAggregatedAmountsAsync(AccessorApi.AggregatedAmountsFilters filters, int startIndex, int recordCount)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var sw = Stopwatch.StartNew();
var query = db.AggregatedAmountsFact.AsNoTracking();
if (filters.StartDate != null)
{
query = query.Where(a => a.TimeframeStart.Date >= filters.StartDate);
}
if (filters.EndDate != null)
{
query = query.Where(a => a.TimeframeEnd.Date <= filters.EndDate);
}
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.BeneficialUse))
{
query = query.Where(a => a.PrimaryBeneficialUse.Name == filters.BeneficialUse || a.AggBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.Name == filters.BeneficialUse));
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitUUID))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitUuid == filters.ReportingUnitUUID);
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitTypeCV))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitTypeCv == filters.ReportingUnitTypeCV);
}
if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCV))
{
query = query.Where(a => a.PrimaryBeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCV || a.AggBridgeBeneficialUsesFact.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.ReportingUnit.Geometry != null && a.ReportingUnit.Geometry.Intersects(shape));
}
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.AggregatedAmountId)
.Skip(startIndex)
.Take(recordCount)
.ProjectTo <AggregatedHelper>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var aggregatedIds = results.Select(a => a.AggregatedAmountId).ToList();
var beneficialUseTask = db.AggBridgeBeneficialUsesFact
.Where(a => aggregatedIds.Contains(a.AggregatedAmountId))
.Select(a => new { a.AggregatedAmountId, a.BeneficialUse })
.ToListAsync();
var orgIds = results.Select(a => a.OrganizationId).ToHashSet();
var orgsTask = db.OrganizationsDim
.Where(a => orgIds.Contains(a.OrganizationId))
.ProjectTo <AccessorApi.AggregatedAmountsOrganization>(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 reportingUnitIds = results.Select(a => a.ReportingUnitId).ToHashSet();
var reportingUnitsTask = db.WaterSourcesDim
.Where(a => waterSourceIds.Contains(a.WaterSourceId))
.ProjectTo <AccessorApi.ReportingUnit>(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 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 beneficialUses = (await beneficialUseTask).Select(a => (a.AggregatedAmountId, a.BeneficialUse)).ToList();
var waterSources = await waterSourceTask;
var variableSpecifics = await variableSpecificTask;
var methods = await methodTask;
var reportingUnits = await reportingUnitsTask;
var waterAllocationOrganizations = new List <AccessorApi.AggregatedAmountsOrganization>();
foreach (var org in await orgsTask)
{
ProcessAggregatedAmountsOrganization(org, results, waterSources, variableSpecifics, reportingUnits, methods, beneficialUses);
waterAllocationOrganizations.Add(org);
}
sw.Stop();
Logger.LogInformation($"Completed AggregatedAmounts [{sw.ElapsedMilliseconds } ms]");
return(new AccessorApi.AggregatedAmounts
{
TotalAggregatedAmountsCount = totalCount,
Organizations = waterAllocationOrganizations
});
}
}
async Task <IEnumerable <AccessorApi.AggregatedAmountsOrganization> > AccessorApi.IAggregatedAmountsAccessor.GetAggregatedAmountsAsync(AccessorApi.AggregatedAmountsFilters filters)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var query = db.AggregatedAmountsFact.AsNoTracking();
if (filters.StartDate != null)
{
query = query.Where(a => a.TimeframeStart.Date >= filters.StartDate);
}
if (filters.EndDate != null)
{
query = query.Where(a => a.TimeframeEnd.Date <= filters.EndDate);
}
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.BeneficialUse))
{
query = query.Where(a => a.BeneficialUse.BeneficialUseCategory == filters.BeneficialUse || a.AggBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.BeneficialUseCategory == filters.BeneficialUse));
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitUUID))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitUuid == filters.ReportingUnitUUID);
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitTypeCV))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitTypeCv == filters.ReportingUnitTypeCV);
}
if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCV))
{
query = query.Where(a => a.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCV || a.AggBridgeBeneficialUsesFact.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.ReportingUnit.Geometry != null && a.ReportingUnit.Geometry.Intersects(shape));
}
var results = await query
.GroupBy(a => a.Organization)
.ProjectTo <AccessorApi.AggregatedAmountsOrganization>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var allBeneficialUses = results.SelectMany(a => a.BeneficialUses).ToList();
Parallel.ForEach(results.SelectMany(a => a.AggregatedAmounts).Batch(10000), aggAmounts =>
{
SetBeneficialUses(aggAmounts, allBeneficialUses);
});
return(results);
}
}