Say you have this query which describes a one-to-many relationship between TableA (one) and TableB (many):

SELECT * FROM TableB
JOIN TableA ON TableB.ForeignKeyId = TableA.Id

Let's look at a real world query to demonstrate:

SELECT * FROM ProductFeature
JOIN Product ON ProductFeature.ProductId = Product.Id
WHERE Product.Id = 1

Let's say we also have some C# models that represent the two tables:

public class Product 
{
    public Product() {
        Features = new List<ProductFeature>();
    }
    public int Id { get; set; }
    public string Title { get; set; }
    public string Slug { get; set; }
    public IList<ProductFeature> Features { get; }
}

public class ProductFeature 
{
    public int Id { get; set; }
    public string FeatureName { get; set; }
    public string FeatureSlug { get; set; }
}

It doesn't stretch the imagination to now say that it is likely that we need some way of mapping our database tables to code, otherwise we will never be able to show that lovely data to our users! 

Now if you are using the micro object relational mapper Dapper, you could use a call to the QueryMultiple method, which allows you to run multiple SQL statements within the same context and eventually map the results of each table into the relationship above. However sometimes JOINS are necessary for performance reasons (particularly if you have lots of tables to JOIN through to), so we end up with a query result like below:

ProductId Title Slug ProductFeatureId FeatureName FeatureSlug
1 A sample product sample-product 4 It's awesome awesome
1 A sample product sample-product 5 It's bad bad

As you can see, we have some redundancy here as the data relating to TableA (i.e. the Product table) is duplicated for every row found for product ID 1 in the ProductFeature table. However this is not a huge problem in my mind.

So now that we've ascertained we need to map the relationship between the two tables to our models using just a simple JOIN query instead, we think to ourselves "This should be easy with Dapper right?" No. Unfortunately, according to this answer by one of Dapper's primary maintainers, Sam Saffron, this is not possible:

"It is just not the way the API was designed. All the Query APIs will always return an object per database row."

However, someone who is a lot more ofay with the wonders of Func has produced an extension method that will allow you to map this kind of query:

public static IEnumerable<TParent> QueryParentChild<TParent, TChild, TParentKey>(
    this IDbConnection connection,
    string sql,
    Func<TParent, TParentKey> parentKeySelector,
    Func<TParent, IList<TChild>> childSelector,
    dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
    Dictionary<TParentKey, TParent> cache = new Dictionary<TParentKey, TParent>();

    connection.Query<TParent, TChild, TParent>(
        sql,
        (parent, child) =>
            {
                if (!cache.ContainsKey(parentKeySelector(parent)))
                {
                    cache.Add(parentKeySelector(parent), parent);
                }

                TParent cachedParent = cache[parentKeySelector(parent)];
                IList<TChild> children = childSelector(cachedParent);
                children.Add(child);
                return cachedParent;
            },
        param as object, transaction, buffered, splitOn, commandTimeout, commandType);

    return cache.Values;
}

And we use this wonder of an extension method like so:

using MyProject.ExtensionMethods;

public class HomeController : Controller
{
    public ActionResult Index()
    {
        IList<Product> collection = null;

        using (var connection = new SqlConnection("XXX"))
        {
            connection.Open();

            string sql = "SELECT * FROM Product JOIN ProductFeature WHERE Product.Id = 1";

            collection = connection.QueryParentChild<Product, ProductFeature, int>(sql, p => p.Id, p => p.Features, splitOn: "ProductFeatureId").ToList();

        }
        return View(collection);
    }
}

Cool huh? Never as good as the almightyness of a fully featured ORM like Fluent NHibernate, but then again not all projects need that kind of gnarlyness.