private void DestinationAndSourceTableNameTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.Truncate <Entry>();
context.Truncate <EntryPrep>();
context.Truncate <EntryArchive>();
var entities = new List <Entry>();
for (int i = 1; i <= 10; i++)
{
var entity = new Entry
{
Name = "Name " + i,
};
entities.Add(entity);
}
// [DEST]
context.BulkInsert(entities, b => b.CustomDestinationTableName = nameof(EntryArchive)); // Insert into table 'EntryArchive'
Assert.Equal(10, context.EntryArchives.Count());
// [SOURCE] (With CustomSourceTableName list not used so can be empty)
context.BulkInsert(new List <Entry>(), b => b.CustomSourceTableName = nameof(EntryArchive)); // InsertOrMERGE from table 'EntryArchive' into table 'Entry'
Assert.Equal(10, context.Entries.Count());
var entities2 = new List <EntryPrep>();
for (int i = 1; i <= 20; i++)
{
var entity = new EntryPrep
{
NameInfo = "Name Info " + i,
};
entities2.Add(entity);
}
context.EntryPreps.AddRange(entities2);
context.SaveChanges();
var mappings = new Dictionary <string, string>();
mappings.Add(nameof(EntryPrep.EntryPrepId), nameof(Entry.EntryId)); // here used 'nameof(Prop)' since Columns have the same name as Props
mappings.Add(nameof(EntryPrep.NameInfo), nameof(Entry.Name)); // if columns they were different name then they would be set with string names, eg. "EntryPrepareId"
var bulkConfig = new BulkConfig {
CustomSourceTableName = nameof(EntryPrep),
CustomSourceDestinationMappingColumns = mappings,
//UpdateByProperties = new List<string> { "Name" } // with this all are insert since names are different
};
// [SOURCE]
context.BulkInsertOrUpdate(new List <Entry>(), bulkConfig); // InsertOrMERGE from table 'EntryPrep' into table 'Entry'
Assert.Equal(20, context.Entries.Count());
}
private void InsertAndUpdateWithCompositeKey(DbServer databaseType)
{
ContextUtil.DbServer = databaseType;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.Truncate <UserRole>();
var entities = new List <UserRole>();
for (int i = 0; i < EntitiesNumber; i++)
{
entities.Add(new UserRole
{
UserId = i / 10,
RoleId = i % 10,
Description = "desc"
});
}
context.BulkInsert(entities);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.UserRoles.ToList();
int entitiesCount = entities.Count();
for (int i = 0; i < entitiesCount; i++)
{
entities[i].Description = "desc updated " + i;
}
context.BulkUpdate(entities);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.UserRoles.ToList();
Assert.Equal(EntitiesNumber, entities.Count());
context.BulkDelete(entities);
}
}
[InlineData(DbServer.SQLite)] // Does NOT have Computed Columns
private void ComputedAndDefaultValuesTest(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>();
for (int i = 1; i <= EntitiesNumber; i++)
{
var entity = new Document
{
Content = "Info " + i
};
if (isSqlite)
{
entity.DocumentId = Guid.NewGuid();
entity.ContentLength = entity.Content.Length;
}
entities.Add(entity);
}
context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument
var firstDocument = context.Documents.AsNoTracking().FirstOrDefault();
var count = context.Documents.Count();
// TEST
Assert.Equal("DefaultData", firstDocument.Tag);
firstDocument.Tag = null;
var upsertList = new List <Document> {
//firstDocument, // GetPropertiesWithDefaultValue .SelectMany(
new Document {
Content = "Info " + (count + 1)
}, // to test adding new with InsertOrUpdate (entity having Guid DbGenerated)
new Document {
Content = "Info " + (count + 2)
}
};
if (isSqlite)
{
upsertList[0].DocumentId = Guid.NewGuid(); //[1]
upsertList[1].DocumentId = Guid.NewGuid(); //[2]
}
count += 2;
context.BulkInsertOrUpdate(upsertList);
firstDocument = context.Documents.AsNoTracking().FirstOrDefault();
var entitiesCount = context.Documents.Count();
//Assert.Null(firstDocument.Tag); // OnUpdate columns with Defaults not omitted, should change even to default value, in this case to 'null'
Assert.NotEqual(Guid.Empty, firstDocument.DocumentId);
Assert.Equal(true, firstDocument.IsActive);
Assert.Equal(firstDocument.Content.Length, firstDocument.ContentLength);
Assert.Equal(entitiesCount, count);
}
private void UpdateSetting(SettingsEnum settings, object value)
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.Truncate <Setting>();
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.Settings.Add(new Setting()
{
Settings = SettingsEnum.Sett1, Value = "Val1"
});
context.SaveChanges();
}
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
context.Settings.Where(x => x.Settings == settings).BatchUpdate(x => new Setting {
Value = value.ToString()
});
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.Truncate <Setting>();
}
}
private void RunBatchUpdateEnum(DbServer dbServer)
{
using var context = new TestContext(ContextUtil.GetOptions());
context.Truncate <Source>();
context.Sources.AddRange(new Source[] {
new Source {
StatusId = Status.Init, TypeId = Type.Type2
},
new Source {
StatusId = Status.Changed, TypeId = Type.Type2
}
});
context.SaveChanges();
var updateValues = new Source()
{
StatusId = Status.Changed
};
var updateColumns = new List <string>()
{
nameof(updateValues.StatusId)
};
context.Sources.Where(e => e.StatusId == Status.Init).BatchUpdate(updateValues, updateColumns);
Assert.Equal(Type.Type2, context.Sources.FirstOrDefault().TypeId); // Should remain 'Type.Type2' and not be changed to default 'Type.Undefined'
}
[InlineData(DbServer.Sqlite)] // Does NOT have Computed Columns
private void ComputedAndDefaultValuesTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.Truncate <Document>();
var entities = new List <Document>();
for (int i = 1; i <= EntitiesNumber; i++)
{
var entity = new Document
{
Content = "Info " + i
};
if (dbServer == DbServer.Sqlite)
{
entity.DocumentId = Guid.NewGuid();
entity.ContentLength = entity.Content.Length;
}
entities.Add(entity);
}
context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument
// TEST
var documents = context.Documents.ToList();
Assert.Equal(EntitiesNumber, documents.Count());
var firstDocument = documents[0];
Assert.NotEqual(Guid.Empty, firstDocument.DocumentId);
Assert.Equal(firstDocument.Content.Length, firstDocument.ContentLength);
Assert.Equal(true, firstDocument.IsActive);
}
//[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
{
Data = "Some data " + i
};
entities.Add(entity);
}
context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument
// For testing concurrency conflict (UPDATE changes RowVersion which is TimeStamp column)
context.Database.ExecuteSqlRaw("UPDATE dbo.[File] SET Data = 'Some data 1 PRE CHANGE' WHERE [Id] = 1;");
var entitiesToUpdate = entities.Take(10).ToList();
foreach (var entityToUpdate in entitiesToUpdate)
{
entityToUpdate.Data += " 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 InsertWithOwnedTypes(DbServer databaseType)
{
ContextUtil.DbServer = databaseType;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
if (databaseType == 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 (databaseType == DbServer.SqlServer)
{
context.BulkRead(
entities,
new BulkConfig
{
UpdateByProperties = new List <string> {
nameof(Item.Description)
}
}
);
Assert.Equal(2, entities[1].ChangeLogId);
}
}
}
//[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
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 - 1; i++)
{
entities.Add(new ItemLink
{
ItemLinkId = 0,
Item = items[i % items.Count]
});
}
context.BulkInsert(entities);
if (dbServer == DbServer.SqlServer)
{
context.BulkRead(entities);
foreach (var entity in entities)
{
Assert.NotNull(entity.Item);
}
}
context.BulkDelete(context.ItemLinks.ToList());
}
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);
var entitiesToUpsert = new List <UserRole>()
{
new UserRole {
UserId = 1, RoleId = 1
},
new UserRole {
UserId = 2, RoleId = 2
},
new UserRole {
UserId = 100, RoleId = 10
},
};
// TEST
var entities = context.UserRoles.ToList();
Assert.Equal(EntitiesNumber, entities.Count());
context.BulkInsertOrUpdate(entitiesToUpsert, new BulkConfig {
PropertiesToInclude = new List <string> {
nameof(UserRole.UserId), nameof(UserRole.RoleId)
}
});
var entitiesFinal = context.UserRoles.ToList();
Assert.Equal(EntitiesNumber + 1, entitiesFinal.Count());
}
[InlineData(DbServer.Sqlite)] // Does NOT have Computed Columns
private void ComputedAndDefaultValuesTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.Truncate <Document>();
var entities = new List <Document>();
for (int i = 1; i <= EntitiesNumber; i++)
{
var entity = new Document
{
Content = "Info " + i
};
if (dbServer == DbServer.Sqlite)
{
entity.DocumentId = Guid.NewGuid();
entity.ContentLength = entity.Content.Length;
}
entities.Add(entity);
}
context.BulkInsert(entities, bulkAction => bulkAction.SetOutputIdentity = true); // example of setting BulkConfig with Action argument
var firstDocument = context.Documents.AsNoTracking().FirstOrDefault();
var count = context.Documents.Count();
// TEST
Assert.Equal("DefaultData", firstDocument.Tag);
firstDocument.Tag = null;
context.BulkInsertOrUpdate(new List <Document> {
firstDocument
});
firstDocument = context.Documents.AsNoTracking().FirstOrDefault();
Assert.Null(firstDocument.Tag); // OnUpdate columns with Defaults not ommited, should change even to default value, in this case to 'null'
Assert.NotEqual(Guid.Empty, firstDocument.DocumentId);
Assert.Equal(true, firstDocument.IsActive);
Assert.Equal(firstDocument.Content.Length, firstDocument.ContentLength);
Assert.Equal(EntitiesNumber, count);
}
private void DefaultValuesTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.Truncate <Document>();
context.Documents.BatchDelete();
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("00C69E47-A08F-49E0-97A6-56C62C9BB47E"), Content = "Info " + 2
},
new Document {
DocumentId = Guid.Parse("22CF94AE-20D3-49DE-83FA-90E79DD94706"), Content = "Info " + 3
},
new Document {
DocumentId = Guid.Parse("B3A2F9A5-4222-47C3-BEEA-BF50771665D3"), Content = "Info " + 4
},
new Document {
DocumentId = Guid.Parse("12AF6361-95BC-44F3-A487-C91C440018D8"), Content = "Info " + 5
},
};
var firstDocumentUp = entities.FirstOrDefault();
context.BulkInsertOrUpdate(entities, bulkConfig => bulkConfig.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);
}
[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 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 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 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);
}
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);
}
