PetaPoco - Working with Joins

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

Normally when working with PetaPoco there's a fairly direct mapping of C# class to database table. This works well most of the time, but what happens when you want to do a JOIN - you've now got more columns than the C# classes have properties to hold.

Approach 1 - Manually Define a New POCO class

The first approach is to simply create a new class to hold all the columns of the join. Lets say you wanted a list of article titles and a count of comments on each article. The SQL might look like this:

SELECT articles.title, COUNT(comments.comment_id) as comment_count
FROM articles
LEFT JOIN comments ON comments.article_id = articles.article_id
GROUP BY articles.article_id;

Note: all the examples in this post are manually typed and almost certainly contain typos. Let me know if you find one

Define a C# class to hold the results (note that the property names match the column names in the SQL)

public class ArticleWithCommentCount
{
    public string title 
    { 
        get; 
        set; 
    }

    public long comment_count
    {
        get;
        set;
    }
}

Run the query using your new class type:

var articles = db.Fetch<ArticleWithCommentCount>(sql);

Approach 2 - Extend Your Existing POCOs

A more likely scenario is that you already have most of the join's columns represented by an existing POCO and all you want to do is join in an extra column or two.

Taking the same example as above, you probably already have an article object and you just want to add the comment_count property. This is where the [ResultColumn] attribute comes in... just add the new property to your existing class:

[ResultColumn]
public long comment_count
{
    get;
    set;
}

By declaring a property as a [ResultColumn] it will be populated if a result set has a matching column name, but ignored for updates and inserts.

Approach 3 - Extend the POCOs in the T4 Templates

The above approach is fine if you're manually writing your own POCO classes. But what if you're using PetaPoco's T4 templates to write those classes for you? How you extend those classes in a way such that re-running the template won't overwrite your new properties. The answer lies in the fact that the T4 template generates partial classes.

If you don't know what a partial class is, I suggest you read this. If you know what a partial class is, you already know the rest of this story, but here goes anyway...

Taking the above article/comment count example again, add a new class to your project with the same name as the class generated by the T4 template (make sure the namespace matches too). Declare the class as partial, and add the [ResultColumn] property for any joined columns:

public partial class article
{
    [ResultColumn]
    public long comment_count
    {
        get;
        set;
    }
}

Here's the final query (using PetaPoco's SQL builder this time).

var articles = db.Fetch<article>(PetaPoco.Sql.Builder
                .Append("SELECT articles.title, COUNT(comments.comment_id) as comment_count")
                .Append("FROM articles")
                .Append("LEFT JOIN comments ON comments.article_id = articles.article_id")
                .Append("GROUP BY articles.article_id")
                );

Approach 4 - Object References to Other POCOs

Of course it would be nice (perhaps) if PetaPoco could map joined tables with property object references - like a fully fledged ORM would do. PetaPoco can't do this though, nor will it ever - it's just not worth the complexity and is not a problem PetaPoco was ever designed to solve.

UPDATE: Approach 5 - Use C# 4.0's dynamic

Since originally posting this article, PetaPoco has been updated to support C# dynamic expando objects. This provides a great way to handle joins, group by and other calculated queries. See here for more.