EF6->SQL Server Table-Valued Parameters

When Papa Bear (bulk load) is too big and Baby Bear (row-by-row) is too small ... Mama Bear is just right!!! :smile:

At a 25K-foot altitude, interaction between a middle-tier, i.e., API layer, and the backing persistent store, i.e., a database, is either 1 or n. Entity Framework does a great job with reading/writing the 1, and a better than average job with reading of n; it's the writing of n that has always caused me a bit of ... consternation. :wink:

The sledgehammer approach is to use the SQLXML Bulk Load 1 or the SQL Bulk Insert 2 components, and while there have been times when I have used both to load gigabytes of offline data, I yearned for something more adaptive and every-day-usable.

   Trigger Warning    :wink:
One of these days - for the good of humanity - I'm gonna pay someone to proceed me down the street, waving a big ol' sign like this:


<AssertionFollows>
I believe that the average OLTP-type application has a bulk insert need for - at most - a few hundred rows at a times. MAX!!!
</AssertionFollows>

Are there times when gigabytes of data have to be added to the database? Abso!!! But not as part of the day-to-day usage by the day-to-day user!

Jurassic Park ... Jurassic World ... Jurassic Me?
By now, y'all know I'm a dinosaur. And by that I mean I understand the role that stored procedures play in modern application development. :smirk:

Back in the old days - you know, when cell phones could barely transmit verbal content, let alone oodles and oodles of data - my Obi Wan taught me that the closer you could get your data changes to the physical drive ... the better!

SQL Server introduced Table-Valued Parameters with 2008, but since EFx is an ORM targeting multiple persistent engines, there is no native support.

There have been a couple attempts by valiant community members - (most notably 3) - to wrangle this beast, but I couldn't find anything that was easy to understand! (For me, that is!!! :smile:)

My requirements are pretty darn straightforward:

  • Easy to implement
  • Must not require massive additional effort to maintain
  • Easy to use
  • Fast!
  • Easy to ... fill in the blank

Here's what I ended up with:

yourContext.ExecuteTVPProcedure<YourDataType>(IEnumerableOf<YourDataType>)  

The YourDataType generic reference above is intended to be a complex object with n number of simple properties, e.g., an EFx-compliant model. (It should not include any navigation properties.)

When ExecuteTVPProcedure is called - if YourDataType has not been previously encountered - certain default registration analyses are performed followed by the creation of a DataTable instance based on the IEnumerableOf <YourDataType>. All of this is combined and dispatched to the SQL Server using the standard ExecuteSqlCommand() method.

Pretty sweet, huh? :grin:

But before you go getting all googly-eyed ... understand that there's a couple limitations (where have we heard that before!!! :smirk:)

Nuts & Bolts
The simplest usage is to follow a convention-over-configuration model:

  1. Schema for both stored procedures and table types is dbo.
  2. There is a 1-to-1 name match between the POCO and TVP types and the datatypes must be equivalent.
  3. The stored procedure is named dbo.Save??? where ??? is the pluralized POCO type name, e.g., Contact -> Contacts, Company -> Companies.
  4. This only works for single-type execution calls.

When these conventions are satisfied, the following will just work:

var res = myContext.ExecuteTVPProcedure<Contact>(Contacts);

var res = myContext.ExecuteTVPProcedure<Company>(CompanyList);  

(Presuming User-Defined Table Types named dbo.Contact and dbo.Company and the stored procedures named dbo.SaveContacts and dbo.SaveCompanies all exist in the database.)

My Future Epitaph: Sam ... why did you have to make everything so complex?!?!?!
More complex usage scenarios can be achieved by making a call to the RegisterProcedure() method.

1. Defining the stored procedure name to be invoked:

TVPExtensions.RegisterProcedure<Contact>("dbo.UpdateAllContacts");  
TVPExtensions.RegisterProcedure<Contact, EmployeeContact>("dbo.ProcessEmployeeContacts");  

2. Defining the User-Defined Table Type to be used:

TVPExtensions.RegisterProcedure<EmployeeContact>("dbo.SaveEmployeeContacts", "hr.uddtEmployeeContact");  
TVPExtensions.RegisterProcedure<Contact, Event>("dbo.RegisterContactForEvents", null, "evt.uddtExternalEvent");  

