Multiple joins to the same type in PetaPoco

Question

Question: I'm trying to use PetaPoco to join more than four tables to populate an object of type A with five members of type B (very similiar to this question: https://stackoverflow.com/a/11275334/296296). From what I've read it would be possible to use this syntax:

var result = db.Query<Investment>(new System.Type[] { typeof(Investment), typeof(Person), typeof(Person), typeof(Person), typeof(Person), typeof(Person) }, null, sql, null).FirstOrDefault();

Where the sql is:

        SELECT Investment.*, p1.*, p2.*, p3.*, p4.*, p5.* FROM Investment
        INNER JOIN People p1 ON Investment.OwnerID = p1.Id
        INNER JOIN People p2 ON Investment.ITOwnerID = p2.Id
        INNER JOIN People p3 ON Investment.InformationOwnerId = p3.Id
        INNER JOIN People p4 ON Investment.MaintenanceLeaderId = p4.Id
        INNER JOIN People p5 ON Investment.MaintenanceLeaderITId = p5.Id
        WHERE (Investment.Id = @0)

But it just gives me the following error:

 Can't auto join Person as Investment has more than one property of type Person

Anyone who had the same problem or could help in any way?

Background:

I have the following (simplified) database tables:

Investment
--------------
Id
Name
OwnerId
ITOwnerId
InformationOwnerId
MaintenanceLeaderId
MaintenanceLeaderITId

People
--------------
Id
Name

The classes I would like to map these database tables to are:

 Public class Investment {
    public int Id 
    public string Name
    public Person Owner
    public Person ITOwner
    public Person InformationOwner
    public Person MaintenanceLeader
    public Person MaintenanceLeaderIT
 }

 Public class Person {
    public int Id 
    public string Name
 }

In order to do so, I need to join the People table for every Person-type in the Investment class and map them to the corresponding property as instances of the type Person.

Answer 1

The only way you could possibly do this is create your own callback (instead of letting it fall back to the self discovering callback thats currently being invoked) where you wire up each Person object on the Investment object.

Take a look at http://www.toptensoftware.com/Articles/115/PetaPoco-Mapping-One-to-Many-and-Many-to-One-Relationships

Answer 2

why can't you do this :

/////SQL CODE for testing the selects

DECLARE @People TABLE (Id INT,  Name VARCHAR(50))
        INSERT INTO @People (Id,Name)
            SELECT 1,' John Smith' UNION ALL
            SELECT 2,'Albert Lee' UNION ALL
            SELECT 3,'Christina Wetherbe' UNION ALL
            SELECT 4,'Alice Cany' UNION ALL
            SELECT 5,'Jim Blabery' UNION ALL
            SELECT 6,'Octaviose Mayflower' UNION ALL
            SELECT 7,'Sandra Lee M' UNION ALL
            SELECT 8,'Some test user' UNION ALL
            SELECT 9,'Some test user 2' UNION ALL
            SELECT 10,'Some test user 3' UNION ALL
            SELECT 11,'Some test user 4' 


    DECLARE @Investment TABLE (
                    Id INT,
                    Name VARCHAR(50),
                    OwnerId INT,
                    ITOwnerId  INT,
                    InformationOwnerId INT,
                    MaintenanceLeaderId INT,
                    MaintenanceLeaderITId INT
                )


    INSERT INTO @Investment(Id,Name,OwnerId,ITOwnerId,InformationOwnerId,MaintenanceLeaderId,MaintenanceLeaderITId)
    SELECT 1,'INVESTMENT 1',1,2,1,3,4 UNION ALL
    SELECT 2,'INVESTMENT 2',1,3,2,3,2 UNION ALL
    SELECT 3,'INVESTMENT 3',3,1,3,3,4 UNION ALL
    SELECT 4,'INVESTMENT 4',5,4,4,2,3 UNION ALL
    SELECT 5,'INVESTMENT 5',6,5,5,7,6 UNION ALL
    SELECT 6,'INVESTMENT 6',8,6,6,7,8 UNION ALL
    SELECT 7,'INVESTMENT 7',9,8,7,4,5 UNION ALL
    SELECT 8,'INVESTMENT 8',11,8,8,6,11 UNION ALL
    SELECT 9,'INVESTMENT 9',10,9,9,10,9


    --SELECT * FROM @People
    --SELECT * FROM @Investment

     -- THIS IS YOUR SELECT STATEMENT to be uses in PetaPoco call
    SELECT unpv.Id,unpv.Name, unpv.INVTYPE,unpv.PersonId,p.Name FROM @Investment
    UNPIVOT(PersonId for INVTYPE in    (OwnerId,ITOwnerId,InformationOwnerId,MaintenanceLeaderId,MaintenanceLeaderITId))unpv 
    join @People p on unpv.PersonId = p.Id
    order by INVTYPE, PersonId

Then C# code as

1 - Extend your Investment POCO to have 2 more columns, INVTYPE and PersonId. Just create new object in the same name-space. Like so:

    public partial class Investment
    {
        [ResultColumn]
        public string INVTYPE { set; get; }
        [ResultColumn]
        public int PersonId { set; get; }
    }

2 - create viewclass InvestmentView (you can use your Investment class for that, just rename it)

   var myInvestmentView = new InvestmentView
                            {
                              Id = result.Id,
                              Name = result.Name,
                              Owner = new Person{ Id = result.firstOrDefault(o => o.INVTYPE.Equals("OwnerId")).PersonId, Name =  result.firstOrDefault(o=> o.INVTYPE.Equals("OwnerId")).PersonName},
                              ITOwner  = new Person{ Id = result.firstOrDefault(o => o.INVTYPE.Equals("ITOwnerId")).PersonId, Name =  result.firstOrDefault(o=> o.INVTYPE.Equals("ITOwnerId")).PersonName},
                              InformationOwner  = new Person{ Id = result.firstOrDefault(o => o.INVTYPE.Equals("InformationOwnerId")).PersonId, Name =  result.firstOrDefault(o=> o.INVTYPE.Equals("InformationOwnerId")).PersonName},
                              MaintenanceLeader = new Person{ Id = result.firstOrDefault(o => o.INVTYPE.Equals("MaintenanceLeaderId")).PersonId, Name =  result.firstOrDefault(o=> o.INVTYPE.Equals("MaintenanceLeaderId")).PersonName},
                              MaintenanceLeaderIT = new Person{ Id = result.firstOrDefault(o => o.INVTYPE.Equals("MaintenanceLeaderITId")).PersonId, Name =  result.firstOrDefault(o=> o.INVTYPE.Equals("MaintenanceLeaderITId")).PersonName}
                            }  

3 - populate the class with data from the list returned

    using (var data = new Database(Config.MainDbConnectionName))
    {
       var result = data.Fetch<Investment,People>(
                        Sql.Builder
                           .Append("SELECT unpv.Id,unpv.Name, unpv.INVTYPE,unpv.PersonId,p.name as PersonName FROM Investment")
                           .Append(" UNPIVOT(PersonId for INVTYPE in    (OwnerId,ITOwnerId,InformationOwnerId,MaintenanceLeaderId,MaintenanceLeaderITId)) unpv")
                           .Append(" JOIN People p on unpv.PersonId = p.Id")
                           .Append(" WHERE (Investment.Id = @0)",InvId)
                           .Append(" ORDER BY INVTYPE, PersonId")
                    );
    }

this way the only class that will need the special treatment will be Investment class as you would need to process the data backwards from InvestmentView into flat structure of POCO.