Dapper.Compose

Dapper is a great tool, but it carries an implicit limitation of stringly queries: we have to write lots of small, easily testable queries so we can validate them against the database. This unfortunately ends up incurring many round trips, because there's no straightforward way to safely combine small queries into larger queries.

This is the purpose of Dapper.Compose. Suppose we start with the Northwind database, and we want a simple dynamic web page listing the orders an employee is managing. Using Dapper.Compose, we might write it like so:

publicstaticclassQueries{// individual query to obtain an employeepublicstaticreadonlyQuery<Employee>GetEmployeeById=Query.Single<Employee>(@"select EmployeeID, FirstName, LastName from Employees where EmployeeId = @employeeID");// get the list of orders an employee is managingpublicstaticreadonlyQuery<List<Order>>GetOrdersByEmployeeId=Query.List<Order>("select OrderID, OrderDate, EmployeeID from Orders where EmployeeId = @employeeID");// the combined querypublicstaticreadonlyQuery<EmployeeOrders>GetOrdersByEmployeeId=Query.Combine(GetEmployeeById,GetOrdersByEmployeeId,(e,o)=>newEmployeeOrders{Employee=e,Orders=o.ToList()});}publicclassEmployee{publicintEmployeeID{get;set;}publicstringFirstName{get;set;}publicstringLastName{get;set;}}publicclassOrder{publicintOrderID{get;set;}publicDateTime?OrderDate{get;set;}publicintEmployeeID{get;set;}}publicclassEmployeeOrders{publicEmployeeEmployee{get;set;}publicList<Order>Orders{get;set;}}

So the queries are still individually usable, but are easily combined into larger queries that still only perform a single round trip to the database. On our dynamic web page, our model would be EmployeeOrders which we would obtain like so:

varresults=Queries.GetOrdersByEmployeeId.Execute(dbConnection,new{employeeId= ...});

The only caveat is that you still have to ensure you're using the correct parameter names, but this is no worse than ordinary Dapper.

This provides a simple framework to incrementally build up your application, where you can easily compose previously written small queries into larger multiqueries that minimize database rountrips.

Queries as Embedded Resources

Embedding SQL queries as strings in your assembly is generally terrible for many reasons. You don't get intellisense, you can't easily test the query for well-formedness, SQL queries can get somewhat long which ends up seriously cluttering your code, and so on.

Fortunately, .NET has long had the ability to ship other file types along with code via embedded resources. So add an .sql file to your project:

-- in ProjectName/Queries/GetEmployee.sqlselect EmployeeID, FirstName, LastName from Employees where EmployeeId = @employeeID

Then mark it as an embedded resource in the build properties and in your program call:

publicstaticclassQueries{// individual query to obtain an employeepublicstaticreadonlyQuery<Employee>GetEmployeeById=Query.Single<Employee>(Query.Load<Employee>("ProjectName.Queries.GetEmployee.sql"));
...}

While editing an .sql file you get full intellisense and query validation if you're connected to your database, and long query strings are no longer cluttering your code.

There's one other convenient feature available for query validation purposes: you can identify only the section of an .sql containing your query, and anything before that marker will be ignored:

-- in ProjectName/Queries/GetEmployee.sql-- everything until the comment "-- Dapper.Compose.Query" is ignored by Query.Load
declare @employeeID int=3-- Dapper.Compose.Queryselect EmployeeID, FirstName, LastName from Employees where EmployeeId = @employeeID

Notice how this SQL query is now a fully runnable query as-is. Query.Load will ignore anything before the comment "-- Dapper.Compose.Query", but the prologue that's ignored makes the whole query trivial to test:

vardbConnection= ...;// open connectionforeach(varqueryinQuery.GetRunnable<Employee>())dbConnection.Execute(query.Value);

Any query with the marker comment is assumed to be runnable, so you can load them all and run them as-is to check whether they return proper results or whether they generate any errors indicating a possible schema mismatch.

Shout out to QueryFirst for inspiring this idea. That project will be a promising solution once it develops a little more.

Including Updates in a Query

This library primarily deals with queries that return results, but updates don't actually return results so that makes them difficult to compose like other queries.

For example, suppose you're writing some kind of email front-end that needs to return the list of unread messages to show some useful links on the toolbar. However, viewing an individual message should actually mark that message as read so it's no longer in the list.

Your query to return the list of unread messages might be something like:

--GetEmailSummaries: return list of unread messages
USE EmailDB
declare @userid int=103-- Dapper.Compose.QueryselectN.Id, substring(N.Text, 1, 25) as Summary, N.VersionasDate, N.Tagfromdbo.Emails_To X inner
joindbo.Emails N ONX.EmailId=N.Id inner
joindbo.Users U ONU.EmailAddress=X.AddresswhereU.UserId= @userid andX.DateRead is null

For the email viewing page, you'd combine it with an update query like this:

--MarkEmailRead: mark a note as read
USE EmailDB
declare @userId int=103
declare @emailId int=103-- Dapper.Compose.Query
declare @email nvarchar(128) = (select EmailAddress fromdbo.Userswhere UserId=@userId)
update N
setN.DateRead= GetDate()
fromdbo.Emails_To N
whereN.EmailId= @emailId andN.Address= @email
select @@ROWCOUNT

And we might combine them all in code returning a view model for the message viewing page as follows:

staticreadonlyQuery<ReadEmailViewModel>getReadNote=Query.Combine(GetAuthenticatedUserById,MarkEmailRead,GetEmailSummaries,GetEmailById,(user,_,unread,current)=>newReadEmailViewModel{User=user,Messages=unread,Email=current,});

The _ parameter corresponds to the select @@ROWCOUNT line in the MarkEmailRead query, which isn't typically used so it's just discarded.

Here you can see how you can combine sequences of sophisticated queries so you only need one round-trip to the server.

Validating Queries in Code

Aside from the above query validation, you can also bind default query parameter values via attributes. So given a static query class like:

publicstaticclassQueries{// individual query to obtain an employee[QueryParam(nameof(Employee.EmployeeID),3)]publicstaticreadonlyQuery<Employee>GetEmployeeById=Query.Single<Employee>(Query.Load<Employee>("ProjectName.Queries.GetEmployee.sql"));// a runnable embedded query, see above: Query.GetRunnable<T>()publicstaticreadonlyQuery<int>CountEmployeeOrders=Query.Single<int>(Query.Load<Employee>("ProjectName.Queries.CountEmployeeOrders.sql"));
...}

You can run all static members of a class bound like this via single call:

// transaction is optional, but recommendedQuery.Validate<Queries>(dbConnection,dbTransaction);

This is useful for queries that accept types that are awkward to express in SQL, like arrays.

Installation

The prerelease version is on nuget and fully functional. Just install with:

Install-Package Dapper.Compose -pre

Building

If you want to build and run the tests, note that you'll need the Northwind database installed. This was the simplest way for me to run some tests, although I plan to switch to an embedded database at some point. Contributions are welcome!

Future Work

• Associations aren't supported -- this one might be tricky!