Use case: Advanced Bulk Operations

[mikependon]

This use case covers:

• Bulk Merge
• Bulk Delete
• Bulk Update

[mikependon]

This is a very important scenario. I would humbly say, the Bulk Delete, Bulk Merge, Bulk Update must be presented as well :)

[Grauenwolf]

24 covers Bulk Insert.

Let's rename this one "Advanced Bulk Operations" to cover the other scenarios.

[FransBouma]

This is formulated too vaguely: are these operations on sets inserted in a temptable first (merge suggests that) or is e.g. bulk update a single update statement that affects multiple rows? (same for delete)

[mikependon]

For BulkInsert, simply call the SqlBulkCopy.WriteToServer. For Bulk Merge/Update/Delete, bulk insert first on the pseudo-table then execute a Merge/Update/Delete via JOIN statement.

[Grauenwolf]

• ADO/Dapper: Obviously we can just do it the hard way.
• EF Core: I think there are some extensions for EF Core that do this.
• Chain: No, it doesn't understand understand temp tables.

I don't know about the other ORMs.

[FransBouma]

LLBLGen Pro can't do this either. Well, I can build it with the plainsql API I have of course.

The bulk delete/updates using a single delete/update statement is a scenario that is used way more often than this tho. In 18 years I've never had a single request to build in bulk merge/update using a temp table.

[Grauenwolf]

So you mean like 'DELETE FROM Table WHERE ...`?

If so, that's going to be #104.

[FransBouma]

yes, and update table set ... where <predicate> which affects multiple rows. Bonus points if you can also support row filtering based on related entities :)

[mikependon]

Was not it more faster to bring the data in Bulk within the DB server and do the operation there utilizing the correct index (specially the clustered index)? With pseudo temp table, this can be achieved. But you it is correct that this is not a common scenario.