private async Task <List <Dictionary <string, string> > > GetExcelRows(DataMatchesForImport matchingData)
{
if (matchingData.FileStream != null)
{
return(await _excelIoWrapper.GetRows(matchingData.FileStream, matchingData.Sheet));
}
var filePath = Path.Combine(Path.GetTempPath(), matchingData.FileName);
return(await _excelIoWrapper.GetRows(filePath, matchingData.Sheet));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity">The type of EF Entity</typeparam>
/// <typeparam name="TId">Type of the item to use for finding</typeparam>
/// <param name="matchingData">Specification for how to match spreadsheet to entity</param>
/// <param name="finder">A func to look for an existing copy of the entity id. default will use "find". Runs against the DB via EF.</param>
/// <param name="idPropertyName">The unique identifier property. Leave blank unless you are using an overrider to use something other than the real key.</param>
/// <param name="overridingMapper">A custom mapper for mapping between excel columns and an entity. </param>
/// <param name="saveBehavior">Optional configuration to change the save behavior. See ImportSaveBehavior</param>
/// <param name="validator">Optional method to run custom validation on the modified entity before saving</param>
/// <returns></returns>
public async Task <ImportResult> ImportColumnData <TEntity, TId>(DataMatchesForImport matchingData, Func <TId, Expression <Func <TEntity, bool> > > finder = null, string idPropertyName = null, UpdatePropertyOverrider <TEntity> overridingMapper = null, ImportSaveBehavior saveBehavior = null, IEntityValidator <TEntity> validator = null)
where TEntity : class, new()
{
if (saveBehavior == null)
{
saveBehavior = new ImportSaveBehavior();
}
var selctedDict = BuildDictionaryFromSelected(matchingData.Selected);
var keyInfo = GetEntityKeys(typeof(TEntity));
EnsureImportingEntityHasSingleKey(keyInfo);
var pk = keyInfo[0];
Type idType = ((PrimitiveType)pk.TypeUsage.EdmType).ClrEquivalentType;
if (idPropertyName == null)
{
idPropertyName = pk.Name;
}
var isImportingEntityId = selctedDict.ContainsKey(idPropertyName);
var isAutoIncrementingId = IsIdAutoIncrementing(typeof(TEntity));
EnsureNoIdColumnIncludedWhenCreatingAutoIncrementEntites(saveBehavior.RecordMode, isAutoIncrementingId, isImportingEntityId);
var importResult = new ImportResult {
RowErrorDetails = new Dictionary <string, string>()
};
var excelRows = await GetExcelRows(matchingData);
var foundErrors = false;
for (var index = 0; index < excelRows.Count; index++)
{
var excelRow = excelRows[index];
var rowNumber = index + 2; // add 2 to reach the first data row because the first row is a header, excel row numbers start with 1 not 0
TEntity entityToUpdate = null;
try
{
if (ExcelRowIsBlank(excelRow))
{
continue;
}
string idValue = null;
if (isImportingEntityId)
{
var xlsxIdColName = selctedDict[idPropertyName];
var idStringValue = excelRow[xlsxIdColName];
entityToUpdate = await GetMatchedDbObject(finder, idStringValue, idType);
ValidateDbResult(entityToUpdate, saveBehavior.RecordMode, xlsxIdColName, idStringValue);
}
if (entityToUpdate == null)
{
EnsureNoEntityCreationWithIdWhenAutoIncrementIdType(idPropertyName, isAutoIncrementingId, idValue);
entityToUpdate = new TEntity();
_dbContext.Set <TEntity>().Add(entityToUpdate);
}
await MapIntoEntity(selctedDict, idPropertyName, overridingMapper, entityToUpdate, excelRow, isAutoIncrementingId, saveBehavior.RecordMode);
if (validator != null)
{
var errors = validator.GetValidationErrors(entityToUpdate);
if (errors.Any())
{
throw new RowInvalidException(errors);
}
}
else
{
importResult.SuccessCount++;
}
}
catch (RowParseException e)
{
HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
foundErrors = true;
}
catch (RowInvalidException e)
{
HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
foundErrors = true;
}
catch (Exception)
{
HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Cannot be updated - error importing");
foundErrors = true;
}
if (saveBehavior.CommitMode == CommitMode.AnySuccessfulOneAtATime)
{
await _dbContext.SaveChangesAsync();
}
}
if ((saveBehavior.CommitMode == CommitMode.AnySuccessfulAtEndAsBulk) ||
(saveBehavior.CommitMode == CommitMode.CommitAllAtEndIfAllGoodOrRejectAll && !foundErrors))
{
await _dbContext.SaveChangesAsync();
}
return(importResult);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity">The type of EF Entity</typeparam>
/// <param name="matchingData">Specification for how to match spreadsheet to entity</param>
/// <param name="saveBehavior">Optional configuration to change the save behavior. See ImportSaveBehavior</param>
/// <returns></returns>
public async Task <ImportResult> ImportColumnData <TEntity>(DataMatchesForImport matchingData, ImportSaveBehavior saveBehavior = null)
where TEntity : class, new()
{
return(await ImportColumnData <TEntity, string>(matchingData, null, null, null, saveBehavior));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity">The type of EF Entity</typeparam>
/// <typeparam name="TId">Type of the item to use for finding</typeparam>
/// <param name="matchingData">Specification for how to match spreadsheet to entity</param>
/// <param name="finder">A func to look for an existing copy of the entity id. default will use "find". Runs against the DB via EF.</param>
/// <param name="idPropertyName">The unique identifier property. Leave blank unless you are using an overrider to use something other than the real key.</param>
/// <param name="overridingMapper">A custom mapper for mapping between excel columns and an entity. </param>
/// <param name="saveBehavior">Optional configuration to change the save behavior. See ImportSaveBehavior</param>
/// <param name="validator">Optional method to run custom validation on the modified entity before saving</param>
/// <returns></returns>
public async Task <ImportResult> ImportColumnData <TEntity, TId>(DataMatchesForImport matchingData, Func <TId, Expression <Func <TEntity, bool> > > finder = null, string idPropertyName = null, UpdatePropertyOverrider <TEntity> overridingMapper = null, ImportSaveBehavior saveBehavior = null, IEntityValidator <TEntity> validator = null)
where TEntity : class, new()
{
if (saveBehavior == null)
{
saveBehavior = new ImportSaveBehavior();
}
var selctedDict = BuildDictionaryFromSelected(matchingData.Selected);
var keyInfo = GetEntityKeys(typeof(TEntity));
EnsureImportingEntityHasSingleKey(keyInfo);
var pk = keyInfo[0];
var idType = ((PrimitiveType)pk.TypeUsage.EdmType).ClrEquivalentType;
if (idPropertyName == null)
{
idPropertyName = pk.Name;
}
var tableObj = GetTable(typeof(TEntity));
var tableName = $"[{tableObj.Schema}].[{tableObj.Table}]";
var isImportingEntityId = selctedDict.ContainsKey(idPropertyName);
var isAutoIncrementingId = IsIdAutoIncrementing(typeof(TEntity));
EnsureNoIdColumnIncludedWhenCreatingAutoIncrementEntites(saveBehavior.RecordMode, isAutoIncrementingId, isImportingEntityId);
var importResult = new ImportResult {
RowErrorDetails = new Dictionary <string, string>()
};
var filePath = matchingData.FilePath != null ? (Path.GetTempPath() + matchingData.FileName) : matchingData.FileName;
var excelRows = await _excelIoWrapper.GetRows(filePath, matchingData.Sheet);
var foundErrors = false;
if (saveBehavior.RecordMode == RecordMode.CreateOrIgnore && saveBehavior.CommitMode == CommitMode.Bulk)
{
_dbContext.Configuration.AutoDetectChangesEnabled = false;
_dbContext.Configuration.ValidateOnSaveEnabled = false;
}
var genericListType = typeof(List <>).MakeGenericType(idType);
var rowIds = new List <object>();
var existingIds = (IList)Activator.CreateInstance(genericListType);
if (isImportingEntityId)
{
var xlsxIdColName = selctedDict[idPropertyName];
rowIds.AddRange(excelRows.Select(excelRow => excelRow[xlsxIdColName])
.Where(x => !string.IsNullOrEmpty(x)).Select(idStringValue => StringToTypeConverter.Convert(idStringValue, idType)));
if (rowIds.Count > 0)
{
var sqlQuery = $"Select [{idPropertyName}] from {tableName}";
var dbIds = await _dbContext.Database.SqlQuery(idType, sqlQuery).ToListAsync();
var ids = from id in dbIds
join rowId in rowIds on
id equals rowId
select id;
existingIds = ids.ToList();
}
}
Task task = null;
var pendingEntities = new List <TEntity>();
for (var index = 0; index < excelRows.Count; index++)
{
var excelRow = excelRows[index];
var rowNumber = index + 2; // add 2 to reach the first data row because the first row is a header, excel row numbers start with 1 not 0
TEntity entityToUpdate = null;
try
{
if (saveBehavior.checkForEmptyRows && ExcelRowIsBlank(excelRow))
{
continue;
}
if (isImportingEntityId)
{
var xlsxIdColName = selctedDict[idPropertyName];
var idStringValue = excelRow[xlsxIdColName];
var id = rowIds[index];
if (saveBehavior.RecordMode == RecordMode.CreateOrIgnore && (string.IsNullOrEmpty(idStringValue) || existingIds.Contains(id)))
{
continue;
}
if (saveBehavior.RecordMode != RecordMode.CreateOrIgnore)
{
entityToUpdate = await GetMatchedDbObject(finder, idStringValue, idType);
ValidateDbResult(entityToUpdate, saveBehavior.RecordMode, xlsxIdColName, idStringValue);
}
else
{
existingIds.Add(id);
}
}
if (entityToUpdate == null)
{
entityToUpdate = new TEntity();
if (saveBehavior.CommitMode == CommitMode.Bulk)
{
pendingEntities.Add(entityToUpdate);
}
else
{
var xlsxIdColName = selctedDict[idPropertyName];
var idValue = excelRow[xlsxIdColName];
EnsureNoEntityCreationWithIdWhenAutoIncrementIdType(idPropertyName, isAutoIncrementingId, idValue);
_dbContext.Set <TEntity>().Add(entityToUpdate);
}
}
await MapIntoEntity(selctedDict, idPropertyName, overridingMapper, entityToUpdate, excelRow, isAutoIncrementingId, saveBehavior.RecordMode);
if (validator != null)
{
var errors = validator.GetValidationErrors(entityToUpdate);
throw new RowInvalidException(errors);
}
else
{
importResult.SuccessCount++;
}
}
catch (RowParseException e)
{
HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
foundErrors = true;
}
catch (RowInvalidException e)
{
HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
foundErrors = true;
}
catch (Exception)
{
HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Cannot be updated - error importing");
foundErrors = true;
}
if (saveBehavior.CommitMode == CommitMode.AnySuccessfulOneAtATime)
{
await _dbContext.SaveChangesAsync();
}
if (saveBehavior.CommitMode == CommitMode.Bulk && (index % 100 == 0 || index == excelRows.Count - 1))
{
if (task != null)
{
await task;
task = null;
}
_dbContext.Set <TEntity>().AddRange(pendingEntities);
task = _dbContext.BulkSaveChangesAsync(b => b.BatchSize = 50);
pendingEntities.Clear();
}
}
if (task != null)
{
await task;
}
if (pendingEntities.Count > 0)
{
_dbContext.Set <TEntity>().AddRange(pendingEntities);
}
_dbContext.Configuration.AutoDetectChangesEnabled = true;
_dbContext.Configuration.ValidateOnSaveEnabled = true;
if ((saveBehavior.CommitMode == CommitMode.AnySuccessfulAtEndAsBulk) ||
(saveBehavior.CommitMode == CommitMode.CommitAllAtEndIfAllGoodOrRejectAll && !foundErrors) ||
(saveBehavior.CommitMode == CommitMode.Bulk))
{
await _dbContext.BulkSaveChangesAsync(b => b.BatchSize = 50);
}
return(importResult);
}
