/// <summary>
/// Generate a query for the specified 'filter'.
/// </summary>
/// <param name="context"></param>
/// <param name="user"></param>
/// <param name="filter"></param>
/// <returns></returns>
public static IQueryable <Entity.Parcel> GenerateQuery(this PimsContext context, ClaimsPrincipal user, Entity.Models.ParcelFilter filter)
{
filter.ThrowIfNull(nameof(user));
filter.ThrowIfNull(nameof(filter));
// Check if user has the ability to view sensitive properties.
var userAgencies = user.GetAgenciesAsNullable();
var viewSensitive = user.HasPermission(Permissions.SensitiveView);
var isAdmin = user.HasPermission(Permissions.AdminProperties);
// Users may only view sensitive properties if they have the `sensitive-view` claim and belong to the owning agency.
var query = context.Parcels.AsNoTracking();
if (!isAdmin)
{
query = query.Where(p =>
p.IsVisibleToOtherAgencies ||
((!p.IsSensitive || viewSensitive) &&
userAgencies.Contains(p.AgencyId)));
}
if (filter.NELatitude.HasValue && filter.NELongitude.HasValue && filter.SWLatitude.HasValue && filter.SWLongitude.HasValue)
{
var pfactory = new NetTopologySuite.Geometries.GeometryFactory();
var ring = new NetTopologySuite.Geometries.LinearRing(
new[] {
new NetTopologySuite.Geometries.Coordinate(filter.NELongitude.Value, filter.NELatitude.Value),
new NetTopologySuite.Geometries.Coordinate(filter.SWLongitude.Value, filter.NELatitude.Value),
new NetTopologySuite.Geometries.Coordinate(filter.SWLongitude.Value, filter.SWLatitude.Value),
new NetTopologySuite.Geometries.Coordinate(filter.NELongitude.Value, filter.SWLatitude.Value),
new NetTopologySuite.Geometries.Coordinate(filter.NELongitude.Value, filter.NELatitude.Value)
});
var poly = pfactory.CreatePolygon(ring);
poly.SRID = 4326;
query = query.Where(p => poly.Contains(p.Location));
}
if (filter.Agencies?.Any() == true)
{
// Get list of sub-agencies for any agency selected in the filter.
var filterAgencies = filter.Agencies.Select(a => (int?)a);
var agencies = filterAgencies.Concat(context.Agencies.AsNoTracking().Where(a => filterAgencies.Contains(a.Id)).SelectMany(a => a.Children.Select(ac => (int?)ac.Id)).ToArray()).Distinct();
query = query.Where(p => agencies.Contains(p.AgencyId));
}
if (filter.ClassificationId.HasValue)
{
query = query.Where(p => p.ClassificationId == filter.ClassificationId);
}
if (!String.IsNullOrWhiteSpace(filter.ProjectNumber))
{
query = query.Where(p => EF.Functions.Like(p.ProjectNumber, $"{filter.ProjectNumber}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Description))
{
query = query.Where(p => EF.Functions.Like(p.Description, $"%{filter.Description}%"));
}
if (!String.IsNullOrWhiteSpace(filter.AdministrativeArea))
{
query = query.Where(p => EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.AdministrativeArea}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Zoning))
{
query = query.Where(p => EF.Functions.Like(p.Zoning, $"%{filter.Zoning}%"));
}
if (!String.IsNullOrWhiteSpace(filter.ZoningPotential))
{
query = query.Where(p => EF.Functions.Like(p.ZoningPotential, $"%{filter.ZoningPotential}%"));
}
// TODO: Parse the address information by City, Postal, etc.
if (!String.IsNullOrWhiteSpace(filter.Address))
{
query = query.Where(p => EF.Functions.Like(p.Address.Address1, $"%{filter.Address}%") || EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.Address}%"));
}
if (filter.MinLandArea.HasValue)
{
query = query.Where(p => p.LandArea >= filter.MinLandArea);
}
if (filter.MaxLandArea.HasValue)
{
query = query.Where(p => p.LandArea <= filter.MaxLandArea);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinEstimatedValue.HasValue)
{
query = query.Where(p =>
filter.MinEstimatedValue <= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.FiscalKeys.Estimated)
.Max(pe => pe.FiscalYear))
.Value);
}
if (filter.MaxEstimatedValue.HasValue)
{
query = query.Where(p =>
filter.MaxEstimatedValue >= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.FiscalKeys.Estimated)
.Max(pe => pe.FiscalYear))
.Value);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MinAssessedValue <= p.Evaluations
.FirstOrDefault(e => e.Date == context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.MaxAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MaxAssessedValue >= p.Evaluations
.FirstOrDefault(e => e.Date == context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.Sort?.Any() == true)
{
query = query.OrderByProperty(filter.Sort);
}
else
{
query = query.OrderBy(p => p.Id);
}
return(query);
}
/// <summary>
/// Generate a query for the specified 'filter'.
/// </summary>
/// <param name="context"></param>
/// <param name="user"></param>
/// <param name="filter"></param>
/// <returns></returns>
public static IQueryable <Entity.Parcel> GenerateQuery(this PimsContext context, ClaimsPrincipal user, Entity.Models.ParcelFilter filter)
{
filter.ThrowIfNull(nameof(user));
filter.ThrowIfNull(nameof(filter));
// Check if user has the ability to view sensitive properties.
var userAgencies = user.GetAgenciesAsNullable();
var viewSensitive = user.HasPermission(Permissions.SensitiveView);
var isAdmin = user.HasPermission(Permissions.AdminProperties);
// Users may only view sensitive properties if they have the `sensitive-view` claim and belong to the owning agency.
var query = context.Parcels.Include(p => p.Classification)
.Include(p => p.Address)
.Include(p => p.Address.Province)
.Include(p => p.Agency)
.Include(p => p.Agency.Parent)
.Include(p => p.Evaluations)
.Include(p => p.Fiscals).AsNoTracking();
if (!isAdmin)
{
query = query.Where(p =>
p.IsVisibleToOtherAgencies ||
((!p.IsSensitive || viewSensitive) &&
userAgencies.Contains(p.AgencyId)));
}
if (filter.NELatitude.HasValue && filter.NELongitude.HasValue && filter.SWLatitude.HasValue && filter.SWLongitude.HasValue)
{
var poly = new NetTopologySuite.Geometries.Envelope(filter.NELongitude.Value, filter.SWLongitude.Value, filter.NELatitude.Value, filter.SWLatitude.Value).ToPolygon();
query = query.Where(p => poly.Contains(p.Location));
}
if (filter.Agencies?.Any() == true)
{
// Get list of sub-agencies for any agency selected in the filter.
var filterAgencies = filter.Agencies.Select(a => (int?)a);
var agencies = filterAgencies.Concat(context.Agencies.AsNoTracking().Where(a => filterAgencies.Contains(a.Id)).SelectMany(a => a.Children.Select(ac => (int?)ac.Id)).ToArray()).Distinct();
query = query.Where(p => agencies.Contains(p.AgencyId));
}
if (!String.IsNullOrWhiteSpace(filter.PID))
{
var pidValue = filter.PID.Replace("-", "").Trim();
if (Int32.TryParse(pidValue, out int pid))
{
query = query.Include(p => p.Buildings)
.Include(p => p.CreatedBy)
.Include(p => p.UpdatedBy)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Address)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Address.Province)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.BuildingConstructionType)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.BuildingPredominateUse)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.BuildingOccupantType)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Evaluations)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Fiscals)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Classification)
.Include(p => p.Parcels).ThenInclude(pp => pp.Parcel)
.Include(p => p.Subdivisions).ThenInclude(pp => pp.Subdivision)
.Include(p => p.Projects).ThenInclude(pp => pp.Project).ThenInclude(p => p.Workflow).Where(p => p.PID == pid || p.PIN == pid);
}
}
if (!String.IsNullOrWhiteSpace(filter.PIN))
{
var pinValue = filter.PIN.Trim();
if (Int32.TryParse(pinValue, out int pin))
{
query = query.Include(p => p.Buildings)
.Include(p => p.CreatedBy)
.Include(p => p.UpdatedBy)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Address)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Address.Province)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.BuildingConstructionType)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.BuildingPredominateUse)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.BuildingOccupantType)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Evaluations)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Fiscals)
.Include(p => p.Buildings).ThenInclude(pb => pb.Building).ThenInclude(b => b.Classification)
.Include(p => p.Parcels).ThenInclude(pp => pp.Parcel)
.Include(p => p.Subdivisions).ThenInclude(pp => pp.Subdivision)
.Include(p => p.Projects).ThenInclude(pp => pp.Project).ThenInclude(p => p.Workflow).Where(p => p.PIN == pin);
}
}
if (filter.ClassificationId.HasValue)
{
query = query.Where(p => p.ClassificationId == filter.ClassificationId);
}
if (!String.IsNullOrWhiteSpace(filter.ProjectNumber))
{
query = query.Where(p => p.ProjectNumbers.Contains(filter.ProjectNumber));
}
if (!String.IsNullOrWhiteSpace(filter.Description))
{
query = query.Where(p => EF.Functions.Like(p.Description, $"%{filter.Description}%"));
}
if (!String.IsNullOrWhiteSpace(filter.AdministrativeArea))
{
query = query.Where(p => EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.AdministrativeArea}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Zoning))
{
query = query.Where(p => EF.Functions.Like(p.Zoning, $"%{filter.Zoning}%"));
}
if (!String.IsNullOrWhiteSpace(filter.ZoningPotential))
{
query = query.Where(p => EF.Functions.Like(p.ZoningPotential, $"%{filter.ZoningPotential}%"));
}
// TODO: Parse the address information by City, Postal, etc.
if (!String.IsNullOrWhiteSpace(filter.Address))
{
query = query.Where(p => EF.Functions.Like(p.Address.Address1, $"%{filter.Address}%") || EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.Address}%"));
}
if (filter.MinLandArea.HasValue)
{
query = query.Where(p => p.LandArea >= filter.MinLandArea);
}
if (filter.MaxLandArea.HasValue)
{
query = query.Where(p => p.LandArea <= filter.MaxLandArea);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinMarketValue.HasValue)
{
query = query.Where(p =>
filter.MinMarketValue <= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.FiscalKeys.Market)
.Max(pe => pe.FiscalYear))
.Value);
}
if (filter.MaxMarketValue.HasValue)
{
query = query.Where(p =>
filter.MaxMarketValue >= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.FiscalKeys.Market)
.Max(pe => pe.FiscalYear))
.Value);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MinAssessedValue <= p.Evaluations
.FirstOrDefault(e => e.Date == context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.MaxAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MaxAssessedValue >= p.Evaluations
.FirstOrDefault(e => e.Date == context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.Sort?.Any() == true)
{
query = query.OrderByProperty(filter.Sort);
}
else
{
query = query.OrderBy(p => p.Id);
}
return(query);
}
