//[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 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);
}
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.
CustomDestinationTableName = nameof(Log),
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);
}
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
, UseTempDB = true
},
(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);
}
}
