Thinktecture.EntityFrameworkCore
Thinktecture.EntityFrameworkCore copied to clipboard
PawelGerr
Multiple writes in temp tables
Hello, is there any way to write into the same temp table multiple times?
For example, I would like to bulk insert different batches of data (potentially large, i do not want to concat them in memory) into the same temp table, then use the table to perform queries, and finally dispose it.
Specifically, I use Sqlite as a containter to move data between different MS SqlServer databases in different hosts, and I need a way to load and write potentially big tables without worrying about the memory occupation, so this kind of functionality would be extremely useful.
Thank you!
Currently, there is no api to provide the name of existing (temp) table to methods like BulkInsertAsync.
I've added multiple overloads of BulkInsert*IntoTempTable in version 8.4.0/9.2.0
// creates a new temp table and inserts 1 record
await using var tempTable = await ActDbContext.BulkInsertIntoTempTableAsync(new List<CustomTempTable> { new(1, "value") });
// insert another record into exising `tempTable`
await ActDbContext.BulkInsertIntoTempTableAsync(new List<CustomTempTable> { new(2, "value2") }, tempTable);
IQueryable<CustomTempTable> query = tempTable.Query;
var result = query.Select( ... );
mean, I am using something like this code
// huge data from an unpivot operation
IQueryable hugeData = GetCustomTempComponents();
// create temp table
var tempTable = await db.CreateTempTableAsync(CreateOptions);
// insert in batches of 10000
foreach (var batch in hugeData.Batch(10000))
{
await db.BulkInsertIntoTempTableAsync(batch.ToList(), tempTable, insertOptions);
}
var query = tempTable.Query; //??? ITempTableReference doesn't have a Query property
Use BulkInsertIntoTempTableAsync instead of CreateTempTableAsync.
The initial call can be made with first batch or an empty collection.
await using var tempTable = await db.BulkInsertIntoTempTableAsync(Array.Empty<MyEntity>(), insertOptions, cancellationToken);
foreach (var batch in hugeData.Batch(10000))
{
await db.BulkInsertIntoTempTableAsync(batch, tempTable, insertOptions, cancellationToken);
}
IQueryable<MyEntity> query = tempTable.Query;