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 void RunDeleteAll(DbServer databaseType)
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.Items.Add(new Item {
}); // used for initial add so that after RESEED it starts from 1, not 0
context.SaveChanges();
context.Items.BatchDelete();
context.BulkDelete(context.Items.ToList());
if (databaseType == DbServer.SqlServer)
{
context.Database.ExecuteSqlRaw("DBCC CHECKIDENT('[dbo].[Item]', RESEED, 0);");
}
if (databaseType == DbServer.Sqlite)
{
context.Database.ExecuteSqlRaw("DELETE FROM sqlite_sequence WHERE name = 'Item';");
}
}
}
private async Task RunBatchUpdateAsync()
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
//var updateColumns = new List<string> { nameof(Item.Quantity) }; // Adding explicitly PropertyName for update to its default value
decimal price = 0;
var query = context.Items.Where(a => a.ItemId <= 500 && a.Price >= price);
var parametersDict = new Dictionary <string, object> // is used to fix issue of getting Query Parameters in .NetCore 3.0
{
{ nameof(price), price }
};
await query.BatchUpdateAsync(new Item { Description = "Updated" } /*, updateColumns*/, parametersDict : parametersDict);
await query.BatchUpdateAsync(a => new Item {
Name = a.Name + " Concatenated", Quantity = a.Quantity + 100, Price = null
}, parametersDict); // example of BatchUpdate value Increment/Decrement
}
}
private void TableWithSpecialRowVersion()
{
ContextUtil.DbServer = DbServer.SqlServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.AtypicalRowVersionEntities.BatchDelete();
var bulk = new List <AtypicalRowVersionEntity>();
for (var i = 0; i < 100; i++)
{
bulk.Add(new AtypicalRowVersionEntity {
Id = Guid.NewGuid(), Name = $"Row {i}", RowVersion = i, SyncDevice = "Test"
});
}
//Assert.Throws<InvalidOperationException>(() => context.BulkInsertOrUpdate(bulk)); // commented since when running in Debug mode it pauses on Exception
context.BulkInsertOrUpdate(bulk, new BulkConfig {
IgnoreRowVersion = true
});
Assert.Equal(bulk.Count(), context.AtypicalRowVersionEntities.Count());
}
private void RunBatchUpdate()
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
//var updateColumns = new List<string> { nameof(Item.Quantity) }; // Adding explicitly PropertyName for update to its default value
decimal price = 0;
var query = context.Items.Where(a => a.ItemId <= 500 && a.Price >= price);
query.BatchUpdate(new Item {
Description = "Updated", Price = 1.5m
} /*, updateColumns*/);
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
//query.BatchUpdate(a => new Item { Quantity = a.Quantity + 100 }); // example direct value without variable
}
}
internal void RunDeleteAll(DbServer dbServer)
{
using var context = new TestContext(ContextUtil.GetOptions());
context.Items.Add(new Item {
}); // used for initial add so that after RESEED it starts from 1, not 0
context.SaveChanges();
context.Items.BatchDelete();
context.BulkDelete(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 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);
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 void RunDelete(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)
{
context.BulkDelete(entities);
}
else
{
context.Items.RemoveRange(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(0, entitiesCount);
Assert.Null(lastEntity);
}
// Resets AutoIncrement
using (var context = new TestContext(ContextUtil.GetOptions()))
{
if (databaseType == DbServer.SqlServer)
{
context.Database.ExecuteSqlRaw("DBCC CHECKIDENT ('dbo.[" + nameof(Item) + "]', RESEED, 0);"); // can NOT use $"...{nameof(Item)..." because it gets parameterized
}
else if (databaseType == DbServer.Sqlite)
{
context.Database.ExecuteSqlRaw("DELETE FROM sqlite_sequence WHERE name = 'Item';");
}
}
}
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.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 {
UpdateByProperties = new List <string> {
nameof(Item.Name)
},
PropertiesToInclude = new List <string> {
nameof(Item.Description)
}
}
);
}
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 InsertOrUpdate " + entitiesNumber, lastEntity.Name);
}
}
private void NoPrimaryKeyTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
var list = context.Moduls.ToList();
var bulkConfig = new BulkConfig {
UpdateByProperties = new List <string> {
nameof(Modul.Code)
}
};
context.BulkDelete(list, bulkConfig);
var list1 = new List <Modul>();
var list2 = new List <Modul>();
for (int i = 1; i <= 20; i++)
{
if (i <= 10)
{
list1.Add(new Modul
{
Code = i.ToString(),
Name = "Name " + i.ToString("00"),
});
}
list2.Add(new Modul
{
Code = i.ToString(),
Name = "Name " + i.ToString("00"),
});
}
context.BulkInsert(list1);
list2[0].Name = "UPD";
context.BulkInsertOrUpdate(list2);
// TEST
Assert.Equal(20, context.Moduls.ToList().Count());
}
private void HierarchyIdColumnTest()
{
ContextUtil.DbServer = DbServer.SQLServer;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.BulkDelete(context.Categories.ToList());
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var nodeIdAsString = "/1/";
var entities = new List <Category> {
new Category
{
Name = "Root Element",
HierarchyDescription = HierarchyId.Parse(nodeIdAsString)
}
};
context.BulkInsertOrUpdate(entities);
}
}
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);
}
}
public async Task BatchUpdateAsync_correctly_specifies_AnsiString_type_on_the_sql_parameter()
{
var dbCommandInterceptor = new TestDbCommandInterceptor();
var interceptors = new[] { dbCommandInterceptor };
using var testContext = new TestContext(ContextUtil.GetOptions <TestContext>(DbServer.SqlServer, interceptors));
string oldPhoneNumber = "7756789999";
string newPhoneNumber = "3606789999";
_ = await testContext.Parents
.Where(parent => parent.PhoneNumber == oldPhoneNumber)
.BatchUpdateAsync(parent => new Parent {
PhoneNumber = newPhoneNumber
})
.ConfigureAwait(false);
var executedCommand = dbCommandInterceptor.ExecutedNonQueryCommands.Last();
Assert.Equal(2, executedCommand.DbParameters.Count);
var oldPhoneNumberParameter = (Microsoft.Data.SqlClient.SqlParameter)executedCommand.DbParameters.Single(param => param.ParameterName == "@__oldPhoneNumber_0");
Assert.Equal(System.Data.DbType.AnsiString, oldPhoneNumberParameter.DbType);
Assert.Equal(System.Data.SqlDbType.VarChar, oldPhoneNumberParameter.SqlDbType);
var newPhoneNumberParameter = (Microsoft.Data.SqlClient.SqlParameter)executedCommand.DbParameters.Single(param => param.ParameterName == "@param_1");
Assert.Equal(System.Data.DbType.AnsiString, newPhoneNumberParameter.DbType);
Assert.Equal(System.Data.SqlDbType.VarChar, newPhoneNumberParameter.SqlDbType);
var expectedSql =
@"UPDATE p SET [p].[PhoneNumber] = @param_1
FROM [Parent] AS [p]
WHERE [p].[PhoneNumber] = @__oldPhoneNumber_0";
Assert.Equal(expectedSql.Replace("\r\n", "\n"), executedCommand.Sql.Replace("\r\n", "\n"));
}
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)
{
var bulkConfig = new BulkConfig()
{
SetOutputIdentity = true, CalculateStats = true
};
await context.BulkUpdateAsync(entities, bulkConfig);
}
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);
}
}
[InlineData(DbServer.Sqlite)] // Does NOT have Computed Columns and TimeStamp can be set with DefaultValueSql: "CURRENT_TIMESTAMP" as it is in OnModelCreating() method.
private void InsertWithDbComputedColumnsAndOutput(DbServer databaseType)
{
ContextUtil.DbServer = databaseType;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.BulkDelete(context.Documents.ToList());
var entities = new List <Document>();
for (int i = 0; i < EntitiesNumber; i++)
{
var entity = new Document
{
Content = "Some data " + i
};
if (databaseType == DbServer.Sqlite)
{
entity.ContentLength = entity.Content.Length;
}
entities.Add(entity);
}
//context.BulkInsert(entities, new BulkConfig { SetOutputIdentity = true });
context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument
if (databaseType == DbServer.SqlServer)
{
context.BulkRead(entities, new BulkConfig()
{
SetOutputIdentity = true
}); // Not Yet supported for Sqlite (To Test BulkRead with ComputedColumns)
}
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.Documents.ToList();
Assert.Equal(EntitiesNumber, entities.Count());
}
}
private void RunInsertOrUpdate(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 = isBulkOperation ? i : 0,
Name = "name InsertOrUpdate " + i,
Description = "info",
Quantity = i + 100,
Price = i / (i % 5 + 1),
TimeUpdated = dateTimeNow,
});
}
if (isBulkOperation)
{
context.BulkInsertOrUpdate(entities, null, (a) => WriteProgress(a));
}
else
{
context.Items.Add(entities[entities.Count - 1]);
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 InsertOrUpdate " + EntitiesNumber, lastEntity.Name);
}
}
private static void BulkOperationShouldNotCloseOpenConnection(DbServer dbServer, Action <TestContext> bulkOperation)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
var sqlHelper = context.GetService <ISqlGenerationHelper>();
context.Database.OpenConnection();
try
{
// we use a temp table to verify whether the connection has been closed (and re-opened) inside BulkUpdate(Async)
var columnName = sqlHelper.DelimitIdentifier("Id");
var tableName = sqlHelper.DelimitIdentifier("#MyTempTable");
var createTableSql = $" TABLE {tableName} ({columnName} INTEGER);";
createTableSql = dbServer switch
{
DbServer.SQLite => $"CREATE TEMPORARY {createTableSql}",
DbServer.SQLServer => $"CREATE {createTableSql}",
_ => throw new ArgumentException($"Unknown database type: '{dbServer}'.", nameof(dbServer)),
};
context.Database.ExecuteSqlRaw(createTableSql);
bulkOperation(context);
context.Database.ExecuteSqlRaw($"SELECT {columnName} FROM {tableName}");
}
catch (Exception ex)
{
// Table already exist
}
finally
{
context.Database.CloseConnection();
}
}
public void InsertEnumStringValue(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.Database.ExecuteSqlRaw($@"DELETE FROM ""{nameof(Wall)}""");
var newWall = new Wall()
{
Id = 1,
WallTypeValue = WallType.Brick
};
// INSERT
context.BulkInsert(new List <Wall>()
{
newWall
});
var addedWall = context.Walls.AsNoTracking().First(x => x.Id == newWall.Id);
Assert.True(addedWall.WallTypeValue == newWall.WallTypeValue);
}
private void InsertWithOwnedTypes()
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.Database.ExecuteSqlCommand("TRUNCATE TABLE [" + nameof(ChangeLog) + "]");
var entities = new List <ChangeLog>();
for (int i = 1; i <= EntitiesNumber; i++)
{
entities.Add(new ChangeLog
{
ChangeLogId = 1,
Description = "Dsc " + i,
Audit = new Audit
{
ChangedBy = "User" + 1,
ChangedTime = DateTime.Now
}
});
}
context.BulkInsertOrUpdate(entities);
}
}
[InlineData(DbServer.SQLite)] // Does NOT have Computed Columns
private void ParameterlessConstructorTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.Truncate <Letter>();
var entities = new List <Letter>();
int counter = 10;
for (int i = 1; i <= counter; i++)
{
var entity = new Letter("Note " + i);
entities.Add(entity);
}
context.BulkInsert(entities);
var count = context.Letters.Count();
var firstDocumentNote = context.Letters.AsNoTracking().FirstOrDefault();
// TEST
Assert.Equal(counter, count);
Assert.Equal("Note 1", firstDocumentNote.Note);
}
private void InsertWithDiscriminatorShadow()
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
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(EntitiesNumber, entities.Count());
context.BulkDelete(entities);
}
}
private void 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 " + Guid.NewGuid().ToString().Substring(0, 3),
Description = "info",
Quantity = i % 10,
Price = i / (i % 5 + 1),
TimeUpdated = DateTime.Now,
ItemHistories = new List <ItemHistory>()
};
entities.Add(entity);
}
context.Items.AddRange(entities); // does not guarantee insert order for SqlServer
context.SaveChanges();
}
}
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");
await UpdateByteArrayToDefaultAsync();
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);
}
}
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 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 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 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 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 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 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());
}
}
