public static ParcelLayerGDBCommonMappingToParcelStagingColumnDto AsDto(
this ParcelLayerGDBCommonMappingToParcelStagingColumn parcelLayerGDBCommonMappingToParcelStagingColumn)
{
return(new ParcelLayerGDBCommonMappingToParcelStagingColumnDto()
{
ParcelLayerGDBCommonMappingToParcelColumnID = parcelLayerGDBCommonMappingToParcelStagingColumn
.ParcelLayerGDBCommonMappingToParcelColumnID,
ParcelNumber = parcelLayerGDBCommonMappingToParcelStagingColumn.ParcelNumber,
OwnerName = parcelLayerGDBCommonMappingToParcelStagingColumn.OwnerName
});
}
public static ParcelUpdateExpectedResultsDto AddFromFeatureCollection(RioDbContext _dbContext, FeatureCollection featureCollection, string validParcelNumberRegexPattern, string validParcelNumberAsStringForDisplay, WaterYearDto yearChangesToTakeEffect)
{
var commonColumnMappings = ParcelLayerGDBCommonMappingToParcelStagingColumn.GetCommonMappings(_dbContext);
var wktWriter = new WKTWriter();
//Create a datatable to use for bulk copy
var dt = new DataTable();
dt.Columns.Add("ParcelGeometryText", typeof(string));
dt.Columns.Add("ParcelGeometry4326Text", typeof(string));
dt.Columns.Add("OwnerName", typeof(string));
dt.Columns.Add("ParcelNumber", typeof(string));
dt.Columns.Add("HasConflict", typeof(bool));
foreach (var feature in featureCollection)
{
var parcelNumber = feature.Attributes[commonColumnMappings.ParcelNumber].ToString();
if (!Parcel.IsValidParcelNumber(validParcelNumberRegexPattern, parcelNumber))
{
throw new ValidationException(
$"Parcel number found that does not comply to format {validParcelNumberAsStringForDisplay}. Please ensure that that correct column is selected and all Parcel Numbers follow the specified format and try again.");
}
//if it's an exact duplicate, there was some other info that wasn't relevant for our purposes and we can move along
var geomAs4326Text = wktWriter.Write(feature.Geometry.ProjectTo4326());
var ownerName = feature.Attributes[commonColumnMappings.OwnerName].ToString();
if (dt.AsEnumerable().Any(x =>
x["ParcelGeometry4326Text"].ToString() == geomAs4326Text && x["OwnerName"].ToString() == ownerName &&
x["ParcelNumber"].ToString() == parcelNumber))
{
continue;
}
dt.Rows.Add(
wktWriter.Write(feature.Geometry),
geomAs4326Text,
ownerName,
parcelNumber,
false
);
}
var duplicates = dt.AsEnumerable().GroupBy(x => x[3]).Where(y => y.Count() > 1).ToList();
if (duplicates.Any())
{
duplicates.ForEach(x =>
{
var effectedRows = dt.Select("ParcelNumber='" + x.Key + "'");
var firstEffected = effectedRows.First();
if (effectedRows.Any(y => y["ParcelGeometry4326Text"].ToString() != firstEffected["ParcelGeometry4326Text"].ToString()))
{
throw new ValidationException(
$"Parcel number that has more than one geometry associated with it. Please ensure all Parcels have unique geometries and try uploading again.");
}
foreach (var row in effectedRows)
{
row["HasConflict"] = true;
}
});
}
//Make sure staging table is empty before proceeding
DeleteAll(_dbContext);
var rioDbConnectionString = _dbContext.Database.GetDbConnection().ConnectionString;
using var destinationConnection = new SqlConnection(rioDbConnectionString);
destinationConnection.Open();
using var bulkCopy = new SqlBulkCopy(destinationConnection)
{
DestinationTableName = "dbo.ParcelUpdateStaging", EnableStreaming = true
};
//Specify columns so bulk copy knows exactly what it needs to do
bulkCopy.ColumnMappings.Add("ParcelGeometryText", "ParcelGeometryText");
bulkCopy.ColumnMappings.Add("ParcelGeometry4326Text", "ParcelGeometry4326Text");
bulkCopy.ColumnMappings.Add("OwnerName", "OwnerName");
bulkCopy.ColumnMappings.Add("ParcelNumber", "ParcelNumber");
bulkCopy.ColumnMappings.Add("HasConflict", "HasConflict");
bulkCopy.WriteToServer(dt);
_dbContext.Database.ExecuteSqlRaw("EXECUTE dbo.pUpdateParcelUpdateStagingGeometryFromParcelGeometryText");
return(GetExpectedResultsDto(_dbContext, yearChangesToTakeEffect));
}