Insert new child+parent with LINQ2SQL

Question

struggling with LINQ2SQL, I'm new to it, and so far it's been ok, but this problem is really causing me grief.

I've got two objects, Parent and Child, defined as such

[Table(Name="Parent")]
public class Parent
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public int ParentID { get; set; }

    [Association(OtherKey = "ParentID")]
    protected EntitySet<Child> _children = new EntitySet<Child>();
    public IList<Child> Children
    { 
        get { return _children.ToList(); }
        set { _children (value); }
    }
}

[Table(Name="Child")]
public class Child
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    public int ChildID { get; set; }
    [Association(OtherKey = "ParentID", IsForeignKey=true)]
    [Column] public int ParentID { get; set; }

}

Basics of what I'm then doing are

Parent newParent = new Parent();
Child newChild = new Child();
newParent.Children.Add(newChild);
parentTable.InsertOnSubmit(newParent);
parentTable.Context.SubmitChanges();

If I remove the relationship between the two tables in the DB and save, they save ok. Except that the ParentID in the Child record always stays as 0.

If I create the relationship in the DB and try to save, it fails, obviously because the Child have a ParentID of 0 would break referential integrity.

Even if I modify the above to be;

Parent newParent = new Parent();
Child newChild = new Child();
newChild.ParentID = newParent.ParentID;
newParent.Children.Add(newChild);
parentTable.InsertOnSubmit(newParent);
parentTable.Context.SubmitChanges();

Why is this not working? What am I missing in Linq2SQL?

Any help much appreciated!

Answer 1

Please add a new item: LINQ to SQL classes mapped to relational objects. with prefix '.dbml' to your project and then add tables to the designer interface, thus the VS will automatically create the entities mapping and related relations for you,
P.S.: Don't forget to set a primary key to each table otherwise none will work perfectly