Can PetaPoco populate a list of view models that contain multiple POCOs inside each?

I would like to populate the CharacterViewModel list with a single query if possible, but I'm not sure how PetaPoco can even do something like this. This is what the object and request looks 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 would need to tell PetaPoco to map each JOIN to the corresponding POCO in the view model. Is this possible or am I not mistaken?

+3


source to share


2 answers


It worked great! There was no need for a viewmodel at all with PetaPoco handling nested relationships and I could get a list of characters populated with foreign objects. I used a code generator to generate classes directly from database tables and created partial members to host [ResultColumn] properties. Here's how it happened:

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 helping me in the right direction of CallMeKags :)

0


source


Change your POCOs like this:

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
}

      

Change 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.

+1


source







All Articles