Table Valued Parameters and Dapper in .NET Core

A customer I’ve been working with for a while now has a monolithic ASP.NET MVC web application which we are porting to .NET Core 3.1 (and then almost immediately to .NET 6). One of our biggest changes was getting rid of Entity Framework and replacing it with Dapper, because performance is a feature.

To deflect the ire of EF Core aficionados out there, the answer is still no.

Dapper is a micro-ORM in that it does not do as much “magic” as Entity Framework. This necessitates more work at the data access layer, but we have the trade-off of speed.

One of the more interesting challenges was tuning a catch-all search query that was being run thousands of times a day with multiple different parameters. You know exactly the kind of query I mean. I tuned it by creating a stored procedure and sticking in a lot of dynamic Transact-SQL code. This made it much faster than the EF version by several orders of magnitude, but there was one problem: the list of location IDs that it takes in as a parameter.

Did you know SQL Server has a limit to the number of items you can put in an IN clause?

In any case, this list of location IDs was being sent into the stored procedure as a comma-delimited list in a single string. That string was then split up into individual items using a user-defined function.

At one point during testing, we discovered that NVARCHAR(255) wasn’t enough for the incoming list, and while I could have just made it NVARCHAR(MAX) I decided to make it into a table-valued parameter (TVP) instead.

Enter the venerable DataTable, a long-standing but expensive structure in .NET, and the recommended way to send in a list of items for SQL Server to read as a TVP.

I wrote a basic extension method to convert any IEnumerable<int> into a single-column DataTable and left it at that, figuring I could optimize it later for the much lighter-weight ISqlDataRecord which is fully supported by Dapper for single-column lists.

Enter my new colleague Eric Sampson, who in a single comment in Slack gave me all the information I needed to implement this neat adjustment to the code, which I will share below. Thanks, Eric!

Implementation

The first thing we need is a user-defined table type in SQL Server to populate with the incoming TVP:

CREATE TYPE TVP_Integer AS TABLE
    (
        [Value] INT NULL
    );
GO

Then we create an IEnumerable<SqlDataRecord>, which is much lighter weight than a DataTable:

private static IEnumerable<SqlDataRecord> CreateSqlDataRecord(IEnumerable<int> list)
{
	var metaData = new SqlMetaData("Value", SqlDbType.Int);
	var record = new SqlDataRecord(_metaData);
	foreach (var item in list)
	{
		record.SetInt32(0, item);
		yield return record;
	}
}

To send this into Dapper we need to use an ICustomQueryParameter which will be used to convert into a TVP. The input values are sent as an int[] array because that’s how we built the list elsewhere in the code. Then we use the AsTableValuedParameter method to make sure the object is the way Dapper needs it:

public static SqlMapper.ICustomQueryParameter GetTableValuedParameter(this int[] list)
{
	if (!ValidateList(list))
	{
		list = new int[] { };
	}

	return CreateSqlDataRecord(list).AsTableValuedParameter("TVP_Integer");
}

Finally in the data access layer inside the search method, we call it like so:

var parameters = new
{
	LocationIds = (locationIds ?? new int[] { }).GetTableValuedParameter()
};

return await Query<SearchResultTable>("[dbo].[SearchByLocationIds]", parameters).ConfigureAwait(false);

Dapper uses the table type behind the scenes and inserts all the items from the SqlDataRecord into that table. In your stored procedure you can either do a JOIN or an IN clause, depending on your needs.

If you’re playing along at home, as long as you have the user-defined type in the database this can all be combined into a single C# method. We happened to split it up into extension methods to follow our coding style.

Share your thoughts in the comments below.

Photo by Kyle Glenn on Unsplash.