Tag Archives: One-to-Many

August 21, 2011   .NET   6 Comments

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

Read More