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?
source to share
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 :)
source to share
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.
source to share