NOTE: The set of TypeNames is an ordered list, so pass a null value for any Types where the previously defined default value is acceptable.

Once a procedure has been registered, it can be called using the following syntax:

var res = myContext.ExecuteTVPProcedure<Contact>(Contacts);
var res = myContext.ExecuteTVPProcedure<Contact, EmployeeContact>(Contacts, EmpContacts);    

Once a type set has been defined, different stored procedures (though they still need to match the expected structure!) can be invoked by specifying the stored procedure name to execute:

var res = myContext.ExecuteTVPProcedure<Contact>("dbo.UpdateContacts", Contacts);  
var res = myContext.ExecuteTVPProcedure<Contact>("dbo.ExportContacts", Contacts);  
var res = myContext.ExecuteTVPProcedure<Contact, EmployeeContact>("dbo.MergeEmployeeContacts", Contacts, EmpContacts);  
var res = myContext.ExecuteTVPProcedure<Contact, EmployeeContact>("dbo.RegisterContactsForEmployee", Contacts, EmpContacts);  

The stored procedure name is optional and if absent, the stored procedure associated with the first registration call will be invoked. The following calls are equivalent:

var res = myContext.ExecuteTVPProcedure<Contact>(Contacts);  
var res = myContext.ExecuteTVPProcedure<Contact>("dbo.SaveContacts", Contacts);  

As with the simple usage, certain conventions need to be followed:

1. The referenced POCO class must have the same property names and datatypes (or equivalent!) as the TVP declared in SQL Server.

The TVP TypeName will be determined by the concatenation of either the default or a custom schema, an optional type prefix, and then the name of the POCO class:

SqlTypeName = String.Format("{0}{1}{2}", (C_CustomTVPSchema ?? "dbo."), C_CustomTVPPrefix, aTypes[i].Name);  

It is possible to override this behavior by passing the TypeName to use as part of the RegisterProcedure<>() call:

TVPExtensions.RegisterProcedure<Contact, EmployeeContact>("dbo.SaveEmployeeContacts", null, "hr.uddtEmployeeContacts");  

The set of TypeNames is an ordered list, so pass a null value for any Types where default value is acceptable.

2. The parameters passed to the ExecuteTVPProcedure<>() method must be in the same order as the declaration, and be an IEnumerable<> of the declared type.

myContext.ExecuteTVPProcedure<Contact, EmployeeContact>(ContactsList, EmpContactsList);  

3. Additional non-TVP parameters can be included and will be passed to the stored procedure, after all other TVP parameters have been referenced:

myContext.ExecuteTVPProcedure<Contact>("dbo.SaveContactBeerConsumptionPreferences", Contacts, false, NumberOfBottlesOnTheWall);  

The SqlDbType used in the stored procedure invocation is based on the type of the passed parameter. A null value will be handled properly, presuming the stored procedure can handle a null input.

4. When creating the SQL Stored Procedure, it is recommended to declare with an associated default value for any non-TVP parameters:

CREATE PROCEDURE dbo.SaveContactBeerConsumptionPreferences(@Contact dbo.Contact READONLY, @DomesticOnly bit = 0, @PreferredBottleCount int = 99)  

5. The RETURN_VALUE of the executed stored procedure is returned by the ExecuteTVPProcedure<>() call. This is an integer-type value, and any other result set(s) will be ignored.

6. All stored procedure parameters are passed positionally. Be very careful when ALTERing existing stored procedure definitions or bad things might happen! :smirk:

Both the RegisterProcedure<>() and the ExecuteTVPProcedure<>() methods support stored procedures with up to 5 user-defined table types.

Ready for Prime Time???
This extension library has been used daily in a couple B2B applications for over two years, with a common usage pattern being the logging of every data request and change operation. And while it uses reflection, everything that I could cache is cached!

That said, I'm eager to have others review and/or put it through its' paces. This is an important part of my development framework, so I will respond to questions/comments as quick as I can.

Source Code and a sample project is included below.

Sunny Skies ... :wink:

SC



Source Code & Examples

Comments powered by Disqus