PetaPOCO and more than 4 joins

Question

Is petapoco capable of achieving the following : 1.Unlimited joins in one query 2.Unlimited One to Many relations in one query

I have looked at PetaPOCO and it seems like it is not capable of doing more than 4 joins, the longest signature looks like : db.Query<T1, T2, T3 , T4>

Also seems like it supports a one to many relation , but only for one composite object such as below : db.FetchOneToMany<T1, T2> where T2 is a foreign key of T1

I'm testing some of the micro ORMs out there to stick to the best one. Do you know of any of them that can handle these situations and if none of the micro ORMs are supporting this feauture, how do you deal with an object that is like the following :

class A
{
    List<B> member1;
    List<C> member2; 
    Z  member3; //Composit object
    Z1 member4; //Composit object
    Z2 member5; //Composit object
    Z3 member6; //Composit object
    Z4 member7; //Composit object
}

And then even more complicated is , what if member one (type B) has some composite object within itself ? What if we have :

class B
{
    G member0;
}

Please don't propose a solution to hit database multiple times, coz it's going to be way too many calls when the objects become just a little bit complex.

Oh and i also know that one other way of tackling the case of unlimited joins is creating a very flat object that hols all fields combined. It's not an elegant solution at all.

And even worse, what about a class that has a list and the list has a list and that last list has a composite object !!? — James, Jun 29 '12 at 20:38
And you need all this data for one screen? Having a list that has a list is already gonna create a query that has way to much duplicate data (a cartesian product). Usually I try and flatten the DTO that I return data to, remember you can map to any class by property name. — Schotime, Jun 29 '12 at 22:51
but what about the lists tho? coz per each list, i'd have to make one call, and then per each composite object inside that list, i would have to make another call to grab that! would be a lot of calls — James, Jun 30 '12 at 00:05
Try creating a view model in C# that flattens out the underlying objects. Then hook it up to a view in your database to flatten the joins. — gidmanma, Mar 19 '13 at 14:44

score 4 · Accepted Answer · answered Jun 30 '12 at 15:30

The T1..T$ Query() overloads all pass through to the main Query(..Type[]..) method. You can either add more Query() overloads yourself to handle more T parameters, or pass in all the types you need in a Type array (which is what the T1-T4 functions do) :

Query<TRet>( new Type[]{typeof(Poco1), typeof(Poco2), typeof(Poco3), typeof(Poco4), typeof(Poco5)}, null, sql, args);

You can have multiple one to many relationships but Schotime is right, you need to be very careful of swathes of duplicate data coming back in your result set. Write the sql query and look at the result set, is the amount of duplication acceptable to you? If so then in Petapoco there is a concept of relator callbacks where you write a small class that handles the different pocos in a single result row and add each poco to the list properties on the parent poco.

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

I've never had to do this with multiple one to many but quoted from the above

"If you're joining more than two tables you'll need something more complex but it's really just extensions of the above."

Another option is to have a stored procedure that does all the work in a single database request and have it return multiple result sets which I believe Schotime has achieved in his branch of petapoco but I've not used it myself yet so I can't really comment on if it will help here :

http://schotime.net/blog/index.php/2011/11/20/petapoco-multiple-result-sets/

If I absolutely had to wire up all the data in one go for objects as complex and nested as you are suggesting then I would use a stored procedure (a single db call) and stitch it all together with code. Only then would I figure out how to do this in Petapoco. However if your UI doesn't show all the nested data until the user clicks on an expander button (or similar) I'd use an AJAX call at that point rather than get all the data initially.

Passing in a Type array solved my very annoying problem. Thanks for the help! — grokmann, Feb 19 '14 at 17:13
There is a parameter, `object cb`. How are you supposed to create this? Do you just create a Func and cast it to object? Very bad documentation. — The Muffin Man, Aug 08 '17 at 03:36

score 0 · Answer 2 · answered Mar 05 '18 at 21:01

The answer is correct, but I came to this page from another forum and no one there could make this work, so I thought I would chip in what I did to make things clearer. Basically, I had code like the following:

var sql = "select * from someTable where tableId = @0";
var listOfStuff = _petapoco.Fetch<FirstType, SecondType, ThirdType, FourthType, FirstType>(new RelatorClass().MapIt, sql, idVar);

Since I needed to add in a fifth poco, and all the Fetch methods eventually lead to the master Query method listed above in the accepted answer, I had to do this:

var sql = "select * from someTable where tableId = @0";
Func<FirstType, SecondType, ThirdType, FourthType, FifthType, FirstType> mapIt = new RelatorClass().MapIt;
var listOfStuff = _petapoco.Query<FirstType>(new[] { typeof (FirstType), typeof (SecondType), typeof (ThirdType), typeof (FourthType), typeof(FifthType)}, mapIt, sql, idVar).ToList();

Now I can query with 5 pocos and I didn't have to modify the PetaPoco code. The only other thing to do would be to add to your relator class so you can tell PetaPoco where to map the new data and you're good to go.

score 0 · Answer 3 · answered May 15 '21 at 17:09

Delegate: Note: you don't have to return the (UserActivity,int) anonymous type like this, you can return a single type without the parenthesis! I'm just lazy and don't want to create a new model for the return type.

private delegate (UserActivity, int) GetIt(int fk_AccountTypeValue, UserActivityModel ua, User u, Client c, Client_Account ca);

SQL Operation:

public List<(UserActivity,int)> SomeMethodName(int orgnizationID)
        {
                    var sql = Sql.Builder
                        .Select("TOP(200) at.FK_AccountTypeValue, ua.*, u.*, c.*, ca.*")
                        .From("UserActivity ua")
                        .LeftJoin("Users u").On("u.PK_UserID = ua.FK_UserID")
                        .LeftJoin("Client c").On("c.FK_UserID = u.PK_UserID")
                        .LeftJoin("Client_Account ca").On("ca.FK_ClientID = c.PK_ClientID")
                        .LeftJoin("AccountType at").On("at.PK_AccountType = c.FK_AccountTypeID")
                        .Where("u.FK_OrganizationID =@0", orgnizationID)
                        .OrderBy("ua.Timestamp desc");
                    GetIt obj = new GetIt(youKnowIt);
                    var typs = new Type[]{typeof(int), typeof(UserActivityModel), typeof(User), typeof(Client), typeof(Client_Account)};
                    var uaList = _database.Query<(UserActivity, int)>(typs, obj, sql.SQL, sql.Arguments).ToList();
                    return uaList;
        }

Method pointed to by the delegate:

private (UserActivity,int) youKnowIt(int fk_AccountTypeValue, UserActivityModel ua, CurrentDesk.Models.User u, CurrentDesk.Models.Client c, CurrentDesk.Models.Client_Account ca)
        {
            // do stuff
            var uam = new UserActivity()
            {
                // assign stuff
            };
            return (uam, fk_AccountTypeValue);
        }

PetaPOCO and more than 4 joins

3 Answers3

Linked