PetaPoco and Many-to-One, One-to-Many and Many-to-Many relations

Question

PetaPoco has introduced Multi-POCO queries in experimental form (for now). As their blog post suggests and the code it provides this looks nice and all in One-to-One relations when we load multi POCOs per row as long as they don't repeat over the records.

What happens when at least one side is many relation? Actually example code is Many-to-One relational data.

Example code is clearly a Many-to-One relation. I haven't tested any PetaPoco code but what does the provided code on the blog post do? Does every Article have their own User object instance even though some may be the same user or do they share the same user object instance?

And what about other Many relation types? How do they work of they work at all?

Ard

Answer 1

Usually I map these one-to-many queries myself like the following example.

[TableName("Blogs"), PrimaryKey("BlogId")]
public class Blog {
    public int BlogId {get;set;}
    public string Title {get;set;}

    [Ignore]
    public IList<Post> Posts {get;set;}
}

[TableName("Posts"), PrimaryKey("PostId")]
public class Post {
    public int PostId {get;set;}
    public int BlogId {get;set;}
    public string Subject {get;set;}
    public string Content {get;set;}
}

public class FlatBlogPost {
    public int BlogId {get;set;}
    public string Title {get;set;}
    public int PostId {get;set;}
    public string Subject {get;set;}
    public string Content {get;set;}
}

There are two ways I could display a list of posts for one blog or without too much work, all blogs.

1.Two queries -

var Blog = Db.Query<Blog>(1);  
var Posts = Db.Query<Post>("where BlogId = @0", 1);

2.One query =

var flat = Db.Query<FlatBlogPost>("select b.blogid, b.title, p.postid, p.subject, 
           p.content from blogs b inner join posts p on b.blogid = p.blogid where
           b.blogid = @0", 1);

var blog = flat
    .GroupBy(x=> new { x.BlogId, x.Title })
    .Select(x=> new Blog {
        BlogId = x.Key.BlogId,
        Title = x.Key.Title,
        Posts = x.Select(y=> new Post{
                    PostId = y.PostId,
                    BlogId = x.Key.BlogId,
                    Subject = y.Subject,
                    Content = y.Content
                }).ToList()
    });

However usually in number 2 I would map directly from the FlatBlogPost object to my viewmodel for which I need to display the data.

Update
Check out these helpers which extend PetaPoco to support basic One-to-Many and Many-to-One queries. schotime.net/blog/index.php/2011/08/21/petapoco-one-to-many-and-many-to-one/ https://schotime.wordpress.com/2011/08/21/petapoco-one-to-many-and-many-to-one/

Perugia

This isn't really nice. It's a hackish workaround (one of the things why I don't like full ORMs because you end up hacking code to make ti work and to make it work fast(er)). Support for multi result set wouldn't be too hard now would it? It's definitely less work than auto spliting columns. Dapper supports multi resultsets why couldn't PetaPoco do the same. You wouldn't need flat classes and you'd also transfer less data from DB to mid-tier. – Ard

But your answer still deserves a +1 since it is a possible solution. – Ard

Answer 2

My 'One to Many' recipe for Petapoco is below. The docs are not clear enough for me. Create a db connection in Linqpad, it will show you all Navigation properties you can add to generated Petapoco poco classes. Execute the same SQL in Linqpad, to make sure it gets the data you expect.

// subclass the generated Parent table pocos, add navigation prop for children
[ResultColumn]  public List<DecoratedChild> Child { get; set; } 

// subclass the generated Child table pocos,  add navigation prop for parent  
[ResultColumn]  public DecoratedParent Parent { get; set; }      

// to get children with parent info
List<DecoratedChild> children = db.Fetch<DecoratedChild, DecoratedParent>(SELECT child.*, parent.* from ...)     

// to get children with parent info, using PetapocoRelationExtensions
List<Child> children = db.FetchManyToOne<Child, Parent>(child => child.ID, "select child.*, parent.* from ...

// to get parents with children info, using PetapocoRelationExtensions              
List<Parent> parents = db.FetchOneToMany<Parent, Child>(par => par.ID, child => child.ID != int.MinValue, "select parent.*, child.* from ...    

SQL select order important, same as in Fetch types list !!! navigation props will have parent or children data ... with 3 levels the call will be like:

List<DecoratedGrandChild> grandChildColl = db.Fetch<DecoratedGrandChild, DecoratedChild, DecoratedParent>(SELECT grandch.* , child.*, parent.* from ...)

Kerianne

Answer 3

Personally I don't think you can avoid another database call to get the comments. You could get a list of all comments for the 10 articles (in the same order the articles are stored) by using an IN clause, and loop through them adding them to each article.comments as you go along and the comment.articleid changes. The only way I can see getting this information in a single sql call would be to use a join but then you'd get duplicate article details for each comment, so maybe this isn't a problem with petapoco, just one of those things that'll never be perfect

Jennee

The latest PetaPoco test methods show how many-to-one and one-to-many is possible github.com/toptensoftware/PetaPoco/commit/… – Jennee

Wouldn't their one-to-many AuthorPostRelator example require the set to be ordered by author ID? It holds onto an author until the author changes. Yet their examples order by the post ID, not the author ID. I don't get it. – Riehl