How to Write One to Many Query in Dapper.Net

How do I write one to many query in Dapper.Net?

This post shows how to query a highly normalised SQL database, and map the result into a set of highly nested C# POCO objects.

Ingredients:

  • 8 lines of C#.
  • Some reasonably simple SQL that uses some joins.
  • Two awesome libraries.

The insight that allowed me to solve this problem is to separate the MicroORM from mapping the result back to the POCO Entities. Thus, we use two separate libraries:

  • Dapper as the MicroORM.
  • Slapper.Automapper for mapping.

Essentially, we use Dapper to query the database, then use Slapper.Automapper to map the result straight into our POCOs.

Advantages

  • Simplicity. Its less than 8 lines of code. I find this a lot easier to understand, debug, and change.
  • Less code. A few lines of code is all Slapper.Automapper needs to handle anything you throw at it, even if we have a complex nested POCO (i.e. POCO contains List<MyClass1> which in turn contains List<MySubClass2>, etc).
  • Speed. Both of these libraries have an extraordinary amount of optimization and caching to make them run almost as fast as hand tuned ADO.NET queries.
  • Separation of concerns. We can change the MicroORM for a different one, and the mapping still works, and vice-versa.
  • Flexibility. Slapper.Automapper handles arbitrarily nested hierarchies, it isn't limited to a couple of levels of nesting. We can easily make rapid changes, and everything will still work.
  • Debugging. We can first see that the SQL query is working properly, then we can check that the SQL query result is properly mapped back to the target POCO Entities.
  • Ease of development in SQL. I find that creating flattened queries with inner joins to return flat results is much easier than creating multiple select statements, with stitching on the client side.
  • Optimized queries in SQL. In a highly normalized database, creating a flat query allows the SQL engine to apply advanced optimizations to the whole which would not normally be possible if many small individual queries were constructed and run.
  • Trust. Dapper is the back end for StackOverflow, and, well, Randy Burden is a bit of a superstar. Need I say any more?
  • Speed of development. I was able to do some extraordinarily complex queries, with many levels of nesting, and the dev time was quite low.
  • Fewer bugs. I wrote it once, it just worked, and this technique is now helping to power a FTSE company. There was so little code that there was no unexpected behavior.

Disadvantages

  • Scaling beyond 1,000,000 rows returned. Works well when returning < 100,000 rows. However, if we are bringing back >1,000,000 rows, in order to reduce the traffic between us and SQL server, we should not flatten it out using inner join (which brings back duplicates), we should instead use multiple select statements and stitch everything back together on the client side (see the other answers on this page).
  • This technique is query oriented. I haven't used this technique to write to the database, but I'm sure that Dapper is more than capable of doing this with some more extra work, as StackOverflow itself uses Dapper as its Data Access Layer (DAL).

Performance Testing

In my tests, Slapper.Automapper added a small overhead to the results returned by Dapper, which meant that it was still 10x faster than Entity Framework, and the combination is still pretty darn close to the theoretical maximum speed SQL + C# is capable of.

In most practical cases, most of the overhead would be in a less-than-optimum SQL query, and not with some mapping of the results on the C# side.

Performance Testing Results

Total number of iterations: 1000

  • Dapper by itself: 1.889 milliseconds per query, using 3 lines of code to return the dynamic.
  • Dapper + Slapper.Automapper: 2.463 milliseconds per query, using an additional 3 lines of code for the query + mapping from dynamic to POCO Entities.

Worked Example

In this example, we have list of Contacts, and each Contact can have one or more phone numbers.

POCO Entities

public class TestContact
{
public int ContactID { get; set; }
public string ContactName { get; set; }
public List<TestPhone> TestPhones { get; set; }
}

public class TestPhone
{
public int PhoneId { get; set; }
public int ContactID { get; set; } // foreign key
public string Number { get; set; }
}

SQL Table TestContact

Sample Image

