private void RunUpdate(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { int counter = 1; var entities = context.Items.AsNoTracking().ToList(); foreach (var entity in entities) { entity.Name = "name Update " + counter++; entity.TimeUpdated = DateTime.Now; } if (isBulkOperation) { context.BulkUpdate(entities); } else { context.Items.UpdateRange(entities); context.SaveChanges(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = context.Items.Count(); Item lastEntity = context.Items.LastOrDefault(); Assert.Equal(entitiesNumber, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name Update " + entitiesNumber, lastEntity.Name); } }
private void InsertAndUpdateWithCompositeKey() { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <UserRole>(); for (int i = 0; i < EntitiesNumber; i++) { entities.Add(new UserRole { UserId = i / 10, RoleId = i % 10, Description = "desc" }); } context.BulkInsert(entities); } using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = context.UserRoles.ToList(); int entitiesCount = entities.Count(); for (int i = 0; i < entitiesCount; i++) { entities[i].Description = "desc updated " + i; } context.BulkUpdate(entities); } using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = context.UserRoles.ToList(); Assert.Equal(EntitiesNumber, entities.Count()); context.BulkDelete(entities); } }
//[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 { Data = "Some data " + i }; entities.Add(entity); } context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument // For testing concurrency conflict (UPDATE changes RowVersion which is TimeStamp column) context.Database.ExecuteSqlRaw("UPDATE dbo.[File] SET Data = 'Some data 1 PRE CHANGE' WHERE [Id] = 1;"); var entitiesToUpdate = entities.Take(10).ToList(); foreach (var entityToUpdate in entitiesToUpdate) { entityToUpdate.Data += " 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 CompositeKeyTest(DbServer dbServer) { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); context.Truncate <UserRole>(); // INSERT var entitiesToInsert = new List <UserRole>(); for (int i = 0; i < EntitiesNumber; i++) { entitiesToInsert.Add(new UserRole { UserId = i / 10, RoleId = i % 10, Description = "desc" }); } context.BulkInsert(entitiesToInsert); // UPDATE var entitiesToUpdate = context.UserRoles.ToList(); int entitiesCount = entitiesToUpdate.Count(); for (int i = 0; i < entitiesCount; i++) { entitiesToUpdate[i].Description = "desc updated " + i; } context.BulkUpdate(entitiesToUpdate); var entitiesToUpsert = new List <UserRole>() { new UserRole { UserId = 1, RoleId = 1 }, new UserRole { UserId = 2, RoleId = 2 }, new UserRole { UserId = 100, RoleId = 10 }, }; // TEST var entities = context.UserRoles.ToList(); Assert.Equal(EntitiesNumber, entities.Count()); context.BulkInsertOrUpdate(entitiesToUpsert, new BulkConfig { PropertiesToInclude = new List <string> { nameof(UserRole.UserId), nameof(UserRole.RoleId) } }); var entitiesFinal = context.UserRoles.ToList(); Assert.Equal(EntitiesNumber + 1, entitiesFinal.Count()); }
private void RunUpdate(bool isBulk, DbServer dbServer) { using (var context = new TestContext(ContextUtil.GetOptions())) { int counter = 1; var entities = context.Items.AsNoTracking().ToList(); foreach (var entity in entities) { entity.Description = "Desc Update " + counter++; entity.Quantity += 1000; // will not be changed since Quantity property is not in config PropertiesToInclude } if (isBulk) { var bulkConfig = new BulkConfig { PropertiesToInclude = new List <string> { nameof(Item.Description) }, UpdateByProperties = dbServer == DbServer.SqlServer ? new List <string> { nameof(Item.Name) } : null, CalculateStats = true }; context.BulkUpdate(entities, bulkConfig); if (dbServer == DbServer.SqlServer) { Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(EntitiesNumber, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberDeleted); } } else { context.Items.UpdateRange(entities); context.SaveChanges(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { //int entitiesCount = ItemsCountQuery(context); int entitiesCount = context.Items.Count(); //Item lastEntity = LastItemQuery(context); Item lastEntity = context.Items.OrderByDescending(a => a.ItemId).FirstOrDefault(); Assert.Equal(EntitiesNumber, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name InsertOrUpdate " + EntitiesNumber, lastEntity.Name); } }
private void RunUpdate(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { int counter = 1; var entities = AllItemsQuery(context).ToList(); // context.Items.AsNoTracking()); foreach (var entity in entities) { entity.Description = "Desc Update " + counter++; entity.Quantity = entity.Quantity + 1000; // will not be changed since Quantity property is not in config PropertiesToInclude } if (isBulkOperation) { context.BulkUpdate( entities, new BulkConfig { PropertiesToInclude = new List <string> { nameof(Item.Description) }, UpdateByProperties = new List <string> { nameof(Item.Name) } } ); } else { context.Items.UpdateRange(entities); context.SaveChanges(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = ItemsCountQuery(context); Item lastEntity = LastItemQuery(context); Assert.Equal(EntitiesNumber, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name InsertOrUpdate " + EntitiesNumber, lastEntity.Name); } }
private void CompositeKeyTest(DbServer dbServer) { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); context.Truncate <UserRole>(); // INSERT var entitiesToInsert = new List <UserRole>(); for (int i = 0; i < EntitiesNumber; i++) { entitiesToInsert.Add(new UserRole { UserId = i / 10, RoleId = i % 10, Description = "desc" }); } context.BulkInsert(entitiesToInsert); // UPDATE var entitiesToUpdate = context.UserRoles.ToList(); int entitiesCount = entitiesToUpdate.Count(); for (int i = 0; i < entitiesCount; i++) { entitiesToUpdate[i].Description = "desc updated " + i; } context.BulkUpdate(entitiesToUpdate); // TEST var entities = context.UserRoles.ToList(); Assert.Equal(EntitiesNumber, entities.Count()); }
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); }
public void InsertTestPostgreSql(DbServer dbServer, bool isBulk) { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); context.Database.ExecuteSqlRaw($@"DELETE FROM ""{nameof(Item)}"""); context.Database.ExecuteSqlRaw($@"ALTER SEQUENCE ""{nameof(Item)}_{nameof(Item.ItemId)}_seq"" RESTART WITH 1"); context.Database.ExecuteSqlRaw($@"DELETE FROM ""{nameof(Box)}"""); context.Database.ExecuteSqlRaw($@"ALTER SEQUENCE ""{nameof(Box)}_{nameof(Box.BoxId)}_seq"" RESTART WITH 1"); context.Database.ExecuteSqlRaw($@"DELETE FROM ""{nameof(UserRole)}"""); var currentTime = DateTime.UtcNow; // default DateTime type: "timestamp with time zone"; DateTime.Now goes with: "timestamp without time zone" var entities = new List <Item>(); for (int i = 1; i <= 2; i++) { var entity = new Item { //ItemId = i, Name = "Name " + i, Description = "info " + i, Quantity = i, Price = 0.1m * i, TimeUpdated = currentTime, }; entities.Add(entity); } var entities2 = new List <Item>(); for (int i = 2; i <= 3; i++) { var entity = new Item { ItemId = i, Name = "Name " + i, Description = "UPDATE " + i, Quantity = i, Price = 0.1m * i, TimeUpdated = currentTime, }; entities2.Add(entity); } var entities3 = new List <Item>(); for (int i = 3; i <= 4; i++) { var entity = new Item { //ItemId = i, Name = "Name " + i, Description = "CHANGE " + i, Quantity = i, Price = 0.1m * i, TimeUpdated = currentTime, }; entities3.Add(entity); } var entities56 = new List <Item>(); for (int i = 5; i <= 6; i++) { var entity = new Item { //ItemId = i, Name = "Name " + i, Description = "CHANGE " + i, Quantity = i, Price = 0.1m * i, TimeUpdated = currentTime, }; entities56.Add(entity); } // INSERT context.BulkInsert(entities); Assert.Equal("info 1", context.Items.Where(a => a.Name == "Name 1").AsNoTracking().FirstOrDefault().Description); Assert.Equal("info 2", context.Items.Where(a => a.Name == "Name 2").AsNoTracking().FirstOrDefault().Description); // UPDATE context.BulkInsertOrUpdate(entities2, new BulkConfig() { NotifyAfter = 1 }, (a) => WriteProgress(a)); Assert.Equal("UPDATE 2", context.Items.Where(a => a.Name == "Name 2").AsNoTracking().FirstOrDefault().Description); Assert.Equal("UPDATE 3", context.Items.Where(a => a.Name == "Name 3").AsNoTracking().FirstOrDefault().Description); var configUpdateBy = new BulkConfig { UpdateByProperties = new List <string> { nameof(Item.Name) } }; configUpdateBy.SetOutputIdentity = true; context.BulkUpdate(entities3, configUpdateBy); Assert.Equal(3, entities3[0].ItemId); // to test Output Assert.Equal(4, entities3[1].ItemId); Assert.Equal("CHANGE 3", context.Items.Where(a => a.Name == "Name 3").AsNoTracking().FirstOrDefault().Description); Assert.Equal("CHANGE 4", context.Items.Where(a => a.Name == "Name 4").AsNoTracking().FirstOrDefault().Description); // Test Multiple KEYS var userRoles = new List <UserRole> { new UserRole { Description = "Info" } }; context.BulkInsertOrUpdate(userRoles); // DELETE context.BulkDelete(new List <Item>() { entities2[1] }, configUpdateBy); // READ var secondEntity = new List <Item>() { new Item { Name = entities[1].Name } }; context.BulkRead(secondEntity, configUpdateBy); Assert.Equal(2, secondEntity.FirstOrDefault().ItemId); Assert.Equal("UPDATE 2", secondEntity.FirstOrDefault().Description); // SAVE CHANGES context.AddRange(entities56); context.BulkSaveChanges(); Assert.Equal(5, entities56[0].ItemId); // BATCH var query = context.Items.AsQueryable().Where(a => a.ItemId <= 1); query.BatchUpdate(new Item { Description = "UPDATE N", Price = 1.5m } /*, updateColumns*/); var queryJoin = context.ItemHistories.Where(p => p.Item.Description == "UPDATE 2"); queryJoin.BatchUpdate(new ItemHistory { Remark = "Rx", }); var query2 = context.Items.AsQueryable().Where(a => a.ItemId > 1 && a.ItemId < 3); query.BatchDelete(); var descriptionsToDelete = new List <string> { "info" }; var query3 = context.Items.Where(a => descriptionsToDelete.Contains(a.Description)); query3.BatchDelete(); // for type 'jsonb' JsonDocument jsonbDoc = JsonDocument.Parse(@"{ ""ModelEL"" : ""Square""}"); var box = new Box { DocumentContent = jsonbDoc, ElementContent = jsonbDoc.RootElement }; context.BulkInsert(new List <Box> { box }); JsonDocument jsonbDoc2 = JsonDocument.Parse(@"{ ""ModelEL"" : ""Circle""}"); var boxQuery = context.Boxes.AsQueryable().Where(a => a.BoxId <= 1); boxQuery.BatchUpdate(new Box { DocumentContent = jsonbDoc2, ElementContent = jsonbDoc2.RootElement }); //var incrementStep = 100; //var suffix = " Concatenated"; //query.BatchUpdate(a => new Item { Name = a.Name + suffix, Quantity = a.Quantity + incrementStep }); // example of BatchUpdate Increment/Decrement value in variable }