private static async Task MapIntoEntity <TEntity>(Dictionary <string, string> matchingData, string idPropertyName, UpdatePropertyOverrider <TEntity> overridingMapper, TEntity entityToUpdate, Dictionary <string, string> excelRow, bool isAutoIncrementingId, RecordMode recordMode) { if (overridingMapper != null) { await overridingMapper.UpdateProperties(entityToUpdate, matchingData, excelRow, recordMode); } else { UpdateProperties(entityToUpdate, matchingData, excelRow, idPropertyName, isAutoIncrementingId); } }
/// <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> /// <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); }