private async Task RunDeleteAsync(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = AllItemsQuery(context).ToList(); // ItemHistories will also be deleted because of Relationship - ItemId (Delete Rule: Cascade) if (isBulkOperation) { await context.BulkDeleteAsync(entities); } else { context.Items.RemoveRange(entities); await context.SaveChangesAsync(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = ItemsCountQuery(context); Item lastEntity = LastItemQuery(context); Assert.Equal(0, entitiesCount); Assert.Null(lastEntity); } using (var context = new TestContext(ContextUtil.GetOptions())) { // Resets AutoIncrement context.Database.ExecuteSqlCommand("DBCC CHECKIDENT ('dbo.[" + nameof(Item) + "]', RESEED, 0);"); //context.Database.ExecuteSqlCommand($"TRUNCATE TABLE {nameof(Item)};"); // can NOT work when there is ForeignKey - ItemHistoryId } }
private async Task RunUpdateAsync(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) { await context.BulkUpdateAsync(entities); } else { context.Items.UpdateRange(entities); await context.SaveChangesAsync(); } } 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 async Task UpdateSettingAsync(SettingsEnum settings, object value) { using (var context = new TestContext(ContextUtil.GetOptions())) { await context.TruncateAsync <Setting>(); } using (var context = new TestContext(ContextUtil.GetOptions())) { await context.Settings.AddAsync(new Setting() { Settings = SettingsEnum.Sett1, Value = "Val1" }).ConfigureAwait(false); await context.SaveChangesAsync().ConfigureAwait(false); } using (var context = new TestContext(ContextUtil.GetOptions())) { // can work with explicit value: .Where(x => x.Settings == SettingsEnum.Sett1) or if named Parameter used then it has to be named (settings) same as Property (Settings) - Case not relevant, it is CaseInsensitive await context.Settings.Where(x => x.Settings == settings).BatchUpdateAsync(x => new Setting { Value = value.ToString() }).ConfigureAwait(false); } using (var context = new TestContext(ContextUtil.GetOptions())) { await context.TruncateAsync <Setting>(); } }
private async Task RunUpdateAsync(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { int counter = 1; var entities = AllItemsQuery(context).ToList(); foreach (var entity in entities) { entity.Description = "Desc Update " + counter++; entity.TimeUpdated = DateTime.Now; } if (isBulkOperation) { await context.BulkUpdateAsync(entities); } else { context.Items.UpdateRange(entities); await context.SaveChangesAsync(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = ItemsCountQuery(context); Item lastEntity = LastItemQuery(context); Assert.Equal(EntitiesNumber, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("Desc Update " + EntitiesNumber, lastEntity.Description); } }
private async Task RunInsertAsync(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 = 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) { await context.BulkInsertAsync(entities, new BulkConfig { PreserveInsertOrder = true, SetOutputIdentity = true, BatchSize = 5000 }); foreach (var entity in entities) { subEntities.Add(new ItemHistory { ItemHistoryId = SeqGuid.Create(), ItemId = entity.ItemId, Remark = "some more info" }); } await context.BulkInsertAsync(subEntities); } else { await context.BulkInsertAsync(entities); } } else { await context.Items.AddRangeAsync(entities); await context.SaveChangesAsync(); } } 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); } }
private async Task RunInsertOrUpdateOrDeleteAsync(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); var dateTimeNow = DateTime.Now; 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 }); } if (isBulkOperation) { var bulkConfig = new BulkConfig() { SetOutputIdentity = true, CalculateStats = true }; await context.BulkInsertOrUpdateOrDeleteAsync(entities, bulkConfig); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(EntitiesNumber / 2, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal(EntitiesNumber / 2, 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(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { //int entitiesCount = ItemsCountQuery(context); int entitiesCount = await context.Items.CountAsync(); //Item lastEntity = LastItemQuery(context); Item firstEntity = context.Items.OrderBy(a => a.ItemId).FirstOrDefault(); Item lastEntity = context.Items.OrderByDescending(a => a.ItemId).FirstOrDefault(); Assert.Equal(EntitiesNumber / 2, entitiesCount); Assert.NotNull(firstEntity); Assert.Equal("name InsertOrUpdateOrDelete 2", firstEntity.Name); Assert.NotNull(lastEntity); Assert.Equal("name InsertOrUpdateOrDelete " + EntitiesNumber, lastEntity.Name); } }
private async Task RunInsertOrUpdateAsync(bool isBulk, DbServer dbServer) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); var dateTimeNow = DateTime.Now; for (int i = 2; i <= EntitiesNumber; i += 2) { entities.Add(new Item { ItemId = i, Name = "name InsertOrUpdate " + i, Description = "info", Quantity = i, Price = i / (i % 5 + 1), TimeUpdated = dateTimeNow }); } if (isBulk) { var bulkConfig = new BulkConfig() { SetOutputIdentity = true, CalculateStats = true }; await context.BulkInsertOrUpdateAsync(entities, bulkConfig); if (dbServer == DbServer.SqlServer) { Assert.Equal(1, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(EntitiesNumber / 2 - 1, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberDeleted); } } 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, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name InsertOrUpdate " + EntitiesNumber, lastEntity.Name); } }
private async Task RunDeleteAsync(bool isBulkOperation, DbServer databaseType) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = AllItemsQuery(context).ToList(); // ItemHistories will also be deleted because of Relationship - ItemId (Delete Rule: Cascade) if (isBulkOperation) { var bulkConfig = new BulkConfig() { CalculateStats = true }; await context.BulkDeleteAsync(entities, bulkConfig); if (databaseType == DbServer.SqlServer) { Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal(EntitiesNumber / 2, bulkConfig.StatsInfo.StatsNumberDeleted); } } else { context.Items.RemoveRange(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(0, entitiesCount); Assert.Null(lastEntity); } using (var context = new TestContext(ContextUtil.GetOptions())) { if (databaseType == DbServer.SqlServer) { await context.Database.ExecuteSqlRawAsync("DBCC CHECKIDENT('[dbo].[Item]', RESEED, 0);").ConfigureAwait(false); } if (databaseType == DbServer.Sqlite) { await context.Database.ExecuteSqlRawAsync("DELETE FROM sqlite_sequence WHERE name = 'Item';").ConfigureAwait(false); } } }
private async Task RunUpdateAsync(bool isBulkOperation, DbServer databaseType) { using (var context = new TestContext(ContextUtil.GetOptions())) { int counter = 1; var entities = AllItemsQuery(context).ToList(); foreach (var entity in entities) { entity.Description = "Desc Update " + counter++; entity.TimeUpdated = DateTime.Now; } if (isBulkOperation) { var bulkConfig = new BulkConfig() { SetOutputIdentity = true, CalculateStats = true }; await context.BulkUpdateAsync(entities, bulkConfig); if (databaseType == 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); 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, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("Desc Update " + EntitiesNumber, lastEntity.Description); } }
private async Task RunDeleteAsync(bool isBulk, DbServer dbServer) { using var context = new TestContext(ContextUtil.GetOptions()); var entities = AllItemsQuery(context).ToList(); // ItemHistories will also be deleted because of Relationship - ItemId (Delete Rule: Cascade) if (isBulk) { var bulkConfig = new BulkConfig() { CalculateStats = true }; await context.BulkDeleteAsync(entities, bulkConfig); if (dbServer == DbServer.SQLServer) { Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberInserted); Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated); Assert.Equal(EntitiesNumber / 2, bulkConfig.StatsInfo.StatsNumberDeleted); } } else { context.Items.RemoveRange(entities); await context.SaveChangesAsync(); } // TEST int entitiesCount = await context.Items.CountAsync(); Item lastEntity = context.Items.OrderByDescending(a => a.ItemId).FirstOrDefault(); Assert.Equal(0, entitiesCount); Assert.Null(lastEntity); // RESET AutoIncrement string deleteTableSql = dbServer switch { DbServer.SQLServer => $"DBCC CHECKIDENT('[dbo].[{nameof(Item)}]', RESEED, 0);", DbServer.SQLite => $"DELETE FROM sqlite_sequence WHERE name = '{nameof(Item)}';", _ => throw new ArgumentException($"Unknown database type: '{dbServer}'.", nameof(dbServer)), }; await context.Database.ExecuteSqlRawAsync(deleteTableSql).ConfigureAwait(false); }
private async Task RunInsertOrUpdateAsync(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); var dateTimeNow = DateTime.Now; for (int i = 2; i <= EntitiesNumber; i += 2) { entities.Add(new Item { ItemId = i, Name = "name InsertOrUpdate " + i, Description = "info", Quantity = i, Price = i / (i % 5 + 1), TimeUpdated = dateTimeNow }); } if (isBulkOperation) { var bulkConfig = new BulkConfig() { SetOutputIdentity = true, CalculateStats = true }; await context.BulkInsertOrUpdateAsync(entities, bulkConfig); } 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, entitiesCount); Assert.NotNull(lastEntity); Assert.Equal("name InsertOrUpdate " + EntitiesNumber, lastEntity.Name); } }
internal async Task RunDeleteAllAsync(DbServer databaseType) { using (var context = new TestContext(ContextUtil.GetOptions())) { await context.Items.AddAsync(new Item { }); // used for initial add so that after RESEED it starts from 1, not 0 await context.SaveChangesAsync(); //await context.Items.BatchDeleteAsync(); // TODO: Use after BatchDelete gets implemented for v3.0 await context.BulkDeleteAsync(context.Items.ToList()); if (databaseType == DbServer.SqlServer) { await context.Database.ExecuteSqlRawAsync("DBCC CHECKIDENT('[dbo].[Item]', RESEED, 0);").ConfigureAwait(false); } if (databaseType == DbServer.Sqlite) { await context.Database.ExecuteSqlRawAsync("DELETE FROM sqlite_sequence WHERE name = 'Item';").ConfigureAwait(false); } } }
internal async Task RunDeleteAllAsync(DbServer dbServer) { using var context = new TestContext(ContextUtil.GetOptions()); await context.Items.AddAsync(new Item { }); // used for initial add so that after RESEED it starts from 1, not 0 await context.SaveChangesAsync(); await context.Items.BatchDeleteAsync(); await context.BulkDeleteAsync(context.Items.ToList()); // RESET AutoIncrement string deleteTableSql = dbServer switch { DbServer.SqlServer => $"DBCC CHECKIDENT('[dbo].[{nameof(Item)}]', RESEED, 0);", DbServer.Sqlite => $"DELETE FROM sqlite_sequence WHERE name = '{nameof(Item)}';", _ => throw new ArgumentException($"Unknown database type: '{dbServer}'.", nameof(dbServer)), }; context.Database.ExecuteSqlRaw(deleteTableSql); }
private async Task RunInsertOrUpdateAsync(bool isBulkOperation) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = new List <Item>(); var dateTimeNow = DateTime.Now; for (int i = 2; i <= entitiesNumber; i += 2) { entities.Add(new Item { ItemId = i, Name = "name InsertOrUpdate " + i, Description = "info", Quantity = i, Price = i / (i % 5 + 1), TimeUpdated = dateTimeNow }); } if (isBulkOperation) { await context.BulkInsertOrUpdateAsync(entities); } else { await context.Items.AddRangeAsync(entities); await context.SaveChangesAsync(); } } 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 InsertOrUpdate " + entitiesNumber, lastEntity.Name); } }
private async Task RunDeleteAsync(bool isBulkOperation, DbServer databaseType) { using (var context = new TestContext(ContextUtil.GetOptions())) { var entities = AllItemsQuery(context).ToList(); // ItemHistories will also be deleted because of Relationship - ItemId (Delete Rule: Cascade) if (isBulkOperation) { await context.BulkDeleteAsync(entities); } else { context.Items.RemoveRange(entities); await context.SaveChangesAsync(); } } using (var context = new TestContext(ContextUtil.GetOptions())) { int entitiesCount = ItemsCountQuery(context); Item lastEntity = LastItemQuery(context); Assert.Equal(0, entitiesCount); Assert.Null(lastEntity); } using (var context = new TestContext(ContextUtil.GetOptions())) { if (databaseType == DbServer.SqlServer) { await context.Database.ExecuteSqlCommandAsync("DBCC CHECKIDENT('[dbo].[Item]', RESEED, 0);").ConfigureAwait(false); } if (databaseType == DbServer.Sqlite) { await context.Database.ExecuteSqlCommandAsync("DELETE FROM sqlite_sequence WHERE name = 'Item';").ConfigureAwait(false); } } }
private async Task RunInsert() { 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 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 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); } }