Calling Stored Procedure with UDT table parameter in Entity Framework 6

I recently had need to drastically decrease the number of database round trips being made in an application that reads and saves input from csv files.Prior to this the application had seen mostly small csv files with a few hundred lines, but that number recently jumped into the 10s of thousands so you can imagine the time explosion. In one case we were uploading one line at a time and saving the EntityFramework context after each one. A horrible situation, but an easy enough design mistake to make (I’d inherited this application and have been wrestling it into submission for the last couple months). I shifted it to attach the thousands of records to the context and then save in bulk, but even this proved to take longer than it felt it should. 

Since Entity Framework 6 doesn’t seem to support bulk insert, I landed on a strategy I’d used several EF versions ago. Upload with a sql stored procedure that takes a user defined table type as input. This would allow upload of bulk datasets with little effort. Here I’d like to demonstrate what how that was achieved in this application. I do this in .NET Framework 4.7.2 (should work in 4.8 also) with EF6. I found a nice little nuget package that helps make this easy: EntityFrameworkExtras.EF6. Sample code for this can be found in this github repo.

In this post, I’ve boiled it down to a few steps:

1. define your type
2. define your stored proc (assumes a table already exists)
3. build your classes
4. call you context

To start, I use MS SQL Server in this, and have defined a simple demo table like this:

Create Table dbo.SampleTableForUdtTableInsertByProc(
Id uniqueidentifier primary key default(NEWSEQUENTIALID()),
ItemName nvarchar(50) not null,
ItemDescription nvarchar(500) not null,
ItemCount int not null default(0)
)

Define Your Type

create type dbo.SampleUdtForInsertByProc AS TABLE(
ItemName nvarchar(50) not null,
ItemDescription nvarchar(500) not null,
ItemCount int not null default(0)
)

Define your procedure

CREATE PROCEDURE ProcForSampleUdtInsert (@tbl_SampleUdtForInsertByProc SampleUdtForInsertByProc readonly) 
AS 
BEGIN   
       INSERT INTO SampleTableForUdtTableInsertByProc 
       (ItemName, ItemDescription, ItemCount) 
       SELECT  ItemName, 
                      ItemDescription,
                      ItemCount
       FROM @tbl_SampleUdtForInsertByProc 
END

With that existing in your database, it’s now down to writing some classes so we can call the extension methods built into EntityFrameworkExtras.
Define the type (I’ve named mine after the type I created). EntityFrameworkExtras provides a few attributes so we can explicitly define the shape of the data.

[UserDefinedTableType("SampleUdtForInsertByProc")]
    public class SampleUdtForInsertByProc
    {
        [UserDefinedTableTypeColumn(1)]
        public string ItemName { get; set; }
        [UserDefinedTableTypeColumn(2)]
        public string ItemDescription { get; set; }
        [UserDefinedTableTypeColumn(3)]
        public int ItemCount { get; set; }
    }

The stored procedure is similarly defined.

[StoredProcedure("ProcForSampleUdtInsert")]
    public class ProcForSampleUdtInsert
    {
        [StoredProcedureParameter(System.Data.SqlDbType.Udt, ParameterName = "tbl_SampleUdtForInsertByProc")]
        public List<SampleUdtForInsertByProc> tbl_SampleUdtForInsertByProc { get; set; }
    }

And that’s all it takes to be able to make the calls. I was tempted to add something to the context but this turned out not be necessary as EntityFrameworkExtras leverages  ExecuteStoreQuery to do it’s magic.You can make the call like this.

using (var context = new EntityFrameworkSampleContext())
            {
                var proc = new ProcForSampleUdtInsert()
                {
                    tbl_SampleUdtForInsertByProc = new List<Data.UserDefinedTypes.SampleUdtForInsertByProc>
                 {
                     new Data.UserDefinedTypes.SampleUdtForInsertByProc
                     {
                          ItemCount = 2,
                          ItemName = "TestItem2",
                          ItemDescription = "Test item with 2 count"
                     },
                     new Data.UserDefinedTypes.SampleUdtForInsertByProc
                     {
                          ItemCount = 3,
                          ItemName = "TestItem3",
                          ItemDescription = "Test item with 3 count"
                     },
                     new Data.UserDefinedTypes.SampleUdtForInsertByProc
                     {
                          ItemCount = 4,
                          ItemName = "TestItem4",
                          ItemDescription = "Test item with 4 count"
                     }
                 }
                };
           
                context.Database.ExecuteStoredProcedure(proc);
            }

I jammed that into the Home controller as a first pass and left it that way in my demo code. After running the application one time (default navigation to Index causes the insert in the demo), you can simply run a select on the table in your database. Your uploaded data should exist. 
I used this after creating the demo to successfully upload my 10s of thousands of rows of data in just seconds, which resulted in a significant latency decrease.