SQL Table TestPhone

Note that this table has a foreign key ContactID which refers to the TestContact table (this corresponds to the List<TestPhone> in the POCO above).

Sample Image

SQL Which Produces Flat Result

In our SQL query, we use as many JOIN statements as we need to get all of the data we need, in a flat, denormalized form. Yes, this might produce duplicates in the output, but these duplicates will be eliminated automatically when we use Slapper.Automapper to automatically map the result of this query straight into our POCO object map.

USE [MyDatabase];
SELECT tc.[ContactID] as ContactID
,tc.[ContactName] as ContactName
,tp.[PhoneId] AS TestPhones_PhoneId
,tp.[ContactId] AS TestPhones_ContactId
,tp.[Number] AS TestPhones_Number
FROM TestContact tc
INNER JOIN TestPhone tp ON tc.ContactId = tp.ContactId

Sample Image

C# code

const string sql = @"SELECT tc.[ContactID] as ContactID
,tc.[ContactName] as ContactName
,tp.[PhoneId] AS TestPhones_PhoneId
,tp.[ContactId] AS TestPhones_ContactId
,tp.[Number] AS TestPhones_Number
FROM TestContact tc
INNER JOIN TestPhone tp ON tc.ContactId = tp.ContactId";

string connectionString = // -- Insert SQL connection string here.

using (var conn = new SqlConnection(connectionString))
{
conn.Open();
// Can set default database here with conn.ChangeDatabase(...)
{
// Step 1: Use Dapper to return the flat result as a Dynamic.
dynamic test = conn.Query<dynamic>(sql);

// Step 2: Use Slapper.Automapper for mapping to the POCO Entities.
// - IMPORTANT: Let Slapper.Automapper know how to do the mapping;
// let it know the primary key for each POCO.
// - Must also use underscore notation ("_") to name parameters in the SQL query;
// see Slapper.Automapper docs.
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(TestContact), new List<string> { "ContactID" });
Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(TestPhone), new List<string> { "PhoneID" });

var testContact = (Slapper.AutoMapper.MapDynamic<TestContact>(test) as IEnumerable<TestContact>).ToList();

foreach (var c in testContact)
{
foreach (var p in c.TestPhones)
{
Console.Write("ContactName: {0}: Phone: {1}\n", c.ContactName, p.Number);
}
}
}
}

Output

Sample Image

POCO Entity Hierarchy

Looking in Visual Studio, We can see that Slapper.Automapper has properly populated our POCO Entities, i.e. we have a List<TestContact>, and each TestContact has a List<TestPhone>.

Sample Image

Notes

Both Dapper and Slapper.Automapper cache everything internally for speed. If you run into memory issues (very unlikely), ensure that you occasionally clear the cache for both of them.

Ensure that you name the columns coming back, using the underscore (_) notation to give Slapper.Automapper clues on how to map the result into the POCO Entities.

Ensure that you give Slapper.Automapper clues on the primary key for each POCO Entity (see the lines Slapper.AutoMapper.Configuration.AddIdentifiers). You can also use Attributes on the POCO for this. If you skip this step, then it could go wrong (in theory), as Slapper.Automapper would not know how to do the mapping properly.

Update 2015-06-14

Successfully applied this technique to a huge production database with over 40 normalized tables. It worked perfectly to map an advanced SQL query with over 16 inner join and left join into the proper POCO hierarchy (with 4 levels of nesting). The queries are blindingly fast, almost as fast as hand coding it in ADO.NET (it was typically 52 milliseconds for the query, and 50 milliseconds for the mapping from the flat result into the POCO hierarchy). This is really nothing revolutionary, but it sure beats Entity Framework for speed and ease of use, especially if all we are doing is running queries.

Update 2016-02-19

Code has been running flawlessly in production for 9 months. The latest version of Slapper.Automapper has all of the changes that I applied to fix the issue related to nulls being returned in the SQL query.

