One-to-many without exposing child collections and/or foreign keys in the model

Question

I'm having a problem with Dapper when I do not expose my foreign keys and child collections in the POCO model. Let's take a simple example with the two entities:

Bike (Id, ModelName)
Tire (Id, Tread)

In the database each Tire has a foreign key to Bike. But my model does not. From the database, I want to materialize this structure into a Dictionary<Bike, IEnumerable<Tire>>. Where each Bike (unique) will have two tires.

I could select this one-to-many relationship using the following query:

SELECT b.Id, b.ModelName, t.Id, t.Tread
FROM Bike b
JOIN Tire t ON b.Id = t.bike_id

In order to map that using Dapper, I have done the following:

var dbResult = connection.Query<Bike, Tire, KeyValuePair<Bike, Tire>>("sql-query-above",
(a, s) => new KeyValuePair<Bike, Tire>(a, s);
splitOn: "Id");

And to turn that result into my dictionary, using LINQ:

Dictionary<Bike, IEnumerable<Tire>> dict = dbResult.GroupBy(g => g.Key, g => g.Value).ToDictionary(g => g.Key, g => g.AsEnumerable());

And it correctly returns the data structure:

Bike #1
 - Tire #1
 - Tire #2
Bike #2
 - Tire #3
 - Tire #4

But is this the most efficient way of materializing this data structure? An alternative would be to avoid the dictionary and create other entities that exposes the foreign keys and relationships (e.g. Tires collection on Bike and FK on Tire), and use a mapping approach like described here. But I would like to avoid that in my model because it would result in lots of extra classes. But what about performance? Is this worse or the same?

Answer 1

I am no expert at using Dapper but I have run into limitations with it like the one you're experiencing. I had a similar situation where one of the properties in my object was a collection like you tires. I found it was simpler to create a second query to populate those inner types of collections with an extension method.

So you might just grab all the bikes first and then call your extension method to grab the tire data like:

dict.WithTires();    

I know its a second call to the database but the tradeoff is that you can still grab a bike without having to get the tires information everytime.

Also, you might think about adding the tires collection as a property to your bike class which IMHO is better than uing a dictionary. If you had something like this:

public class Bike
{
    public int id { get; set; }
    public string modelName { get; set; }
    public IList<Tires> tires { get; set; }
}

public class Tires
{
    public int id { get; set; }
    public string tread { get; set; }
}

You could easily create an extension method for grabbing the tire data for an individual bike or a collection of bikes:

Bike myBike = new Bike();
List<Bike> bikeCollection = new List<Bike>();

myBike.WithTires();
bikeCollection.WithTires();