Extending Dapper’s ORM Multi-Mapping Capabilities

Since I last posted, my wife and I have bought a new home, moved, sold our previous home, and ran the Chicago marathon. All while getting up the learning curve and taking on more responsibility at our new jobs (both started in June). Needless to say, I’ve had no time for tech blogging. Until now. OK, let’s get to it:

Dapper is a great micro-ORM library. It’s simple and highly performant. I love its Multi-Mapping feature, which simplifies mapping columns from a SQL result set into multiple object types. It has an annoyance though: The lambda method provided by the caller (to map objects) must be a function. That is, it must return a value. Often I simply need to map properties from a row into one or many Dictionaries, HashSets, or KeyedCollections. I don’t need the lambda method to return a value because I never intend to iterate over the IEnumerable returned by Dapper’s QueryAsync extension method. In other words, rather than writing this code:

	using (SqlConnection connection = new SqlConnection(_connection))
	{
	await connection.OpenAsync();
	Dictionary<int, int> serviceCallToTechnician = new Dictionary<int, int>();
	Dictionary<int, HashSet<int>> technicianToServiceCalls = new Dictionary<int, HashSet<int>>();
	Func<int, int, int> map = (ServiceCallId, TechnicianId) =>
	{
	serviceCallToTechnician.Add(ServiceCallId, TechnicianId);
	if (!technicianToServiceCalls.ContainsKey(TechnicianId))
	{
	technicianToServiceCalls.Add(TechnicianId, new HashSet<int>());
	}
	technicianToServiceCalls[TechnicianId].Add(ServiceCallId);
	return default;
	};
	var param = new {MinServiceCallId, MaxServiceCallId};
	IEnumerable<int> unusedReturnValues = await connection.QueryAsync(_sql, map, param, splitOn: "TechnicianId");
	return (serviceCallToTechnician, technicianToServiceCalls);
	}

view raw GetCallDataRegularDapper.cs hosted with by GitHub

I’d rather write this code:

	using (SqlConnection connection = new SqlConnection(_connection))
	{
	await connection.OpenAsync();
	Dictionary<int, int> serviceCallToTechnician = new Dictionary<int, int>();
	Dictionary<int, HashSet<int>> technicianToServiceCalls = new Dictionary<int, HashSet<int>>();
	Action<int, int> map = (ServiceCallId, TechnicianId) =>
	{
	serviceCallToTechnician.Add(ServiceCallId, TechnicianId);
	if (!technicianToServiceCalls.ContainsKey(TechnicianId))
	{
	technicianToServiceCalls.Add(TechnicianId, new HashSet<int>());
	}
	technicianToServiceCalls[TechnicianId].Add(ServiceCallId);
	};
	var param = new { MinServiceCallId, MaxServiceCallId };
	await connection.QueryAsync(_sql, map, param, SplitOn: "TechnicianId");
	return (serviceCallToTechnician, technicianToServiceCalls);
	}

view raw GetCallDataDapperExtension.cs hosted with by GitHub

For an explanation of how Dapper Multi-Mapping works, see the official documentation or the WebAPI Service Method section of my previous post, The Best Domain-Specific Language for Manipulating Data is SQL

Compare lines 14 and 17 in the first example above to line 16 in the second. The second example is more concise. The “map” lambda method doesn’t return an unused value. I realize this is a minor improvement. But it has a positive impact beyond merely scrubbing away a single line of code: It removes a misdirect that may cause a programmer unfamiliar with Dapper to wonder, “What am I supposed to return here? And what am I expected to do with the returned value?” If the answer is nothing, then the programmer shouldn’t be forced to return a value.

Unfortunately, Dapper’s extension methods for IDbConnection don’t support passing an Action, only a Func. Remember, Func<int, int, int> means “pass in two integer parameters and return an integer.” The return type is the last generic type parameter. Action<int, int> means “pass in two integer parameters and do not return anything.”

OK, let’s write our own extension methods.

	using System;
	using System.Data;
	using System.Threading.Tasks;
	using Dapper;
	using JetBrains.Annotations;
	namespace ErikTheCoder.Data
	{
	[UsedImplicitly]
	public static class ExtensionMethods
	{
	// These QueryAsync methods enable mapping column values to variables captured by the lambda (Map Action)
	// in a single traversal of the SQL result set. Because calling code has no need to traverse the
	// IEnumerable returned by the Dapper extension method, no value is returned. Without this extension
	// method, calling code would have to pass a Func to Dapper's extension method even though it never
	// references the Func's return value.
	[UsedImplicitly]
	public static async Task QueryAsync<TFirst, TSecond>(this IDbConnection Connection, string Sql,
	Action<TFirst, TSecond> Map, object Param = null, IDbTransaction Transaction = null,
	bool Buffered = true, string SplitOn = "Id", int? CommandTimeout = null,
	CommandType? CommandType = null)
	{
	if (!Buffered) throw new Exception
	($"{nameof(QueryAsync)} extension method called with Map Action must be buffered.");
	Func<TFirst, TSecond, bool> map = (First, Second) =>
	{
	Map(First, Second);
	return true;
	};
	await Connection.QueryAsync(Sql, map, Param, Transaction, true, SplitOn, CommandTimeout, CommandType);
	}
	}
	}

view raw DapperExtensionMethods.cs hosted with by GitHub

And so on up to seven parameters.

You may review full source code of the extension methods in my Data project in GitHub. Or download the NuGet package. Refer to example code in my Sandbox project in GitHub that demonstrates how to use the extension methods.