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, https://github.com/npgsql/efcore.pg/issues/2317#issuecomment-1443402048). 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 (#10861), where a database function needs to be called on user-supplied data before inserting.

[wdhenrik]

I need to perform a Insert based on the result of a Select statement. The Insert needs to be associated with the first record returned in the Select statement and use the contents of the record to populate columns in the Insert.

For example, this SQL query would set the Work.Status to In Progress of the first job with a Work.Status of New.

DECLARE @InsertStatus VARCHAR(50) = 'In Progress';
INSERT INTO dbo.Work (JobId,@InsertStatus)
SELECT TOP 1 JobId FROM dbo.Work WHERE Status = 'New';

If the Select and Insert are decoupled, then the 'first' record may change between the Select and Insert, leading to concurrency issues.

Is there any way to do this in EFC7?

[roji]

@wdhenrik as I wrote on your other issue (#29894), that's #27320, which has nothing to do with this issue. #27320 is about inserting the result of a query (INSERT ... SELECT), whereas this is about inserting data from the client.

[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.

[iXab3r]

Hey! Are there any information about timelines on this? Change tracker in case of large entities causes way too much overhead

[roji]

This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 8.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.