//[InlineData(DbServer.Sqlite)] // No TimeStamp column type but can be set with DefaultValueSql: "CURRENT_TIMESTAMP" as it is in OnModelCreating() method. private void TimeStampTest(DbServer dbServer) { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); context.Truncate <File>(); var entities = new List <File>(); for (int i = 1; i <= EntitiesNumber; i++) { var entity = new File { Description = "Some data " + i }; entities.Add(entity); } context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument // Test BulkRead var entitiesRead = new List <File> { new File { Description = "Some data 1" }, new File { Description = "Some data 2" } }; context.BulkRead(entitiesRead, new BulkConfig { UpdateByProperties = new List <string> { nameof(File.Description) } }); Assert.Equal(1, entitiesRead.First().FileId); Assert.NotNull(entitiesRead.First().VersionChange); // For testing concurrency conflict (UPDATE changes RowVersion which is TimeStamp column) context.Database.ExecuteSqlRaw("UPDATE dbo.[File] SET Description = 'Some data 1 PRE CHANGE' WHERE [Id] = 1;"); var entitiesToUpdate = entities.Take(10).ToList(); foreach (var entityToUpdate in entitiesToUpdate) { entityToUpdate.Description += " UPDATED"; } using var transaction = context.Database.BeginTransaction(); var bulkConfig = new BulkConfig { SetOutputIdentity = true, DoNotUpdateIfTimeStampChanged = true }; context.BulkUpdate(entitiesToUpdate, bulkConfig); var list = bulkConfig.TimeStampInfo?.EntitiesOutput.Cast <File>().ToList(); Assert.Equal(9, list.Count()); Assert.Equal(1, bulkConfig.TimeStampInfo.NumberOfSkippedForUpdate); if (bulkConfig.TimeStampInfo?.NumberOfSkippedForUpdate > 0) { //Options, based on needs: // 1. rollback entire Update transaction.Rollback(); // 1. rollback entire Update // 2. throw Exception //throw new DbUpdateConcurrencyException() // 3. Update them again // 4. Skip them and leave it unchanged } else { transaction.Commit(); } }
private void OwnedTypesTest(DbServer dbServer) { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); if (dbServer == DbServer.SqlServer) { context.Truncate <ChangeLog>(); context.Database.ExecuteSqlRaw("TRUNCATE TABLE [" + nameof(ChangeLog) + "]"); } else { //context.ChangeLogs.BatchDelete(); // TODO context.BulkDelete(context.ChangeLogs.ToList()); } var entities = new List <ChangeLog>(); for (int i = 1; i <= EntitiesNumber; i++) { entities.Add(new ChangeLog { Description = "Dsc " + i, Audit = new Audit { ChangedBy = "User" + 1, ChangedTime = DateTime.Now, InfoType = InfoType.InfoTypeA }/*, * AuditExtended = new AuditExtended * { * CreatedBy = "UserS" + 1, * Remark = "test", * CreatedTime = DateTime.Now * }, * AuditExtendedSecond = new AuditExtended * { * CreatedBy = "UserS" + 1, * Remark = "sec", * CreatedTime = DateTime.Now * }*/ }); } context.BulkInsert(entities); if (dbServer == DbServer.SqlServer) { context.BulkRead( entities, new BulkConfig { UpdateByProperties = new List <string> { nameof(Item.Description) } } ); Assert.Equal(2, entities[1].ChangeLogId); } // TEST entities[0].Description += " UPD"; entities[0].Audit.InfoType = InfoType.InfoTypeB; context.BulkUpdate(entities); if (dbServer == DbServer.SqlServer) { context.BulkRead(entities); } Assert.Equal("Dsc 1 UPD", entities[0].Description); Assert.Equal(InfoType.InfoTypeB, entities[0].Audit.InfoType); }
private void TablePerTypeInsertTest() { ContextUtil.DbServer = DbServer.SqlServer; using var context = new TestContext(ContextUtil.GetOptions()); context.LogPersonReports.Add(new LogPersonReport { }); // used for initial add so that after RESEED it starts from 1, not 0 context.SaveChanges(); context.Truncate <LogPersonReport>(); context.Database.ExecuteSqlRaw($"DELETE FROM {nameof(Log)}"); context.Database.ExecuteSqlRaw($"DBCC CHECKIDENT('{nameof(Log)}', RESEED, 0);"); int nextLogId = GetLastRowId(context, tableName: nameof(Log)); int numberOfNewToInsert = 1000; var entities = new List <LogPersonReport>(); for (int i = 1; i <= numberOfNewToInsert; i++) { nextLogId++; // OPTION 1. var entity = new LogPersonReport { LogId = nextLogId, // OPTION 1. PersonId = (i % 22), RegBy = 15, CreatedDate = DateTime.Now, ReportId = (i % 22) * 10, LogPersonReportTypeId = 4, }; entities.Add(entity); } var bulkConfigBase = new BulkConfig { SqlBulkCopyOptions = Microsoft.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, // OPTION 1. - to ensure insert order is kept the same since SqlBulkCopy does not guarantee it. CustomDestinationTableName = nameof(Log), PropertiesToInclude = new List <string> { nameof(LogPersonReport.LogId), nameof(LogPersonReport.PersonId), nameof(LogPersonReport.RegBy), nameof(LogPersonReport.CreatedDate) } }; var bulkConfig = new BulkConfig { PropertiesToInclude = new List <string> { nameof(LogPersonReport.LogId), nameof(LogPersonReport.ReportId), nameof(LogPersonReport.LogPersonReportTypeId) } }; context.BulkInsert(entities, bulkConfigBase, type: typeof(Log)); // to base 'Log' table //foreach(var entity in entities) { // OPTION 2. Could be set here if Id of base table Log was set by Db (when Op.2. used 'Option 1.' have to be commented out) // entity.LogId = ++nextLogId; //} context.BulkInsert(entities, bulkConfig); // to 'LogPersonReport' table Assert.Equal(nextLogId, context.LogPersonReports.OrderByDescending(a => a.LogId).FirstOrDefault().LogId); }
private void RunInsert(bool isBulkOperation, bool insertTo2Tables = false) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); var subEntities = new List <ItemHistory>(); for (int i = 1; i < entitiesNumber; i++) { entities.Add(new Item { ItemId = isBulkOperation ? i : 0, Name = "name " + i, Description = "info " + Guid.NewGuid().ToString().Substring(0, 3), Quantity = i % 10, Price = i / (i % 5 + 1), TimeUpdated = DateTime.Now }); } if (insertTo2Tables) { foreach (var entity in entities) { subEntities.Add(new ItemHistory { ItemHistoryId = SeqGuid.Create(), ItemId = entity.ItemId, Remark = "some more info" }); } } if (isBulkOperation) { if (!insertTo2Tables) { context.BulkInsert(entities); } else { using (var transaction = context.Database.BeginTransaction()) { context.BulkInsert( entities, new BulkConfig { PreserveInsertOrder = true, SetOutputIdentity = true, BatchSize = 4000 , UseTempDB = true }, (a) => WriteProgress(a) ); context.BulkInsert(subEntities); transaction.Commit(); } } } else { context.Items.AddRange(entities); if (insertTo2Tables) { context.ItemHistories.AddRange(subEntities); } context.SaveChanges(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = context.Items.Count(); Item lastEntity = context.Items.LastOrDefault(); Assert.Equal(entitiesNumber - 1, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name " + (entitiesNumber - 1), lastEntity.Name); } }