How to insert a PetaPoco object that has a property of another class (foreign key)?

Question

To simplify and shorten my question, let's say that I have the following classes:

[TableName("persons")]
[PrimaryKey("person_id")]
public class Person {

     [Column("person_id")]
     public int Id { get; set; }

     public string Name { get; set; }

     [Column("car_id")]
     public Car Car { get; set; }
}

[TableName("cars")]
[PrimaryKey("car_id")]
public class Car {
    [Column("car_id")]
    public int Id { get; set; }
    public string Color { get; set; }
    [Column("year")]
    public int YearOfMake { get; set; }
}

Again, for the sake of simplicity, let's imagine that each Person has one Car, and that's how the database is currently at (I know that I can simply use one table for that, but this is only a simplification).

I am able to fetch the Person object along with its Car property using:

using(var db = GetDatabase())
{
    string sql = "select * from persons p left join cars c on c.car_id = p.car_id";
    Person p = db.Fetch<Person, Car>(sql).FirstOrDefault();
    Console.WriteLine(String.Format("Person: {0} has a {1} car", p.Name, p.Car.Color));
}

The first problem I faced is if two columns have the same names in the database result tables, then PetaPoco sets both of their values in my objects to ZERO. I managed to rename the columns to avoid that problem.

The real problem is when I try to save/insert a Person object. It works if the object has only primitive value types, but Car object makes things difficult.

Here is what I tried:

Person p2 = new Person { Name = "mike", Car = new Car {Id = 42, Color = "red", YearOfMake = 2003 }};

db.Insert<Person>(p2);      // error: can't convert `Car` to `Int32`
db.Insert<Person, Car>(p2); // error: can't convert `Car` to `Int32`

How can I insert the object that has a a class type as one of its properties?

Answer 1

PetaPoco won't handle wiring relationships like this for you. You have to manage the dependencies yourself.

Please see how I've done it in this integration test

Generally, you'd create a few helper methods to handle this for you ;)