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
}