Update 2017-02-20

Code has been running flawlessly in production for 21 months, and has handled continuous queries from hundreds of users in a FTSE 250 company.

Slapper.Automapper is also great for mapping a .csv file straight into a list of POCOs. Read the .csv file into a list of IDictionary, then map it straight into the target list of POCOs. The only trick is that you have to add a propery int Id {get; set}, and make sure it's unique for every row (or else the automapper won't be able to distinguish between the rows).

Update 2019-01-29

Minor update to add more code comments.

See: https://github.com/SlapperAutoMapper/Slapper.AutoMapper

How to query an object with one-to-many relationship using Dapper?

There are some ways to do this, see How do I write one to many query in Dapper.Net?

I would prefer doing this as follows:

var fatherDictionary = new Dictionary<int, Father>();

var list = connection.Query<Father, Son, Father>(
@"SELECT f.*, s.* FROM Father f INNER JOIN Son s ON f.Id = s.FatherId WHERE f.Id = 1",
(f, s) =>
{
Father fatherEntry;

if (!fatherDictionary.TryGetValue(f.Id , out fatherEntry))
{
fatherEntry = f;
fatherEntry.Sons = new List<Son>();
fatherDictionary.Add(fatherEntry.Id, fatherEntry);
}

fatherEntry.Sons.Add(s);
return fatherEntry;
})
.Distinct()
.ToList();

See example: https://dapper-tutorial.net/result-multi-mapping#example-query-multi-mapping-one-to-many

Dapper Query One To Many Relation

The main changes were:

  • Query parameters
  • Deserialization from a list of strings into the Merchants property (IEnumerable<string>)
  • Add in the splitOn the columns

Result:

var leadModels = _dbConnection.Query<string, string, string, LeadModel>(sql, (taxId, merchant, cardBrand) =>
{
LeadModel leadModel = new LeadModel();

leadModel.TaxId = taxId;
leadModel.Merchants = JsonConvert.DeserializeObject<List<string>>(merchant);
leadModel.CardBrand = cardBrand;

return leadModel;

}, dynamicParameters, splitOn: "TaxId,Merchant,CardBrand")
.ToList();

Dapper One to Many Mapping Logic

I'm the author of this tutorial: https://dapper-tutorial.net/query#example-query-multi-mapping-one-to-many

why they have you store the orders in the dictionary

A row is returned for every OrderDetail. So you want to make sure to add the OrderDetail to the existing Order and not create a new one for every OrderDetail. The dictionary is used for performance to check if the Order has been already created or not.

it would be cleaner to just return the ordersDictionary.Values

How will your query return dictionary values?

Of course, if you are in a method such as yours, you can do

var list = orderDictionary.Values;
return list;

But how to make this Connection.Query return dictionary values? An order is returned for every row/OrderDetail, so the order will be returned multiple times.

Outside the Query, your dictionary solution works great and is even a better solution for performance, but if you want to make your Query return the distinct list of orders without using Distinct or some similar method, it's impossible.

EDIT: Answer comment

my suggestion return orderDictionary.Values.ToList(); //change 3 return dictionaryValues

Thank you for your great feedback, it's always appreciated ;)

It would be weird in a tutorial to use what the query returns when there is no relationship but use the dictionary for one to many relationships

// no relationship
var orders = conn.Query<Order>("", ...).Distinct();

// one to many relationship
conn.Query<Order, OrderDetail>("", ...);
var orders = orderDictionary.Values.ToList();

Your solution is better for performance the way you use it, there is no doubt about this. But this is how people usually use the Query method:

var orders = conn.Query("", ...).Distinct();

var activeOrders = orders.Where(x => x.IsActive).ToList();
var inactiveOrders = orders.Where(x => !x.IsActive).ToList();

They use what the Query method returns.

But again, there is nothing wrong with the way you do it, this is even better if you can do it.



Related Topics



Leave a reply



Submit