Can PetaPoco poplulate a list of view models which contain multiple POCOs within each?

Question

I'd like to populate a list of CharacterViewModel with a single query if possible, but I'm unsure how, nor if PetaPoco can even do something like this. This is what the object and query look like:

    public class CharacterViewModel
{
    public Character Character { get; set; }
    public Entity Entity { get; set; }
    public Faction Faction { get; set; }
}


var characters = db.Query<CharacterViewModel>(
    @"SELECT c.*,e.*,f.*
        FROM [Character] c
        INNER JOIN [Entity] e ON e.Id = c.EntityId
        INNER JOIN [Faction] f ON f.Id = e.FactionId
        WHERE c.UserId = @0", 1)

Somehow I'd need to tell PetaPoco to map each JOIN to the respective POCO within the view model. Is this possible or am I going about it the wrong way?

Answer 1

This worked great! There was no need for a viewmodel at all with PetaPoco handling the nested relationships, and I could get the list of characters with the foreign objects populated. I used the code generator to create the classes directly from the database tables, and created partials to place the [ResultColumn] properties in. Here's how it ended up looking:

public partial class Character
{
    [ResultColumn]
    public Entity Entity { get; set; }
}

public partial class Entity 
{
    [ResultColumn]
    public Faction Faction { get; set; }
}

sql = Sql.Builder
    .Append("SELECT c.*,e.*,f.*")
    .Append("FROM [Character] c")
    .Append("INNER JOIN [Entity] e ON e.Id = c.EntityId")
    .Append("INNER JOIN [Faction] f ON f.Id = e.FactionId")
    .Append("WHERE c.UserId = @0", 1);

var characters = db.Fetch<Character, Entity, Faction, Character>(
    (c, e, f) => { c.Entity = e; e.Faction = f; return c; }, sql);

Thanks for steering me in the right direction CallMeKags :)