private void RunSaveChangesOnInsertAndUpdate()
{
using var context = new TestContext(ContextUtil.GetOptions());
var loadedEntites = context.Items.Include(a => a.ItemHistories).Where(a => a.ItemId <= 3000).ToList(); // load first 3000 entities
var existingEntites = loadedEntites.Where(a => a.ItemId <= 2000).ToList(); // take first 2000 of loaded entities and update them
foreach (var existingEntity in existingEntites)
{
existingEntity.Description += " UPDATED";
existingEntity.ItemHistories.FirstOrDefault().Remark += " UPD";
}
var newEntities = GetNewEntities(4000, "NEW ");
context.Items.AddRange(newEntities);
context.BulkSaveChanges();
// Validate Test
int entitiesCount = context.Items.Count();
Item firstEntity = context.Items.SingleOrDefault(a => a.ItemId == 1);
Assert.Equal(9000, entitiesCount);
Assert.EndsWith(" UPDATED", firstEntity.Description);
}
private void RunSaveChangesOnInsert()
{
using var context = new TestContext(ContextUtil.GetOptions());
var newEntities = GetNewEntities(5000, "");
context.Items.AddRange(newEntities);
context.BulkSaveChanges();
// Validate Test
int entitiesCount = context.Items.Count();
Item firstEntity = context.Items.SingleOrDefault(a => a.ItemId == 1);
Assert.Equal(5000, entitiesCount);
Assert.Equal("Name 1", firstEntity.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
}