Multi Poco Queries

This is from the original PetaPoco documentation, crawled by the Wayback Machine on 9/5/2015. See also the page on Mapping One-to-Many and Many-to-One Relationships.

Firstly, credit needs go to Sam Saffron's Dapper project for the idea behind this. PetaPoco's multi-poco query support is very similar to what Dapper calls multi-mapping however PetaPoco's implementation is considerably different, the way the split point between columns works is different and it can also automatically guess and assign the object relationships between POCOs.

Background

The idea behind multi-poco queries is to make an SQL JOIN query and have the columns from each returned table automatically mapped to POCOs representing those tables. In other words, instead of one row being mapped to one POCO, the first N columns are mapped to one POCO, the next N columns to another etc...

Usage

Here's a simple example of a multi-poco query:

var sql = PetaPoco.Sql.Builder
                .Append("SELECT articles.*, users.*")
                .Append("FROM articles")
                .Append("LEFT JOIN users ON articles.user_id = users.user_id");

var result = db.Query<article, user, article>((a,u) => { a.user=u; return a }, sql);

Some notes:

The SQL query is returning the columns from two tables.
The first two generic type parameters to the Query method specify the POCO types that will hold the data for each row.
The third generic type parameter is the type of items in the returned collection - typically the same as the first table type, but could be something else.
The Query method takes as it's first parameter a callback delegate that can be used to wire up the relationship between the two objects.

So in this example, we're returning an IEnumerable<article> where each article object has a reference to the associated user through it's userproperty.

PetaPoco supports up to 5 POCO types and there are also Fetch and Query variations.

Choosing the Split Points

The trickest part of the multi-poco support is deciding where the result sets should be split – i.e., which columns map to which POCO's. The approach taken by PetaPoco relies on convention and is pretty simple, but different (and IMO better) than Dapper's.

The columns returned must be in the same order as the generic type parameters used in the Query<> method call. ie: the first N columns map to T1, the next N to T2 etc... Columns are mapped by starting at the left most column using the first POCO, finding a split point and mapping subsequent columns onto the next POCO type.

If a column name has already been mapped onto the current POCO type it's assumed to be a split point. Imagine this set of columns:

article_id, title, content, user_id, user_id, name

and these POCOs:

class article
{
    long article_id { get; set; }
    string title { get; set; }
    string content { get; set; }
    long user_id { get; set; }
}

class user
{
    long user_id { get; set; }
    string name { get; set; }
}

and a query like this:

db.Query<article, user, article>( ... )

The field of interest here is the user_id. When mapping this result set, the first user_id column will be mapped to the article POCO. On seeing the second user_id column PetaPoco will realize it's already mapped mapped articles property and start mapping onto the user POCO.

The final way a split point is determined is when a column doesn't exist in the current POCO type but does in the next. Note that if a column doesn't exist in the current POCO type, nor in the next POCO type it's ignored.

Auto Joining POCO's

PetaPoco can also guess the property relationships and automatically assign the object references on the returned objects.

So instead of this:

var result = db.Query<article, user, article>( (a,u)=>{a.user=u; return a }, sql);

You can just write this:

var result = db.Query<article, user>(sql);

Note two things about this:

The third return type parameter isn't required. The returned collection will always be of type T1.
The callback method to setup the object relationships isn't required.

Obviously there's a bit of guessing by PetaPoco to make this work, but it's a common enough case that I think it's worth it. For it to work, T2through T5 must have one property of the same type on one of the types to it's left. In other words: