public async Task Insert_RecordIsInserted()
{
const string streetName = "Just inserted street name";
await DeleteIfExistsByName(streetName);
var address = new Address
{
StreetName = streetName,
HouseNumber = 320,
PostalCode = "Some postal code",
ExtraHouseNumber = 1456,
Duration = new NpgsqlTypes.NpgsqlRange <DateTime>(DateTime.Now, DateTime.Now)
};
var bulkImport = new NpgsqlBulkUploader(CreateContext());
bulkImport.Insert(new[] { address });
var assertContext = CreateContext();
var actualAddress = await assertContext.Addresses
.SingleAsync(x => x.StreetName == streetName);
using (assertContext)
{
actualAddress.StreetName.Should().Be(address.StreetName);
actualAddress.HouseNumber.Should().Be(address.HouseNumber);
actualAddress.PostalCode.Should().Be(address.PostalCode);
actualAddress.ExtraHouseNumber.Should().Be(address.ExtraHouseNumber);
actualAddress.Duration.ToString().Should().Be(address.Duration.ToString());
}
}
public override void Save(IList <RowData> rowsData)
{
using (EventLogContext _context = EventLogContext.Create(_databaseOptions, _databaseActions))
{
if (_maxPeriodRowData == DateTime.MinValue)
{
_maxPeriodRowData = _context.GetRowsDataMaxPeriod(_system);
}
List <Database.Models.RowData> newEntities = new List <Database.Models.RowData>();
foreach (var itemRow in rowsData)
{
if (itemRow == null)
{
continue;
}
if (_maxPeriodRowData != DateTime.MinValue && itemRow.Period.DateTime <= _maxPeriodRowData)
{
if (_context.RowDataExistOnDatabase(_system, itemRow))
{
continue;
}
}
newEntities.Add(new Database.Models.RowData(_system, itemRow, _referencesCache));
}
var bulkUploader = new NpgsqlBulkUploader(_context);
bulkUploader.Insert(newEntities);
}
}
public static void BulkUpdate <TEntity>(this DbContext dbContext, IEnumerable <TEntity> entities)
where TEntity : Entity
{
var bulk = new NpgsqlBulkUploader(dbContext);
bulk.Update(entities);
}
static void TestViaInterfaceCase <T>(IEnumerable <T> data, DbContext context) where T : IHasId
{
var uploader = new NpgsqlBulkUploader(context);
var properties = data
.First()
.GetType()
.GetProperties()
.Where(x => x.GetCustomAttribute <ColumnAttribute>() != null)
.ToArray();
uploader.Insert(data, InsertConflictAction.UpdateProperty <T>(x => x.AddressId, properties));
}
public void CreateEntityInfoTest1()
{
NpgsqlBulkUploader.RelationalHelper = new TestRelationHelper(new Dictionary <Type, List <ColumnInfo> >()
{
{ typeof(Entity1), new List <ColumnInfo>()
{
new ColumnInfo()
{
ColumnName = nameof(Entity1.Id),
ColumnType = "uuid"
},
new ColumnInfo()
{
ColumnName = nameof(Entity1.Number),
ColumnType = "int"
},
new ColumnInfo()
{
ColumnName = nameof(Entity1.Value),
ColumnType = "text"
}
} }
});
var ctx1 = new TestContext <Tuple <int> >((builder) =>
{
builder.Entity <Entity1>().HasKey(x => x.Id);
builder.Entity <Entity1>().Property(x => x.Id).HasComputedColumnSql("SQL");
});
var uploader = new NpgsqlBulkUploader(ctx1);
var info = uploader.CreateEntityInfo <Entity1>();
// When HasComputedColumnSql used -> should not have Id in Insert and Update
Assert.DoesNotContain(info.InsertClientDataInfos, x => x.Property.Name == "Id");
// Key is needed for Update
Assert.Contains(info.UpdateClientDataWithKeysInfos, x => x.Property.Name == "Id");
var ctx2 = new TestContext <Tuple <long> >((builder) =>
{
builder.Entity <Entity1>().HasKey(x => x.Id);
});
uploader = new NpgsqlBulkUploader(ctx2);
info = uploader.CreateEntityInfo <Entity1>();
// When No HasComputedColumnSql used -> should have Id in Insert and Update
Assert.Contains(info.InsertClientDataInfos, x => x.Property.Name == "Id");
Assert.Contains(info.UpdateClientDataWithKeysInfos, x => x.Property.Name == "Id");
}
public async Task <int> Ingest(IEnumerable <T> source, IngestMode ingestMode, CancellationToken cancel)
{
var list = source.GroupBy(s => pk.PropertyInfo.GetValue(s).ToString()).Select(g => g.First()).ToList();
var count = list.Count();
logger.LogInformation($"adding {typeof(T).Name}...");
var stopwatch = Stopwatch.StartNew();
var uploader = new NpgsqlBulkUploader(context);
await uploader.InsertAsync(list);
logger.LogInformation($"it took {stopwatch.Elapsed} to injest {count} records");
return(count);
}
static void TestDerived(BulkContext context)
{
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address2EF()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange<DateTime>(DateTime.Now, DateTime.Now),
LocalizedName = streets[i % streets.Length],
Index2 = i
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Derived: dynamic solution inserted {data.Count} records for {sw.Elapsed }");
// Trace.Assert(context.Addresses.Count() == data.Count);
uploader.Insert(data.Take(100), InsertConflictAction.UpdateProperty<Address2EF>(
x => x.AddressId, x => x.PostalCode));
uploader.Insert(data.Take(100), InsertConflictAction.UpdateProperty<Address2EF>(
x => x.AddressId, x => x.Index2));
Console.WriteLine($"Derived: derived objects are inserted");
var data2 = Enumerable.Range(0, 100000)
.Select((x, i) => new Address2EF()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange<DateTime>(DateTime.Now, DateTime.Now),
LocalizedName = streets[i % streets.Length],
Index2 = i
}).ToList();
uploader.Update(data2);
}
static void TestInsertPartialUpdateAndIndsert(BulkContext context, List <Address> data)
{
Console.WriteLine("");
Console.WriteLine("TestInsertPartialUpdateAndIndsert...");
Console.WriteLine("");
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
var uploader = new NpgsqlBulkUploader(context);
uploader.Insert(data.Take(1000));
uploader.Update(data.Take(1000), x => x.Duration, x => x.ExtraHouseNumber);
uploader.Insert(data.Skip(1000).Take(1000));
}
static async Task TestAsync(BulkContext context, NpgsqlBulkUploader uploader, List <Address> data)
{
Console.WriteLine("");
Console.WriteLine("ASYNC version...");
Console.WriteLine("");
var sw = Stopwatch.StartNew();
await uploader.InsertAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
Trace.Assert(context.Addresses.Count() == data.Count);
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
await uploader.UpdateAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
await uploader.ImportAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
await uploader.InsertAsync(data);
}
Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
await uploader.UpdateAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
}
static void Main(string[] args)
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var optionsBuilder = new DbContextOptionsBuilder <BulkContext>();
optionsBuilder.UseNpgsql("server=localhost;user id=postgres;password=qwerty;database=copy");
var context = new BulkContext(optionsBuilder.Options);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address2()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length]
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
Console.ReadLine();
}
public async Task Update_RecordsAreUpdated()
{
const string initialStreetName = "Initial Street";
const string finalStreetName = "Final Street";
const string initialPostalCode = "Initial Postal Code";
const string finalPostalCode = "Final Postal Code";
const int addressId = 200000;
await DeleteIfExists(addressId);
var address = new Address
{
AddressId = addressId,
StreetName = initialStreetName,
HouseNumber = 320,
PostalCode = initialPostalCode,
ExtraHouseNumber = 1456,
Duration = new NpgsqlTypes.NpgsqlRange <DateTime>(DateTime.Now, DateTime.Now)
};
await AddToDb(address);
address.StreetName = finalStreetName;
address.PostalCode = finalPostalCode;
var bulkImport = new NpgsqlBulkUploader(CreateContext());
bulkImport.Update(new[] { address });
var actualAddress = await CreateContext().Addresses
.SingleAsync(x => x.AddressId == addressId);
actualAddress.StreetName.Should().BeEquivalentTo(finalStreetName);
actualAddress.PostalCode.Should().BeEquivalentTo(finalPostalCode);
}
static void Main(string[] args)
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var optionsBuilder = new DbContextOptionsBuilder <BulkContext>();
optionsBuilder.UseNpgsql(Configuration.ConnectionString);
var context = new BulkContext(optionsBuilder.Options);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange <DateTime>(DateTime.Now, DateTime.Now)
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Attach(data[0]);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
Trace.Assert(context.Addresses.Count() == data.Count);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
TestViaInterfaceCase(data, context);
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
uploader.Import(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope())
{
uploader.Insert(data);
}
Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
TestAsync(context, uploader, data).Wait();
Console.ReadLine();
}
public static async Task BulkUpdateAsync <TEntity>(this DbContext dbContext, IEnumerable <TEntity> entities)
where TEntity : Entity
{
var bulk = new NpgsqlBulkUploader(dbContext);
await bulk.UpdateAsync(entities);
}
static BenchmarkContext CreateContextAndSeed()
{
var ctx = CreateContext();
if (!ctx.Prices.Any())
{
ctx.Database.SetCommandTimeout(TimeSpan.FromHours(1));
Console.WriteLine("Seeding.. This will take some TIME! Better not to interrupt this process..");
var securitesCount = 4_000;
var priceSources = new List <PriceSource>()
{
new PriceSource()
{
PriceSourceId = 1, Name = "Default"
},
new PriceSource()
{
PriceSourceId = 2, Name = "ThirdParty"
},
new PriceSource()
{
PriceSourceId = 3, Name = "User"
},
new PriceSource()
{
PriceSourceId = 4, Name = "Counterparty"
}
};
var securities = new List <Security>();
for (var i = 1; i <= securitesCount; i++)
{
securities.Add(new Security()
{
SecurityId = i, Ticker = $"Ticker_{i}", Description = $"Security of ticker_{i}"
});
}
var uploader = new NpgsqlBulkUploader(ctx);
uploader.Insert(priceSources);
uploader.Insert(securities);
var rnd = new Random();
var prices = new List <Price>();
var tradedOn = new DateTime(2018, 01, 01);
var tradedOnMin = new DateTime(1988, 01, 01);
for (var i = 1; i <= 10_000_000; i++)
{
prices.Add(new Price()
{
PriceId = i,
PriceSourceId = (i % priceSources.Count) + 1,
TradedOn = tradedOn,
SecurityId = (i % securitesCount) + 1,
ClosePrice = 100 + rnd.Next(20),
OpenPrice = 100 + rnd.Next(20)
});
tradedOn = tradedOn.AddDays(-1);
if (tradedOn < tradedOnMin)
{
tradedOn = new DateTime(2018, 01, 01);
}
if (prices.Count % 10_000 == 0)
{
uploader.Insert(prices);
prices.Clear();
}
}
uploader.Insert(prices);
prices.Clear();
Console.WriteLine("Seeding is finished..");
}
return(ctx);
}
static void Main(string[] args)
{
var optionsBuilder = new DbContextOptionsBuilder<BulkContext>();
optionsBuilder.UseNpgsql(Configuration.ConnectionString);
var context = new BulkContext(optionsBuilder.Options);
context.Database.Migrate();
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange<DateTime>(DateTime.Now, DateTime.Now),
AddressType = i % 2 == 0 ? AddressType.Type1 : AddressType.Type2,
AddressTypeInt = i % 2 == 0 ? AddressTypeInt.First : AddressTypeInt.Second,
UnmappedEnum = i % 2 == 0 ? UnmappedEnum.A : UnmappedEnum.B,
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Attach(data[0]);
data[0].AddressId = 11;
data[1].CreatedAt = DateTime.Now;
//context.Add(data[0]);
//context.Add(data[1]);
//context.SaveChanges();
// data.ForEach(x => x.StreetName = null);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
// CompareValues(data, new BulkContext(optionsBuilder.Options));
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
TestViaInterfaceCase(data, context);
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
uploader.Import(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope())
{
uploader.Insert(data);
}
// Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
TestAsync(context, uploader, data).Wait();
TestDerived(context);
Console.WriteLine();
Console.WriteLine("Time to press any key...");
Console.ReadLine();
}