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