PetaPoco – One To Many and Many To One

PetaPoco is a great way to map results from a database. Usually these are flat objects, however sometimes its useful to map many-to-one/one-to-many relationship directly into a viewmodel or complex object.

This feature has been possible in PetaPoco since version 4 as shown in the blog post by Brad, (http://www.toptensoftware.com/Articles/115/PetaPoco-Mapping-One-to-Many-and-Many-to-One-Relationships) however, custom mapping needed to be created each type. Therefore I decided to have a crack at a more generic way of mapping these.

You can grab it now and give it a try. Its available on NuGet under PetaPoco.RelationExtensions.

The extensions add two new methods to the Database class. They come with a variety of overloads for a multitude of generic arguments.

  1. FetchOneToMany<>
  2. FetchManyToOne<>

This is how it works:

var results1 = db.FetchOneToMany<BudgetPeriod, Expense>(x => x.BudgetPeriodId,
        "select b.*, e.* from budgetperiods b " +
        "   inner join expenses e on b.budgetperiodid = e.budgetperiodid");

var results2 = db.FetchManyToOne<Expense, BudgetPeriod, BudgetPeriod>(x => x.ExpenseId,
        "select e.*, b.*, b2.* from budgetperiods b " +
        "   inner join expenses e on b.budgetperiodid = e.budgetperiodid " +
        "   inner join budgetperiods b2 on e.budgetperiodid = b2.budgetperiodid ");

results1 will return a List<BudgetPeriod>

results2 will return a List<Expense>

There are some key things to remember when using these.

  1. It is critical that the columns that you want to map into the class are in the same order you specify the generic arguments as seen in the results2 example above. eg. Expense, BP, BP –> e.*, b.*, b2.*
  2. The first parameter is a lamda which should refer to the primary key of the first T argument.

Currently you can only have 1 one-to-many relation mapped and up to 3 many-to-one relations mapped in one go.

Enjoy

Adam

6 Responses to PetaPoco – One To Many and Many To One

  1. Gareth Elms says:

    Good stuff Adam this is another excellent addition to PetaPoco. I’ve just implemented this in my Github PetaPoco sample if anyone wants to see it in action. Cheers

  2. gambitricky says:

    Would be nice to have db.OneToManySingleOrDefault and db.OneToManySingle versions for the case you want:SELECT a.*, b* FROM auth a LEFT JOIN posts p ON … WHERE a.ID = @0

  3. song says:

    How would I populate the following hierarchy?

    public class Blog()
    {
    public int Id {get;set;}
    public string BlogName {get;set;}

    public List Articles {get;set;}
    }

    public class Article()
    {
    public int Id {get;set;}
    public string ArticleTitle {get;set;}

    public List Tags {get;set;}
    }

    public class Tag()
    {
    public int Id {get;set;}
    public string TagName {get;set;}
    }

  4. Schotime says:

    With a couple of selects.

    Probably a OneToMany of Blog to Article.
    Then get a list of TagId’s from the articles and query the Tags table.

    eg.

    1. select * from blogs b inner join articles a on b.blogid = a.blogid where blogid = 1;
    2. select * from tags t where id in (/*ids from the first select*/)

  5. Bamboo says:

    Good work! Any idea on many to many?

  6. Schotime says:

    Same as one to many.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>