static void TestViaInterfaceCase <T>(IEnumerable <T> data, DbContext context) where T : IHasId
{
var uploader = new NpgsqlBulkUploader(context);
var properties = data
.First()
.GetType()
.GetProperties()
.ToArray();
uploader.Insert(data, InsertConflictAction.UpdateProperty <T>(x => x.AddressId, properties));
}
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)");
// partial update 1
sw = Stopwatch.StartNew();
data.ForEach(x => x.StreetName = x.StreetName + " upd");
await uploader.UpdateAsync(data, x => x.StreetName);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (partial 1) Async");
}
static void TestInheritanceCase()
{
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 context = new BulkContext("DefaultConnection");
context.Database.ExecuteSqlCommand("DELETE FROM addresses2");
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],
LocalizedName = streets[i % streets.Length],
Index1 = i,
Index2 = i
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
// checking consitency
foreach (var addr in data)
{
if (addr.Index1 != addr.Index2)
{
Console.WriteLine($"INCONSITENT! Id: {addr.AddressId}/{addr.Address2Id}, {addr.Index1} != {addr.Index2}");
}
}
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 }");
}
public void InitBuilder(
EntityInfo entityInfo,
Func <Type, NpgsqlDataReader, string, object> readerFunc)
{
var name = NpgsqlBulkUploader.GetUniqueName(typeof(T).Name);
assemblyName = new AssemblyName {
Name = name
};
assemblyBuilder = AssemblyBuilder.DefineDynamicAssembly(
assemblyName, AssemblyBuilderAccess.Run);
moduleBuilder = assemblyBuilder.DefineDynamicModule(name);
typeBuilder = moduleBuilder.DefineType(name, TypeAttributes.Public);
GenerateWriteCode(entityInfo, readerFunc);
}
static void TestPlainCase()
{
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 context = new BulkContext("DefaultConnection");
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]
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
var sw = Stopwatch.StartNew();
HardcodedInsert(data, context);
sw.Stop();
Console.WriteLine($"Hardcoded solution inserted {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
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 }");
}
public static List <T> BulkSelect <T, TKey>(
this IQueryable <T> source,
Expression <Func <T, TKey> > keyExpression,
IEnumerable <TKey> keyData)
{
EnsureNoNavigationProperties(keyExpression);
BulkSelectInterceptor.StartInterception();
var keyDataTable = $"_schema_{DateTime.Now.Ticks}";
var schemaQuery = source.Select(keyExpression);
var schemaSql = $"CREATE TEMP TABLE {keyDataTable} ON COMMIT DROP AS ({schemaQuery} LIMIT 0)";
var context = NpgsqlHelper.GetContextFromQuery(source);
var conn = NpgsqlHelper.GetNpgsqlConnection(context);
var localTr = NpgsqlHelper.EnsureOrStartTransaction(context);
try
{
context.Database.ExecuteSqlCommand(schemaSql);
var columnsInfo = NpgsqlHelper.GetColumnsInfo(context, keyDataTable);
var propsMap = GetPropertiesMap(
((IObjectContextAdapter)context).ObjectContext,
schemaQuery.Expression,
typeof(TKey));
var mapsInfo = new List <MappingInfo>();
foreach (var propMap in propsMap)
{
var cinfo = columnsInfo[propMap.Item2];
mapsInfo.Add(new MappingInfo()
{
Property = propMap.Item1,
ColumnInfo = cinfo,
NpgsqlType = NpgsqlBulkUploader.GetNpgsqlType(cinfo)
});
}
var columnsCsv = string.Join(", ",
mapsInfo.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
var copySql = $"COPY {keyDataTable} ({columnsCsv}) FROM STDIN (FORMAT BINARY)";
using (var importer = conn.BeginBinaryImport(copySql))
{
foreach (var kd in keyData)
{
importer.StartRow();
foreach (var kp in mapsInfo)
{
importer.Write(kp.Property.GetValue(kd), kp.NpgsqlType);
}
}
}
var whereSql = string.Join(" AND ",
mapsInfo.Select(x => $"source.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}" +
$" = {keyDataTable}.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}"));
var selectSql = $"SELECT source.* FROM ({source}) as source\n" +
$"JOIN {keyDataTable} ON {whereSql}";
BulkSelectInterceptor.SetReplaceQuery(source.ToString(), selectSql);
var result = source.ToList();
localTr?.Commit();
return(result);
}
catch
{
localTr?.Rollback();
throw;
}
finally
{
BulkSelectInterceptor.StopInterception();
}
}
static void TestPlainCase()
{
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 addressTypes = new AddressType?[] { null, AddressType.Type1, AddressType.Type2 };
var dates = new DateTime?[] { null, DateTime.Now };
var guids = new Guid?[] { null, Guid.Empty };
var decimals = new decimal?[] { null, decimal.Zero };
var context = new BulkContext("DefaultConnection");
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],
Type = addressTypes[i % addressTypes.Length],
Date = dates[i % dates.Length],
Guid = guids[i % guids.Length],
Dec = decimals[i % decimals.Length]
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
var sw = Stopwatch.StartNew();
HardcodedInsert(data, context);
sw.Stop();
Console.WriteLine($"Hardcoded solution inserted {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
sw = Stopwatch.StartNew();
uploader.Insert(data, InsertConflictAction.UpdateProperty <Address>(x => x.AddressId, x => x.Dec));
uploader.Insert(data, InsertConflictAction.DoNothing());
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 }");
TestViaInterfaceCase(data, context);
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();
}
