1

Closed

Support bulk upsert (in core or contrib) via LinqEntityDataReader + MERGE command

description

David Browne (dbrowne@microsoft.com) wrote the LinqEntityDataReader which wraps collections into a DbDataReader. Many people (myself included) use it as the basis for doing bulk inserts with SqlBulkCopy

http://archive.msdn.microsoft.com/LinqEntityDataReader

However, one of the things he shows an example of, which could be much more valuable IMHO, is that of 'upsert' via MERGE command into a table - the sentence describing the example (just above it on the page) is "Here's another TVP sample, this time using a multi-column TVP to pass a collection of entities to SQL 2008 and MERGE them into a table."

This seems like it would be hugely beneficial, both because it provides upsert (so we can remove the manual separation of inserts and updates ahead of time via reading of existing rows and then splitting our collections), but potentially more importantly because unlike the SqlBulkCopy route, it includes getting the resulting entities back (at least, AFAICT from the sample), so we don't have to make another query after the 'upsert' in order to get the identity ID values that were assigned.

His example depends on having created a supporting table type (Sales_SalesOrderDetail_type in his example) ahead of time, but for the purposes of supporting this programmatically / generically, those types could be generated automatically (either living just long enough for the particular upsert operation, or created if they don't exist and left in the target database). As a V1, and to remove the dependency on the DDL permission to create the type, supporting the upsert via having the call specify the SQL table type seems reasonable (and those that have DDL permission at runtime and want to have it generated programmatically can do so and then just pass in their generated table type).

Now, I certainly would imagine this is the kind of thing that the EF team would say shouldn't be in the core framework, so I'm hoping to get some guidance on 1) if not in EF core, which project(s) would this fall into the scope of? Is there a (semi-)official EF contrib similar to what ASP.NET MVC has with https://mvccontrib.codeplex.com/ ? A search shows https://efcontrib.codeplex.com/ which hasn't had a release in well over 5 years.

Is there already something in the upcoming EF6 to support bulk insert (or upsert) that could/should be leveraged instead of the approach from David Browne's example?

Thanks!
Closed Aug 29, 2013 at 5:37 PM by RoMiller
EF Team Triage: We agree with your assessment that this isn't something that would belong in the core framework, so we are closing this issue as it's not something we would implement in the code base for this project.
We are looking at batch insert/update/delete in upcoming releases (post-EF6).
We would also certainly consider adding any required hooks, interception points, etc. that were required by someone trying to implement bulk upsert.

comments

RoMiller wrote Aug 29, 2013 at 5:36 PM

EF Team Triage: We agree with your assessment that this isn't something that would belong in the core framework, so we are closing this issue as it's not something we would implement in the code base for this project.
We are looking at batch insert/update/delete in upcoming releases (post-EF6).
We would also certainly consider adding any required hooks, interception points, etc. that were required by someone trying to implement bulk upsert.