public void BatchConverterTest(DbServer dbServer) { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); context.Truncate <Info>(); var currentDate = DateTime.Today; var entity = new Info { Message = "name A", DateTimeOff = currentDate }; context.Infos.Add(entity); context.SaveChanges(); var updateTo = new Info { Message = "name B Updated" }; context.Infos.BatchUpdate(updateTo); using var contextRead = new TestContext(ContextUtil.GetOptions()); Assert.Equal(currentDate, contextRead.Infos.First().DateTimeOff); }
private void InsertWithDiscriminatorShadow() { using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesNumber = 1000; var entities = new List <Student>(); for (int i = 1; i <= entitiesNumber; i++) { entities.Add(new Student { Name = "name " + i, Subject = "Math" }); } context.Students.AddRange(entities); // adding to Context so that Shadow property 'Discriminator' gets set context.BulkInsert(entities); } using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = context.Students.ToList(); Assert.Equal(1000, entities.Count()); context.BulkDelete(entities); } }
private void InsertWithDbComputedColumnsAndOutput() { using (var context = new TestContext(ContextUtil.GetOptions())) { context.BulkDelete(context.Documents.ToList()); var entities = new List <Document>(); for (int i = 0; i < EntitiesNumber; i++) { entities.Add(new Document { Content = "Some data " + i, }); } //context.BulkInsert(entities, new BulkConfig { SetOutputIdentity = true }); context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument } using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = context.Documents.ToList(); Assert.Equal(EntitiesNumber, entities.Count()); } }
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()); }
public async Task BatchTestAsync(DbServer dbServer) { ContextUtil.DbServer = dbServer; await RunDeleteAllAsync(dbServer); await RunInsertAsync(); await RunBatchUpdateAsync(dbServer); int deletedEntities = 1; if (dbServer == DbServer.SqlServer) { deletedEntities = await RunTopBatchDeleteAsync(); } await RunBatchDeleteAsync(); await UpdateSettingAsync(SettingsEnum.Sett1, "Val1UPDATE"); using var context = new TestContext(ContextUtil.GetOptions()); var firstItem = (await context.Items.ToListAsync()).First(); var lastItem = (await context.Items.ToListAsync()).Last(); Assert.Equal(1, deletedEntities); Assert.Equal(500, lastItem.ItemId); Assert.Equal("Updated", lastItem.Description); Assert.Null(lastItem.Price); Assert.StartsWith("name ", lastItem.Name); Assert.EndsWith(" Concatenated", lastItem.Name); if (dbServer == DbServer.SqlServer) { Assert.EndsWith(" TOP(1)", firstItem.Name); } }
private async Task RunInsertAsync() { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); for (int i = 1; i <= EntitiesNumber; i++) { var entity = new Item { Name = "name " + i, Description = "info " + Guid.NewGuid().ToString().Substring(0, 3), Quantity = i % 10, Price = i / (i % 5 + 1), TimeUpdated = DateTime.Now, ItemHistories = new List <ItemHistory>() }; entities.Add(entity); } await context.Items.AddRangeAsync(entities); await context.SaveChangesAsync(); } }
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. 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); }
//[InlineData(DbServer.Sqlite)] Not supported private void ShadowFKPropertiesTest(DbServer dbServer) // with Foreign Key as Shadow Property { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); if (dbServer == DbServer.SQLServer) { context.Truncate <ItemLink>(); context.Database.ExecuteSqlRaw("TRUNCATE TABLE [" + nameof(ItemLink) + "]"); } else { //context.ChangeLogs.BatchDelete(); // TODO context.BulkDelete(context.ItemLinks.ToList()); } //context.BulkDelete(context.Items.ToList()); // On table with FK Truncate does not work if (context.Items.Count() == 0) { for (int i = 1; i <= 10; ++i) { var entity = new Item { ItemId = 0, Name = "name " + i, Description = "info " + Guid.NewGuid().ToString().Substring(0, 3), Quantity = i % 10, Price = i / (i % 5 + 1), TimeUpdated = DateTime.Now, ItemHistories = new List <ItemHistory>() }; context.Items.Add(entity); } context.SaveChanges(); } var items = context.Items.ToList(); var entities = new List <ItemLink>(); for (int i = 0; i < EntitiesNumber; i++) { entities.Add(new ItemLink { ItemLinkId = 0, Item = items[i % items.Count] }); } context.BulkInsert(entities); if (dbServer == DbServer.SQLServer) { List <ItemLink> links = context.ItemLinks.ToList(); Assert.True(links.Count() > 0, "ItemLink row count"); foreach (var link in links) { Assert.NotNull(link.Item); } } context.Truncate <ItemLink>(); }
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); }
//[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 RunInsert(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); var subEntities = new List <ItemHistory>(); for (int i = 1; i < EntitiesNumber; i++) { var entity = 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, ItemHistories = new List <ItemHistory>() }; var subEntity1 = new ItemHistory { ItemHistoryId = SeqGuid.Create(), Remark = $"some more info {i}.1" }; var subEntity2 = new ItemHistory { ItemHistoryId = SeqGuid.Create(), Remark = $"some more info {i}.2" }; entity.ItemHistories.Add(subEntity1); entity.ItemHistories.Add(subEntity2); entities.Add(entity); } if (isBulkOperation) { using (var transaction = context.Database.BeginTransaction()) { context.BulkInsert( entities, new BulkConfig { PreserveInsertOrder = true, SetOutputIdentity = true, BatchSize = 4000, UseTempDB = true }, (a) => WriteProgress(a) ); foreach (var entity in entities) { foreach (var subEntity in entity.ItemHistories) { subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB } subEntities.AddRange(entity.ItemHistories); } context.BulkInsert(subEntities); transaction.Commit(); } } else { context.Items.AddRange(entities); context.SaveChanges(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = ItemsCountQuery(context); Item lastEntity = LastItemQuery(context); Assert.Equal(EntitiesNumber - 1, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name); } }
private void RunInsert(bool isBulk) { using var context = new TestContext(ContextUtil.GetOptions()); var entities = new List <Item>(); var subEntities = new List <ItemHistory>(); for (int i = 1, j = -(EntitiesNumber - 1); i < EntitiesNumber; i++, j++) { var entity = new Item { ItemId = 0, //isBulk ? j : 0, // no longer used since order(Identity temporary filled with negative values from -N to -1) is set automaticaly with default config PreserveInsertOrder=TRUE Name = "name " + i, Description = "info " + Guid.NewGuid().ToString().Substring(0, 3), Quantity = i % 10, Price = i / (i % 5 + 1), TimeUpdated = DateTime.Now, ItemHistories = new List <ItemHistory>() }; var subEntity1 = new ItemHistory { ItemHistoryId = SeqGuid.Create(), Remark = $"some more info {i}.1" }; var subEntity2 = new ItemHistory { ItemHistoryId = SeqGuid.Create(), Remark = $"some more info {i}.2" }; entity.ItemHistories.Add(subEntity1); entity.ItemHistories.Add(subEntity2); entities.Add(entity); } if (isBulk) { if (ContextUtil.DbServer == DbServer.SqlServer) { using var transaction = context.Database.BeginTransaction(); var bulkConfig = new BulkConfig { //PreserveInsertOrder = true, // true is default SetOutputIdentity = true, BatchSize = 4000, UseTempDB = true, CalculateStats = true }; context.BulkInsert(entities, bulkConfig, (a) => WriteProgress(a)); Assert.Equal(EntitiesNumber - 1, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberDeleted); foreach (var entity in entities) { foreach (var subEntity in entity.ItemHistories) { subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB } subEntities.AddRange(entity.ItemHistories); } context.BulkInsert(subEntities); transaction.Commit(); } else if (ContextUtil.DbServer == DbServer.Sqlite) { using var transaction = context.Database.BeginTransaction(); var bulkConfig = new BulkConfig() { SetOutputIdentity = true }; context.BulkInsert(entities, bulkConfig); foreach (var entity in entities) { foreach (var subEntity in entity.ItemHistories) { subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB } subEntities.AddRange(entity.ItemHistories); } bulkConfig.SetOutputIdentity = false; context.BulkInsert(subEntities, bulkConfig); transaction.Commit(); } } else { context.Items.AddRange(entities); context.SaveChanges(); } // TEST int entitiesCount = ItemsCountQuery(context); Item lastEntity = context.Items.OrderByDescending(a => a.ItemId).FirstOrDefault(); Assert.Equal(EntitiesNumber - 1, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name); }
private async Task RunInsertAsync(bool isBulkOperation, bool insertTo2Tables) { 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 = i, Name = "name " + i, Description = "info " + Guid.NewGuid().ToString().Substring(0, 3), Quantity = i % 10, Price = i / (i % 5 + 1), TimeUpdated = DateTime.Now, }); } if (isBulkOperation) { if (insertTo2Tables) { using (var transaction = await context.Database.BeginTransactionAsync()) { await context.BulkInsertAsync(entities, new BulkConfig { PreserveInsertOrder = true, SetOutputIdentity = true, BatchSize = 4000 }); foreach (var entity in entities) { subEntities.Add(new ItemHistory { ItemHistoryId = SeqGuid.Create(), ItemId = entity.ItemId, Remark = "some more info" }); } await context.BulkInsertAsync(subEntities); transaction.Commit(); } } else { await context.BulkInsertAsync(entities); } } else { await context.Items.AddRangeAsync(entities); await context.SaveChangesAsync(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = ItemsCountQuery(context); Item lastEntity = LastItemQuery(context); Assert.Equal(EntitiesNumber - 1, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name); } }
private async Task RunBatchDeleteAsync() { using var context = new TestContext(ContextUtil.GetOptions()); await context.Items.Where(a => a.ItemId > 500).BatchDeleteAsync(); }
private async Task <int> RunTopBatchDeleteAsync() { using var context = new TestContext(ContextUtil.GetOptions()); return(await context.Items.Where(a => a.ItemId > 500).Take(1).BatchDeleteAsync()); }
private void DeletePreviousDatabase() { using var context = new TestContext(ContextUtil.GetOptions()); context.Database.EnsureDeleted(); }
private int RunTopBatchDelete() { using var context = new TestContext(ContextUtil.GetOptions()); return(context.Items.Where(a => a.ItemId > 500).Take(1).BatchDelete()); }
private void RunBatchDelete() { using var context = new TestContext(ContextUtil.GetOptions()); context.Items.Where(a => a.ItemId > 500).BatchDelete(); }
private void DefaultValuesTest(DbServer dbServer) { ContextUtil.DbServer = dbServer; using var context = new TestContext(ContextUtil.GetOptions()); context.Truncate <Document>(); bool isSqlite = dbServer == DbServer.Sqlite; var entities = new List <Document>() { new Document { DocumentId = Guid.Parse("15E5936C-8021-45F4-A055-2BE89B065D9E"), Content = "Info " + 1 }, new Document { DocumentId = Guid.Parse("B7A78674-477C-4357-AFD3-36DEB12CE777"), Content = "Info " + 2 }, new Document { DocumentId = Guid.Parse("00C69E47-A08F-49E0-97A6-56C62C9BB47E"), Content = "Info " + 3 }, new Document { DocumentId = Guid.Parse("22CF94AE-20D3-49DE-83FA-90E79DD94706"), Content = "Info " + 4 }, new Document { DocumentId = Guid.Parse("B3A2F9A5-4222-47C3-BEEA-BF50771665D3"), Content = "Info " + 5 }, new Document { DocumentId = Guid.Parse("12AF6361-95BC-44F3-A487-C91C440018D8"), Content = "Info " + 6 }, new Document { DocumentId = Guid.Parse("FE82B1FF-384D-4D49-88D7-D0FB2565D5BC"), Content = "Info " + 7 }, new Document { DocumentId = Guid.Parse("FF009E75-12CF-49B4-BAB9-F485C931DC91"), Content = "Info " + 8 }, }; var firstDocumentUp = entities.FirstOrDefault(); context.BulkInsertOrUpdate(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument var firstDocument = context.Documents.AsNoTracking().OrderBy(x => x.Content).FirstOrDefault(); var countDb = context.Documents.Count(); var countEntities = entities.Count(); // TEST Assert.Equal(countDb, countEntities); Assert.Equal(firstDocument.DocumentId, firstDocumentUp.DocumentId); }
private void RunUdttBatch() { var userRoles = ( from userId in Enumerable.Range(1, 5) from roleId in Enumerable.Range(1, 5) select new UserRole { UserId = userId, RoleId = roleId, } ) .ToList(); var random = new Random(); var keysToUpdate = userRoles .Where(x => random.Next() % 2 == 1) .Select(x => new UdttIntInt { C1 = x.UserId, C2 = x.RoleId, }) .ToList(); var keysToDelete = userRoles .Where(x => !keysToUpdate.Where(y => y.C1 == x.UserId && y.C2 == x.RoleId).Any()) .Select(x => new UdttIntInt { C1 = x.UserId, C2 = x.RoleId, }) .ToList(); using (var context = new TestContext(ContextUtil.GetOptions())) { context.UserRoles.BatchDelete(); context.UserRoles.AddRange(userRoles); context.SaveChanges(); } // read with User Defined Table Type parameter using (var context = new TestContext(ContextUtil.GetOptions())) { var keysToUpdateQueryable = GetQueryableUdtt(context, keysToUpdate); var userRolesToUpdate = context.UserRoles .Where(x => keysToUpdateQueryable.Where(y => y.C1 == x.UserId && y.C2 == x.RoleId).Any()) .ToList(); var keysToDeleteQueryable = GetQueryableUdtt(context, keysToDelete); var userRolesToDelete = context.UserRoles .Where(x => keysToDeleteQueryable.Where(y => y.C1 == x.UserId && y.C2 == x.RoleId).Any()) .ToList(); Assert.Equal(keysToUpdate.Count, userRolesToUpdate.Count); Assert.Equal(keysToDelete.Count, userRolesToDelete.Count); } // batch update and batch delete with User Defined Table Type parameter using (var context = new TestContext(ContextUtil.GetOptions())) { var keysToUpdateQueryable = GetQueryableUdtt(context, keysToUpdate); var keysToDeleteQueryable = GetQueryableUdtt(context, keysToDelete); var userRolesToUpdate = context.UserRoles.Where(x => keysToUpdateQueryable.Where(y => y.C1 == x.UserId && y.C2 == x.RoleId).Any()); var userRolesToDelete = context.UserRoles.Where(x => keysToDeleteQueryable.Where(y => y.C1 == x.UserId && y.C2 == x.RoleId).Any()); // System.ArgumentException : No mapping exists from object type System.Object[] to a known managed provider native type. userRolesToUpdate.BatchUpdate(x => new UserRole { Description = "updated", }); userRolesToDelete.BatchDelete(); } using (var context = new TestContext(ContextUtil.GetOptions())) { Assert.Equal(keysToUpdate.Count, context.UserRoles.Count()); Assert.True(!context.UserRoles.Where(x => x.Description == null || x.Description != "updated").Any()); } }
private async Task RunInsertOrUpdateOrDeleteAsync(bool isBulk) { using var context = new TestContext(ContextUtil.GetOptions()); var entities = new List <Item>(); var dateTimeNow = DateTime.Now; var dateTimeOffsetNow = DateTimeOffset.UtcNow; for (int i = 2; i <= EntitiesNumber; i += 2) { entities.Add(new Item { ItemId = i, Name = "name InsertOrUpdateOrDelete " + i, Description = "info", Quantity = i, Price = i / (i % 5 + 1), TimeUpdated = dateTimeNow }); } int?keepEntityItemId = null; if (isBulk) { var bulkConfig = new BulkConfig() { SetOutputIdentity = true, CalculateStats = true }; keepEntityItemId = 3; bulkConfig.SetSynchronizeFilter <Item>(e => e.ItemId != keepEntityItemId.Value); await context.BulkInsertOrUpdateOrDeleteAsync(entities, bulkConfig); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(EntitiesNumber / 2, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal((EntitiesNumber / 2) - 1, bulkConfig.StatsInfo.StatsNumberDeleted); } else { var existingItems = context.Items; var removedItems = existingItems.Where(x => !entities.Any(y => y.ItemId == x.ItemId)); context.Items.RemoveRange(removedItems); await context.Items.AddRangeAsync(entities); await context.SaveChangesAsync(); } // TEST using var contextRead = new TestContext(ContextUtil.GetOptions()); int entitiesCount = await contextRead.Items.CountAsync(); // = ItemsCountQuery(context); Item firstEntity = contextRead.Items.OrderBy(a => a.ItemId).FirstOrDefault(); // = LastItemQuery(context); Item lastEntity = contextRead.Items.OrderByDescending(a => a.ItemId).FirstOrDefault(); Assert.Equal(EntitiesNumber / 2 + (keepEntityItemId != null ? 1 : 0), entitiesCount); Assert.NotNull(firstEntity); Assert.Equal("name InsertOrUpdateOrDelete 2", firstEntity.Name); Assert.NotNull(lastEntity); Assert.Equal("name InsertOrUpdateOrDelete " + EntitiesNumber, lastEntity.Name); if (keepEntityItemId != null) { Assert.NotNull(context.Items.Where(x => x.ItemId == keepEntityItemId.Value).FirstOrDefault()); } if (isBulk) { var bulkConfig = new BulkConfig() { SetOutputIdentity = true, CalculateStats = true }; bulkConfig.SetSynchronizeFilter <Item>(e => e.ItemId != keepEntityItemId.Value); await context.BulkInsertOrUpdateOrDeleteAsync(new List <Item>(), bulkConfig); var storedEntities = contextRead.Items.ToList(); Assert.Single(storedEntities); Assert.Equal(3, storedEntities[0].ItemId); } }
private async Task RunInsertAsync(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); var subEntities = new List <ItemHistory>(); for (int i = 1; i < EntitiesNumber; i++) { var entity = 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, ItemHistories = new List <ItemHistory>() }; var subEntity1 = new ItemHistory { ItemHistoryId = SeqGuid.Create(), Remark = $"some more info {i}.1" }; var subEntity2 = new ItemHistory { ItemHistoryId = SeqGuid.Create(), Remark = $"some more info {i}.2" }; entity.ItemHistories.Add(subEntity1); entity.ItemHistories.Add(subEntity2); entities.Add(entity); } if (isBulkOperation) { if (ContextUtil.DbServer == DbServer.SqlServer) { using (var transaction = await context.Database.BeginTransactionAsync()) { var bulkConfig = new BulkConfig { PreserveInsertOrder = true, SetOutputIdentity = true, BatchSize = 4000, CalculateStats = true }; await context.BulkInsertAsync(entities, bulkConfig); Assert.Equal(EntitiesNumber - 1, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberDeleted); foreach (var entity in entities) { foreach (var subEntity in entity.ItemHistories) { subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB } subEntities.AddRange(entity.ItemHistories); } await context.BulkInsertAsync(subEntities); transaction.Commit(); } } else if (ContextUtil.DbServer == DbServer.Sqlite) { using (var transaction = context.Database.BeginTransaction()) { var bulkConfig = new BulkConfig() { SetOutputIdentity = true, }; await context.BulkInsertAsync(entities, bulkConfig); foreach (var entity in entities) { foreach (var subEntity in entity.ItemHistories) { subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB } subEntities.AddRange(entity.ItemHistories); } await context.BulkInsertAsync(subEntities, bulkConfig); transaction.Commit(); } } } else { await context.Items.AddRangeAsync(entities); await context.SaveChangesAsync(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { //int entitiesCount = ItemsCountQuery(context); int entitiesCount = await context.Items.CountAsync(); //Item lastEntity = LastItemQuery(context); Item lastEntity = context.Items.OrderByDescending(a => a.ItemId).FirstOrDefault(); Assert.Equal(EntitiesNumber - 1, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name); } }
private async Task DeletePreviousDatabaseAsync() { using var context = new TestContext(ContextUtil.GetOptions()); await context.Database.EnsureDeletedAsync().ConfigureAwait(false); }
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 }, (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); } }
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 }