private static List <Item> GetNewEntities(int count, string NameSufix)
{
var newEntities = new List <Item>();
var dateTimeNow = DateTime.Now;
for (int i = 1; i <= count; i += 1) // Insert 4000 new ones
{
newEntities.Add(new Item
{
//ItemId = i,
Name = "Name " + NameSufix + i,
Description = "info",
Quantity = i + 100,
Price = i / (i % 5 + 1),
TimeUpdated = dateTimeNow,
ItemHistories = new List <ItemHistory>()
{
new ItemHistory
{
//ItemId = i,
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.1"
},
new ItemHistory
{
//ItemId = i,
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.2"
}
}
});
}
return(newEntities);
}
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 = i,
Name = "name " + i,
Description = "info " + Guid.NewGuid().ToString().Substring(0, 3),
Quantity = i % 10,
Price = i / (i % 5 + 1),
TimeUpdated = DateTime.Now
});
}
if (isBulkOperation)
{
if (insertTo2Tables)
{
context.BulkInsert(entities, new BulkConfig {
PreserveInsertOrder = true, SetOutputIdentity = true, BatchSize = 4000
}, (a) => WriteProgress(a));
foreach (var entity in entities)
{
subEntities.Add(new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
ItemId = entity.ItemId,
Remark = "some more info"
});
}
context.BulkInsert(subEntities);
}
else
{
context.BulkInsert(entities);
}
}
else
{
context.Items.AddRange(entities);
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);
}
}
private void RunInsert(bool isBulkOperation)
{
using (var context = new TestContext(GetOptions()))
{
var entities = new List <Item>();
var subEntities = new List <ItemHistory>();
for (int i = 1; i < EntitiesNumber; i++)
{
var entity = 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,
ItemHistories = new List <ItemHistory>()
};
var subEntity1 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.1"
};
var subEntity2 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.2"
};
entity.ItemHistories.Add(subEntity1);
entity.ItemHistories.Add(subEntity2);
entities.Add(entity);
}
if (isBulkOperation)
{
using (var transaction = context.Database.BeginTransaction())
{
context.BulkInsert(
entities,
new BulkConfig
{
PreserveInsertOrder = true,
SetOutputIdentity = true,
BatchSize = 4000,
UseTempDB = true,
UnderlyingConnection = GetUnderlyingConnection,
UnderlyingTransaction = GetUnderlyingTransaction
}
);
foreach (var entity in entities)
{
foreach (var subEntity in entity.ItemHistories)
{
subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
context.BulkInsert(subEntities, new BulkConfig()
{
UnderlyingConnection = GetUnderlyingConnection, UnderlyingTransaction = GetUnderlyingTransaction
});
transaction.Commit();
}
}
else
{
context.Items.AddRange(entities);
context.SaveChanges();
}
}
using (var context = new TestContext(GetOptions()))
{
int entitiesCount = ItemsCountQuery(context);
Item lastEntity = LastItemQuery(context);
Assert.Equal(EntitiesNumber - 1, entitiesCount);
Assert.NotNull(lastEntity);
Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name);
}
}
private void RunInsert(bool isBulkOperation)
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = new List <Item>();
var subEntities = new List <ItemHistory>();
for (int i = 1, j = -(EntitiesNumber - 1); i < EntitiesNumber; i++, j++)
{
var entity = new Item
{
ItemId = isBulkOperation ? j : 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>()
};
var subEntity1 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.1"
};
var subEntity2 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.2"
};
entity.ItemHistories.Add(subEntity1);
entity.ItemHistories.Add(subEntity2);
entities.Add(entity);
}
if (isBulkOperation)
{
if (ContextUtil.DbServer == DbServer.SqlServer)
{
using (var transaction = context.Database.BeginTransaction())
{
context.BulkInsert(
entities,
new BulkConfig
{
PreserveInsertOrder = true,
SetOutputIdentity = true,
BatchSize = 4000,
UseTempDB = true
},
(a) => WriteProgress(a)
);
foreach (var entity in entities)
{
foreach (var subEntity in entity.ItemHistories)
{
subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
context.BulkInsert(subEntities);
transaction.Commit();
}
}
else if (ContextUtil.DbServer == DbServer.Sqlite)
{
using (var connection = (SqliteConnection)context.Database.GetDbConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var bulkConfig = new BulkConfig()
{
SqliteConnection = connection,
SqliteTransaction = transaction,
SetOutputIdentity = true,
};
context.BulkInsert(entities, bulkConfig);
foreach (var entity in entities)
{
foreach (var subEntity in entity.ItemHistories)
{
subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
bulkConfig.SetOutputIdentity = false;
context.BulkInsert(subEntities, bulkConfig);
transaction.Commit();
}
}
}
}
else
{
context.Items.AddRange(entities);
context.SaveChanges();
}
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var temp = context.ItemHistories.FirstOrDefault();
int entitiesCount = ItemsCountQuery(context);
Item lastEntity = LastItemQuery(context);
Assert.Equal(EntitiesNumber - 1, entitiesCount);
Assert.NotNull(lastEntity);
Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name);
}
}
private async Task RunInsertAsync(bool isBulkOperation)
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
var entities = new List <Item>();
var subEntities = new List <ItemHistory>();
for (int i = 1; i < EntitiesNumber; i++)
{
var entity = 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,
ItemHistories = new List <ItemHistory>()
};
var subEntity1 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.1"
};
var subEntity2 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.2"
};
entity.ItemHistories.Add(subEntity1);
entity.ItemHistories.Add(subEntity2);
entities.Add(entity);
}
if (isBulkOperation)
{
if (ContextUtil.DbServer == DbServer.SqlServer)
{
using (var transaction = await context.Database.BeginTransactionAsync())
{
var bulkConfig = new BulkConfig
{
PreserveInsertOrder = true,
SetOutputIdentity = true,
BatchSize = 4000,
CalculateStats = true
};
await context.BulkInsertAsync(entities, bulkConfig);
Assert.Equal(EntitiesNumber - 1, bulkConfig.StatsInfo.StatsNumberInserted);
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated);
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberDeleted);
foreach (var entity in entities)
{
foreach (var subEntity in entity.ItemHistories)
{
subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
await context.BulkInsertAsync(subEntities);
transaction.Commit();
}
}
else if (ContextUtil.DbServer == DbServer.Sqlite)
{
using (var transaction = context.Database.BeginTransaction())
{
var bulkConfig = new BulkConfig()
{
SetOutputIdentity = true,
};
await context.BulkInsertAsync(entities, bulkConfig);
foreach (var entity in entities)
{
foreach (var subEntity in entity.ItemHistories)
{
subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
await context.BulkInsertAsync(subEntities, bulkConfig);
transaction.Commit();
}
}
}
else
{
await context.Items.AddRangeAsync(entities);
await context.SaveChangesAsync();
}
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
//int entitiesCount = ItemsCountQuery(context);
int entitiesCount = await context.Items.CountAsync();
//Item lastEntity = LastItemQuery(context);
Item lastEntity = context.Items.OrderByDescending(a => a.ItemId).FirstOrDefault();
Assert.Equal(EntitiesNumber - 1, entitiesCount);
Assert.NotNull(lastEntity);
Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name);
}
}
private async Task RunInsertAsync(bool isBulkOperation, bool insertTo2Tables)
{
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 = i,
Name = "name " + i,
Description = "info " + Guid.NewGuid().ToString().Substring(0, 3),
Quantity = i % 10,
Price = i / (i % 5 + 1),
TimeUpdated = DateTime.Now,
});
}
if (isBulkOperation)
{
if (insertTo2Tables)
{
using (var transaction = await context.Database.BeginTransactionAsync())
{
await context.BulkInsertAsync(entities, new BulkConfig { PreserveInsertOrder = true, SetOutputIdentity = true, BatchSize = 4000 });
foreach (var entity in entities)
{
subEntities.Add(new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
ItemId = entity.ItemId,
Remark = "some more info"
});
}
await context.BulkInsertAsync(subEntities);
transaction.Commit();
}
}
else
{
await context.BulkInsertAsync(entities);
}
}
else
{
await context.Items.AddRangeAsync(entities);
await context.SaveChangesAsync();
}
}
using (var context = new TestContext(ContextUtil.GetOptions()))
{
int entitiesCount = ItemsCountQuery(context);
Item lastEntity = LastItemQuery(context);
Assert.Equal(EntitiesNumber - 1, entitiesCount);
Assert.NotNull(lastEntity);
Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name);
}
}
private void RunInsert(bool isBulk)
{
using var context = new TestContext(ContextUtil.GetOptions());
var entities = new List <Item>();
var subEntities = new List <ItemHistory>();
for (int i = 1, j = -(EntitiesNumber - 1); i < EntitiesNumber; i++, j++)
{
var entity = new Item
{
ItemId = 0, //isBulk ? j : 0, // no longer used since order(Identity temporary filled with negative values from -N to -1) is set automaticaly with default config PreserveInsertOrder=TRUE
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>()
};
var subEntity1 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.1"
};
var subEntity2 = new ItemHistory
{
ItemHistoryId = SeqGuid.Create(),
Remark = $"some more info {i}.2"
};
entity.ItemHistories.Add(subEntity1);
entity.ItemHistories.Add(subEntity2);
entities.Add(entity);
}
if (isBulk)
{
if (ContextUtil.DbServer == DbServer.SqlServer)
{
using var transaction = context.Database.BeginTransaction();
var bulkConfig = new BulkConfig
{
//PreserveInsertOrder = true, // true is default
SetOutputIdentity = true,
BatchSize = 4000,
UseTempDB = true,
CalculateStats = true
};
context.BulkInsert(entities, bulkConfig, (a) => WriteProgress(a));
Assert.Equal(EntitiesNumber - 1, bulkConfig.StatsInfo.StatsNumberInserted);
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberUpdated);
Assert.Equal(0, bulkConfig.StatsInfo.StatsNumberDeleted);
foreach (var entity in entities)
{
foreach (var subEntity in entity.ItemHistories)
{
subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
context.BulkInsert(subEntities);
transaction.Commit();
}
else if (ContextUtil.DbServer == DbServer.Sqlite)
{
using var transaction = context.Database.BeginTransaction();
var bulkConfig = new BulkConfig()
{
SetOutputIdentity = true
};
context.BulkInsert(entities, bulkConfig);
foreach (var entity in entities)
{
foreach (var subEntity in entity.ItemHistories)
{
subEntity.ItemId = entity.ItemId; // setting FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
bulkConfig.SetOutputIdentity = false;
context.BulkInsert(subEntities, bulkConfig);
transaction.Commit();
}
}
else
{
context.Items.AddRange(entities);
context.SaveChanges();
}
// TEST
int entitiesCount = ItemsCountQuery(context);
Item lastEntity = context.Items.OrderByDescending(a => a.ItemId).FirstOrDefault();
Assert.Equal(EntitiesNumber - 1, entitiesCount);
Assert.NotNull(lastEntity);
Assert.Equal("name " + (EntitiesNumber - 1), lastEntity.Name);
}