What's the best way to save a one-to-many relationship in Linq2Sql?

Question

Hi folks,

I'm trying to figure out the best way to save a simple one-to-many relationship in Linq2Sql.

Lets assume we have the following POCO model (pseduo code btw):

Person has zero to many Vechicles.

class Person
{
    IList<Vehicle> Vehicle;
}

class Vehicle
{
    string Name;
    string Colour;
}

Now, when i save a Person, i pass that poco object to the repository code (which happens to be L2S). I can save the person object fine. I usually do this.

using (Db db = new Db())
{
    var newPerson = db.People.SingleOrDefault(p => p.Id == person.Id) ?? new SqlContext.Person();
    // Left to right stuff.
    newPerson.Name = person.Name;
    newPerson.Age = person.Age;

    if (newPerson.Id <= 0)
        db.People.InsertOnSubmit(newPerson);
    db.SubmitChanges();
}

i'm not sure where and how i should handle the list of vehicles the person might have? any suggestions?

Answer 1

using (Db db = new Db())
{
    var newPerson = db.People.SingleOrDefault(p => p.Id == person.Id) ?? new SqlContext.Person();
    // Left to right stuff.
    newPerson.Name = person.Name;
    newPerson.Age = person.Age;

    // add vehicles.
    Vehicle firstV = new Vehicle();
    firstV.Name = "some name";
    firstV.Person = newPerson; // need to do this to set the person Id on the vehicle.
    newPerson.Vehicle.Add(firstV);
    // now when you save the Person it should save the Vehicle list 
    // if you set Cascade save update on the list. (not sure how to do that in L2S

    if (newPerson.Id <= 0)
        db.People.InsertOnSubmit(newPerson);
    db.SubmitChanges();
}

Now you may choose to construct the list of vehicles at another level , with the data that's coming from the interface.

But you need to remember that it's not enough to add the Vehicle to the list on the Person object , you also need to set the vehicles Person property to the person that has the vehicles.

Observation I'm not sure about this but when you do db.People.SingleOrDefault you might be loading the whole People table in memory . That's not something you want to do. Corrected by Slace in the comments.

Answer 2

All you need to do is ensure that there are the appropriate relationships set up within the database.

If your Vehicle table has a PersonId and there is a foreign key between them when you add them to the DBML Linq to SQL will detect that there is a relationship between them and create a Table<T> representation of the relationship.