Support for Temporary Tables in EF Core and Bulk operations

[jstachera]

I am trying to do massive update based on primary key for around 2,000,000 of records.
The Id's which needs to be updated are stored in the file.
I did not find any easy way with EF Core to do this update without round-trip (client-db).

The current solution was to have simple Id table:
create table if not exists "ValidIdentifiers" ( "Id" integer not null );
before the update that table is cleaned by:
await DbContext.Database.ExecuteSqlRawAsync("TRUNCATE TABLE \"ValidIdentifiers\"");

and I am using third part tool (https://entityframework-extensions.net/bulk-insert) to insert 2M records:
await DbContext.BulkInsertAsync(ids, options => { options.AutoMapOutputDirection = false; options.UseTableLock = true; });

To do update for those 2M records I use:
DbContext.Database.ExecuteSqlRawAsync($"UPDATE \"{some_table}\" SET \"DeleteDate\" = current_date WHERE \"DeleteDate\" is null AND date(\"UploadDate\") < date(current_date) AND \"Id\" in (SELECT \"Id\" FROM \"{some_table}\" EXCEPT SELECT \"Id\" FROM \"ValidIdentifiers\")");

Firstly It would be good to have ability to create temporary tables.
Secondly there is a need for bulk operations in EF Core (INSERT/UPDATE/DELETE).

[roji]

Unlike in EF6, EF Core's SaveChanges does not do a roundtrip for each insert when you call SaveChanges - updates are batched. Mass insertion is still going to be faster using SqlBulkCopy (which is what EF Extensions say they use), but have you measured the throughput difference to see if it's significant for you?

Bulk updates is covered by #795.

[jstachera]

@roji I will measure the times. I am using the Postgresql, thus I guess there is no SqlBulkCopy, AFAIK this is SQL Server function.
I am using this provider: https://www.npgsql.org/efcore/.

[roji]

@jstachera the Npgsql ADO provider does have a high-perf bulk copy (not the same API as SqlBulkCopy) - EF Core doesn't expose it (just like it doesn't expose SQL Server's SqlBulkCopy).

Using the lower-level bulk copy API means you have to bypass EF Core. I'd recommend testing if the perf difference between that and EF Core's SaveChanges is really important enough to do that.

[sdanyliv]

@jstachera, Well there is another extension https://github.com/linq2db/linq2db.EntityFrameworkCore
You can create temporary tables without worring about mapping:

class IdHolder
{
    public int Id { get; set; }
}

using var db = ctx.CreateLinqToDBConnection();
using var validIdentifiers = await db.CreateTempTableAsync<IdHolder>(items);

var queryToUpdate =
   from e in db.GetTable<SomeTable>()
   join v in validIdentifiers on e.Id equals v.Id into gj
   from v in gj.DefaultIfEmpty()
   where (int?)v.Id == null && e.DeleteDate == null 
     && e.UploadDate < Sql.CurrentTimestamp
   select e;

await queryToUpdate
   .Set(e => e.DeleteDate, e => Sql.CurrentTimestamp)
   .UpdateAsync();

It also uses Npgsql COPY realisation, if it is possible.

[roji]

If that extension works for you, that's great. I don't think there's anything else to track here on the EF Core side.

[AndriySvyryd]

Duplicate of #15059