Non-tracking ExecuteInsert

[roji]

EF Core 7.0 introduced ExecuteUpdate and ExecuteDelete, which are ways of updating and deleting database rows without passing through change tracking. For inserts, it's still necessary to go through the change tracker (Add and then SaveChanges).

There are two main advantages to this:

1. Ease-of-use. For the common task of adding a single entity, this would be a single line instead of two.
2. Performance. Not passing through the change tracker would remove that overhead.

Notes:

• We should probably allow a single call can insert multiple rows, since SQL INSERT allows this.
  • If so, it should still be kept distinct from #27333, which would be a bulk import mechanism suitable for a large number of rows (different mechanism).
  • As alternatives, note that it's still possible to use the change tracker to insert multiple rows. #10879 would also allow bundling an arbitrary set of query or ExecuteXXX operations in a single batch.
• Note that unlike ExecuteUpdate/Delete, ExecuteInsert doesn't need to return the number of rows affected (inserts either succeed or error), so the simple ExecuteInsert would return void/Task.
• Similar to ExecuteUpdate and ExecuteDelete, since ExecuteInsert bypasses change tracking, it would also not (by default) fetch back database-generated values.
• #29898 tracks adding RETURNING/OUTPUT support to ExecuteUpdate/Delete, which would be relevant here too. So some version of ExecuteInsert would return an IQueryable:

var dbGeneratedStuff = ctx.Blogs.ExecuteInsertReturning(blogs).Select(b => new { b.Id, b.Name}).ToArray();

At least in some DBs, returning an IQueryable allow embedding the insert in a CTE (WITH).

• Consider whether we want to allow arbitrary expressions (e.g. INSERT INTO foo (bar) VALUES (current_timestamp)). This can also be useful to call some database function over a user-provided parameter (e.g. PG full-text search insertion, How to manually update a tsvector column? npgsql/efcore.pg#2317 (comment)). Note that there wouldn't be any query root here; only totally static functions make sense in this context. This also may intersect with server-side value converters (Support server side value conversions #10861), where a database function needs to be called on user-supplied data before inserting.

[StrangeWill]

Ran into a strong need for this on generating bulk data -- for the time being I just used a context factory to get around the ever-growing state of the change tracker and the overhead induced by that (nasty workaround but works), I've learned on EF Core for doing some lighter ETL and data generation but for some hefty datasets the change tracker is the number 1 thing in the way of performance.

Only thing I'm somewhat hesitant about is dropping the return row count from the API, but as mentioned I can't really think of a use case that isn't served fine another way, and no weird "gotchas" there (triggers? I forget if those will show an inflated insert count if they do additional work -- I mean shows how much I read that value back out anyway)

As EF Core has continued to mature it has been exciting watching things that require third party tooling slip into the mainline lib like ExecuteDelete and ExecuteUpdate, this is another welcome addition to basically Making those third party libs no longer required. :D

[roji]

@StrangeWill thanks for the feedback. FWIW for bulk insert you're probably looking for #27333; this issue here would be able getting EF to send INSERTs, whereas #27333 would be about using e.g. SqlBulkCopy.

[Charlieface]

The primary benefit I see of this feature would be to do the equivalent of

INSERT INTO Table1 (Column1, Column2)
SELECT Column1, 'FixedValueHere'
FROM Table2;

There is no way to do this currently, and the alternatives mentioned do not allow selecting from an existing table directly on the server. SqlBulkCopy needs the data to come back to the client, likewise even with the EFCore.BulkExtensions project.

So you would do something like what we already have for ExecuteUpdate

context.Table2
  .ExecuteInsert(setters => setters
       .Set(t1 => t1.Column1, t2 => t2.Column1)
       .Set(t1 => t1.Column2, t2 => "FixedValueHere")
  )

to get the same result.

[roji]

@Charlieface INSERT ... SELECT ... is tracked by #27320, not by this issue, since you're fundamentally copying data between tables rather than from the client. And yes, when #27320 is implemented, it should allow you to generate the INSERT you wrote above.