private void TableWithSpecialRowVersion()
{
ContextUtil.DbServer = DbServer.SQLServer;
using var context = new TestContext(ContextUtil.GetOptions());
context.AtypicalRowVersionEntities.BatchDelete();
context.AtypicalRowVersionConverterEntities.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());
var bulk2 = new List <AtypicalRowVersionConverterEntity>();
for (var i = 0; i < 100; i++)
{
bulk2.Add(new AtypicalRowVersionConverterEntity {
Id = Guid.NewGuid(), Name = $"Row {i}"
});
}
context.BulkInsertOrUpdate(bulk2);
Assert.Equal(bulk2.Count(), context.AtypicalRowVersionConverterEntities.Count());
}
private void PrivateKeyTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.BulkDelete(context.PrivateKeys.ToList());
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = new List <PrivateKey> {
new()
{
Name = "foo"
}
};
context.BulkInsertOrUpdate(entities);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var defaultValueTest = context.PrivateKeys.Single();
Assert.Equal("foo", defaultValueTest.Name);
}
}
}
private void GeographyAndGeometryArePersistedCorrectlyTest()
{
ContextUtil.DbServer = DbServer.SQLServer;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.BulkDelete(context.Addresses.ToList());
}
var point = new Point(52, 13);
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = new List <Address> {
new Address {
Street = "Some Street nn",
LocationGeography = point,
LocationGeometry = point
}
};
context.BulkInsertOrUpdate(entities);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var address = context.Addresses.Single();
Assert.Equal(point.X, address.LocationGeography.Coordinate.X);
Assert.Equal(point.Y, address.LocationGeography.Coordinate.Y);
Assert.Equal(point.X, address.LocationGeometry.Coordinate.X);
Assert.Equal(point.Y, address.LocationGeometry.Coordinate.Y);
}
}
private void HierarchyIdIsPersistedCorrectlyLargerHierarchyTest()
{
ContextUtil.DbServer = DbServer.SQLServer;
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.BulkDelete(context.Categories.ToList());
}
var nodeIdAsString = "/1.1/-2/3/4/5/";
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = new List <Category> {
new Category
{
Name = "Deep Element",
HierarchyDescription = HierarchyId.Parse(nodeIdAsString)
}
};
context.BulkInsertOrUpdate(entities);
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var category = context.Categories.Single();
Assert.Equal(nodeIdAsString, category.HierarchyDescription.ToString());
}
}
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
}/*,
* AuditExtended = new AuditExtended
* {
* CreatedBy = "UserS" + 1,
* Remark = "test",
* CreatedTime = DateTime.Now
* },
* AuditExtendedSecond = new AuditExtended
* {
* CreatedBy = "UserS" + 1,
* Remark = "sec",
* CreatedTime = DateTime.Now
* }*/
});
}
context.BulkInsertOrUpdate(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 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());
}
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 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 RunInsertOrUpdate(bool isBulk, DbServer dbServer)
{
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 = isBulk ? i : 0,
Name = "name InsertOrUpdate " + i,
Description = "info",
Quantity = i + 100,
Price = i / (i % 5 + 1),
TimeUpdated = dateTimeNow
});
}
if (isBulk)
{
var bulkConfig = new BulkConfig()
{
SetOutputIdentity = true, CalculateStats = true
};
context.BulkInsertOrUpdate(entities, bulkConfig, (a) => WriteProgress(a));
if (dbServer == DbServer.SqlServer)
{
Assert.Equal(1, bulkConfig.StatsInfo.StatsNumberInserted);
Assert.Equal(EntitiesNumber / 2 - 1, bulkConfig.StatsInfo.StatsNumberUpdated);
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberDeleted);
}
}
else
{
context.Items.Add(entities[entities.Count - 1]);
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(EntitiesNumber, entitiesCount);
Assert.NotNull(lastEntity);
Assert.Equal("name InsertOrUpdate " + EntitiesNumber, lastEntity.Name);
}
}
private void UpsertWithOutputSortTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
using var context = new TestContext(ContextUtil.GetOptions());
new EFCoreBatchTest().RunDeleteAll(dbServer);
var entitiesInitial = new List <Item>();
for (int i = 1; i <= 10; ++i)
{
var entity = new Item {
Name = "name " + i
};
entitiesInitial.Add(entity);
}
context.Items.AddRange(entitiesInitial);
context.SaveChanges();
var entities = new List <Item>()
{
new Item {
ItemId = 0, Name = "name " + 11 + " New"
},
new Item {
ItemId = 6, Name = "name " + 6 + " Updated"
},
new Item {
ItemId = 5, Name = "name " + 5 + " Updated"
},
new Item {
ItemId = 0, Name = "name " + 12 + " New"
}
};
context.BulkInsertOrUpdate(entities, new BulkConfig()
{
SetOutputIdentity = true
});
Assert.Equal(11, entities[0].ItemId);
Assert.Equal(6, entities[1].ItemId);
Assert.Equal(5, entities[2].ItemId);
Assert.Equal(12, entities[3].ItemId);
Assert.Equal("name " + 11 + " New", entities[0].Name);
Assert.Equal("name " + 6 + " Updated", entities[1].Name);
Assert.Equal("name " + 5 + " Updated", entities[2].Name);
Assert.Equal("name " + 12 + " New", entities[3].Name);
}
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);
}
[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 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)
{
var bulkConfig = new BulkConfig()
{
SetOutputIdentity = true, CalculateStats = true
};
context.BulkInsertOrUpdate(entities, bulkConfig, (a) => WriteProgress(a));
}
else
{
context.Items.Add(entities[entities.Count - 1]);
context.SaveChanges();
}
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
int entitiesCount = ItemsCountQuery(context);
Item lastEntity = LastItemQuery(context);
Assert.Equal(EntitiesNumber, entitiesCount);
Assert.NotNull(lastEntity);
Assert.Equal("name InsertOrUpdate " + EntitiesNumber, lastEntity.Name);
}
}
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);
}
public void UpsertOrderTest(DbServer dbServer)
{
ContextUtil.DbServer = dbServer;
new EFCoreBatchTest().RunDeleteAll(dbServer);
using var context = new TestContext(ContextUtil.GetOptions());
context.Items.Add(new Item {
Name = "name 1", Description = "info 1"
});
context.Items.Add(new Item {
Name = "name 2", Description = "info 2"
});
context.SaveChanges();
var entities = new List <Item>();
for (int i = 1; i <= 4; i++)
{
int j = i;
if (i == 1)
{
j = 2;
}
if (i == 2)
{
j = 1;
}
entities.Add(new Item
{
Name = "name " + j,
Description = "info x " + j,
});
}
context.BulkInsertOrUpdate(entities, new BulkConfig {
SetOutputIdentity = true, UpdateByProperties = new List <string> {
nameof(Item.Name)
}
});
Assert.Equal(2, entities[0].ItemId);
}
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 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 void RunInsertOrUpdate(bool isBulkOperation)
{
using (var context = new TestContext(GetContextOptions()))
{
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)
{
context.BulkInsertOrUpdate(entities);
}
else
{
context.Items.AddRange(entities);
context.SaveChanges();
}
}
using (var context = new TestContext(GetContextOptions()))
{
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 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
}
