Table per Subclass Inheritance with PetaPoco / NPoco

Question

I'm using version 2.6.88 of NPoco Micro-ORM.

Lets say I have a class hierarchy like this:

[NPoco.TableName("Person")]
[NPoco.PrimaryKey("PersonID", AutoIncrement = false)]    
class Person
{
    public Guid PersonID { get; set; }
    public String Name { get; set; }
}

class Employee : Person
{
    public String Department { get; set; }
}

I need to map this class hierarchy to a database designed with the "Table per Subclass" approach. This means I have Person table with columns PersonID and Name, and I have a Employee table with columns PersonID and Department.

Now my question is how do I insert a new Employee into the database with NPoco? I tried something like this:

Employee myEmployee = new Employee() { PersonID = Guid.NewGuid(), Name = "Employee Name", Department = "TestDepartment" };
NPoco.Database myDb = new NPoco("MyConnection");
using (var transaction = myDb.GetTransaction())
{
    myDb.Insert<Person>(myEmployee as Person);
    myDb.Insert<Employee>("Employee", "PersonID", false, myEmployee);
    transaction.Complete();
}

This code fails on the first insert, because NPoco tries to insert the Employee specific fields into the Person table.

How do I implement this correctly?

score 0 · Answer 1 · answered Mar 12 '15 at 21:11

0

If you are using NPoco, then you begin a transaction by invoking myDb.GetTransaction(); and commit a transaction using transaction.Complete(); but not in the manner reflected in your code.

After modifying your code, you can either use:

using (var transaction = myDb.GetTransaction())
{
   myDb.Insert<Person>(person);
   var sql = new Sql("INSERT INTO Employee (Department,PersonId) VALUES(@0, @1)", employee.Department,  employee.PersonId );
   myDb.Execute(sql);
   transaction.Complete();
}

or add a Name column to your Employee table and use the following to insert a new Employee

using (var transaction = db.GetTransaction())
{
    myDb.Insert<Person>(person);
    myDb.Insert<Employee>("Employee", "PersonId", false, employee);
    transaction.Complete();
}

answered Mar 12 '15 at 21:11

kagundajm

1,152
1
15
26

Thank you for your answer! I will update my question regarding the transaction handling. – Robert Hegner Mar 13 '15 at 07:05
However I can't see how any of the two approaches you present in your answer would solve my problem. I don't have a separate `person` and `employee` object to insert. I only have the `myEmployee` object which is of type `Employee` but which is also a `Person` (inheritance). And regarding your second approach: I can't add the `Name` column to the `Employee` table - that wouldn't be "table per subclass" anymore. – Robert Hegner Mar 13 '15 at 07:12
1

The following will insert into both tables without an error. `var sql = new Sql("INSERT INTO Person (Name,PersonId) VALUES(@0, @1)", myEmployee.Name, myEmployee.PersonId); myDb.Execute(sql); sql = new Sql("INSERT INTO Employee (Department,PersonId) VALUES(@0, @1)", myEmployee.Department, myEmployee.PersonId ); myDb.Execute(sql);` – kagundajm Mar 14 '15 at 05:01

Table per Subclass Inheritance with PetaPoco / NPoco

1 Answers1