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?

score 1 · Answer 1 · answered Sep 29 '14 at 20:44

Modify your POCOs as follows:

public class Faction 
{
  // Other properties

  [PetaPoco.ResultColumn]
  public Entity Entity { get; set; }
}


public class Entity
{
  // Other properties

  [PetaPoco.ResultColumn]
  public Character Character{ get; set; }
}


public class Character
{
  // Properties of character object
}

Modify your query syntax as follows:

var 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.Query<Character, Entity, Faction, Faction>(
       (c, e, f) => { f.Entity = e; e.Character = c; return f;}, sql);

This should return an object graph of the Faction object. Note that the fourth parameter (Faction) is the return type of the items in the collection.

I think I see where you are going with this. In my case I'd want a list of Character returned with the respective Entity populated within each Character, and the Faction of each Entity populated within each Entity. I'll give that a try. — Sgraffite, Sep 30 '14 at 18:36

score 0 · Accepted Answer · answered Oct 04 '14 at 19:33

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 :)

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

2 Answers2