private void NonEntityChildTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
var list = context.Animals.ToList();
context.BulkDelete(list);
var mammalList = new List <Mammal>()
{
new Mammal {
Name = "Cat"
},
new Mammal {
Name = "Dog"
}
};
var bulkConfig = new BulkConfig {
SetOutputIdentity = true
};
context.BulkInsert(mammalList, bulkConfig, type: typeof(Animal));
// TEST
Assert.Equal(2, context.Animals.ToList().Count());
}
private void RunDelete(bool isBulk)
{
using var context = new TestContext(GetOptions());
var entities = AllItemsQuery(context).ToList();
// ItemHistories will also be deleted because of Relationship - ItemId (Delete Rule: Cascade)
if (isBulk)
{
context.BulkDelete(entities, new BulkConfig()
{
UnderlyingConnection = GetUnderlyingConnection,
UnderlyingTransaction = GetUnderlyingTransaction
});
}
else
{
context.Items.RemoveRange(entities);
context.SaveChanges();
}
Assert.Equal(0, ItemsCountQuery(context));
Assert.Null(LastItemQuery(context));
// Resets AutoIncrement
context.Database.ExecuteSqlRaw("DBCC CHECKIDENT ('dbo.[" + nameof(Item) + "]', RESEED, 0);");
}
private void InsertAndUpdateWithCompositeKey(DbServer databaseType)
{
ContextUtil.DbServer = databaseType;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
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);
}
}
private void RunDelete(bool isBulkOperation)
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.Items.AsNoTracking().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 = context.Items.Count();
Item lastEntity = context.Items.LastOrDefault();
Assert.Equal(0, entitiesCount);
Assert.Null(lastEntity);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
// Resets AutoIncrement
context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT ('{nameof(Item)}', RESEED, 0);");
//context.Database.ExecuteSqlCommand($"TRUNCATE TABLE {nameof(Item)};"); // can NOT work when there is ForeignKey - ItemHistoryId
}
}
private void InsertWithDbComputedColumnsAndOutput()
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
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
}
);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.Documents.ToList();
Assert.Equal(EntitiesNumber, entities.Count());
context.BulkDelete(entities);
}
}
private void RunDelete(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)
{
context.BulkDelete(entities);
}
else
{
context.Items.RemoveRange(entities);
context.SaveChanges();
}
}
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);"); // can NOT use $"...{nameof(Item)..." because it gets parameterized
//context.Database.ExecuteSqlCommand($"TRUNCATE TABLE {nameof(Item)};"); // can NOT work when there is ForeignKey - ItemHistoryId
}
}
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);
}
}
}
private void InsertWithDiscriminatorShadow(DbServer databaseType)
{
ContextUtil.DbServer = databaseType;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.BulkDelete(context.Students.ToList());
}
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 = new List <Student>();
for (int i = 1; i <= EntitiesNumber / 2; i += 2)
{
entities.Add(new Student
{
Name = "name " + i,
Subject = "Math Upd"
});
}
context.Students.AddRange(entities); // adding to Context so that Shadow property 'Discriminator' gets set
context.BulkInsertOrUpdate(entities, new BulkConfig
{
UpdateByProperties = new List <string> {
nameof(Student.Name)
},
PropertiesToExclude = new List <string> {
nameof(Student.PersonId)
},
});
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.Students.ToList();
Assert.Equal(EntitiesNumber, entities.Count());
}
}
private void InsertWithValueConversion(DbServer databaseType)
{
ContextUtil.DbServer = databaseType;
var dateTime = DateTime.Today;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.BulkDelete(context.Infos.ToList());
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = new List <Info>();
for (int i = 1; i <= EntitiesNumber; i++)
{
entities.Add(new Info
{
Message = "Msg " + i,
ConvertedTime = dateTime,
InfoType = InfoType.InfoTypeA
});
}
context.BulkInsert(entities);
}
if (databaseType == DbServer.SqlServer)
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.Infos.ToList();
var entity = entities.FirstOrDefault();
Assert.Equal(entity.ConvertedTime, dateTime);
var conn = context.Database.GetDbConnection();
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (var command = conn.CreateCommand())
{
command.CommandText = $"SELECT TOP 1 * FROM {nameof(Info)} ORDER BY {nameof(Info.InfoId)} DESC";
var reader = command.ExecuteReader();
reader.Read();
var row = new Info()
{
ConvertedTime = reader.Field <DateTime>(nameof(Info.ConvertedTime))
};
Assert.Equal(row.ConvertedTime, dateTime.AddDays(1));
}
}
}
}
private void RunDelete(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
};
context.BulkDelete(entities, bulkConfig);
if (dbServer == DbServer.SqlServer)
{
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberInserted);
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated);
Assert.Equal(entities.Count, bulkConfig.StatsInfo.StatsNumberDeleted);
}
}
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 (dbServer == DbServer.SqlServer)
{
context.Database.ExecuteSqlRaw("DBCC CHECKIDENT ('dbo.[" + nameof(Item) + "]', RESEED, 0);"); // can NOT use $"...{nameof(Item)..." because it gets parameterized
}
else if (dbServer == DbServer.Sqlite)
{
context.Database.ExecuteSqlRaw("DELETE FROM sqlite_sequence WHERE name = 'Item';");
}
}
}
private void CustomPrecisionDateTimeTest()
{
ContextUtil.DbServer = DbServer.SQLServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.BulkDelete(context.Events.ToList());
var entities = new List <Event>();
for (int i = 1; i <= 10; i++)
{
var entity = new Event
{
Name = "Event " + i,
TimeCreated = DateTime.Now
};
var testTime = new DateTime(2020, 1, 1, 12, 45, 20, 324);
if (i == 1)
{
entity.TimeCreated = testTime.AddTicks(6387); // Ticks will be 3256387 when rounded to 3 digits: 326 ms
}
if (i == 2)
{
entity.TimeCreated = testTime.AddTicks(5000); // Ticks will be 3255000 when rounded to 3 digits: 326 ms (middle .5zeros goes to Upper)
}
var fullDateTimeFormat = "yyyy-MM-dd HH:mm:ss.fffffff";
entity.Description = entity.TimeCreated.ToString(fullDateTimeFormat);
entities.Add(entity);
}
bool useBulk = true;
if (useBulk)
{
context.BulkInsert(entities, b => b.DateTime2PrecisionForceRound = false);
}
else
{
context.AddRange(entities);
context.SaveChanges();
}
// TEST
Assert.Equal(3240000, context.Events.SingleOrDefault(a => a.Name == "Event 1").TimeCreated.Ticks % 10000000);
Assert.Equal(3240000, context.Events.SingleOrDefault(a => a.Name == "Event 2").TimeCreated.Ticks % 10000000);
}
private void GeometryColumnTest()
{
ContextUtil.DbServer = DbServer.SqlServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.BulkDelete(context.Addresses.ToList());
var entities = new List <Address> {
new Address {
Street = "Some Street nn",
Location = new Point(52, 13)
}
};
context.BulkInsertOrUpdate(entities);
}
private void InsertWithValueConversion()
{
var dateTime = new DateTime(2018, 1, 1);
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = new List <InfoLog>();
for (int i = 1; i <= EntitiesNumber; i++)
{
entities.Add(new InfoLog
{
Message = "Msg " + i,
ConvertedTime = dateTime
});
}
context.BulkInsert(entities);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = context.InfoLogs.ToList();
var entity = entities.FirstOrDefault();
Assert.Equal(entity.ConvertedTime, dateTime);
var conn = context.Database.GetDbConnection();
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (var command = conn.CreateCommand())
{
command.CommandText = $"SELECT TOP 1 * FROM {nameof(InfoLog)} ORDER BY {nameof(InfoLog.InfoLogId)} DESC";
var reader = command.ExecuteReader();
reader.Read();
var row = new InfoLog()
{
ConvertedTime = reader.Field <DateTime>(nameof(InfoLog.ConvertedTime))
};
Assert.Equal(row.ConvertedTime, dateTime.AddDays(1));
}
context.BulkDelete(entities);
}
}
private void RunDelete(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
};
context.BulkDelete(entities, bulkConfig);
if (dbServer == DbServer.SqlServer)
{
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberInserted);
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated);
Assert.Equal(entities.Count, bulkConfig.StatsInfo.StatsNumberDeleted);
}
}
else
{
context.Items.RemoveRange(entities);
context.SaveChanges();
}
// TEST
int entitiesCount = context.Items.Count();
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)),
};
context.Database.ExecuteSqlRaw(deleteTableSql);
}
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';");
}
}
}
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 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);
}
}
[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 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);
Item lastEntity = LastItemQuery(context);
Assert.Equal(0, entitiesCount);
Assert.Null(lastEntity);
}
// Resets AutoIncrement
using (var context = new TestContext(ContextUtil.GetOptions()))
{
if (databaseType == DbServer.SqlServer)
{
context.Database.ExecuteSqlCommand("DBCC CHECKIDENT ('dbo.[" + nameof(Item) + "]', RESEED, 0);"); // can NOT use $"...{nameof(Item)..." because it gets parameterized
}
else if (databaseType == DbServer.Sqlite)
{
context.Database.ExecuteSqlCommand("DELETE FROM sqlite_sequence WHERE name = 'Item';");
}
}
}
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);
}
}
//[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);
}
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
}