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!

please show us the code where you create child and its parent — Amir Ismail, Aug 08 '11 at 10:06
It looks like you hand coded the entities, is that correct? When you let LINQ to SQL generate the code for you, it generates all sorts of notification stuff that makes sure the relationships keep in sync, which doesn't happen with your code. If you insist in handwriting it, please take a look at the code L2S would generate, to see what you're missing. — Steven, Aug 08 '11 at 10:20
I agree with Steven, there seems to be something wrong with your models. Your second case, the one where you include the relation in the models, should not "obviously" fail, in fact that is the correct way of inserting objects with this type of hierarchy in Linq-to-SQL — AHM, Aug 08 '11 at 11:20
I've tried the autogen code as Steven suggested. I noticed that it flips things round a bit. It adds a Parent property (of type Parent) to the Child class. And an attach_Child method a method in the Parent class, with actions on the Children EntitySet collection, so that when a Child is added to the Children collection, it also passes a reference from the Parent to the Child's Parent property. Then inside the Child Parent property setter if takes the ParentID from the received Parent and assigns it to the Child ParentID property. — Chris Butcher, Aug 08 '11 at 11:54
I think I might need to ditch my hand-coded classes and use the auto-gen'd ones instead. What do you reckon? — Chris Butcher, Aug 08 '11 at 11:57
I think that is more or less the only usable option, if you want to use Linq-to-SQL. If you are prepared to use the Entityframework instead, then EF4 supports using plain old POCO models, which I belive is what you are really trying to here. See: http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx — AHM, Aug 08 '11 at 12:24
Thanks guys! I've been banging my head against this for days. You've helped me sort things out.! Much respect to you all! — Chris Butcher, Aug 08 '11 at 14:47

score 1 · Accepted Answer · answered Aug 08 '11 at 11:24

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

answered Aug 08 '11 at 11:24

Rami Alshareef

7,015
12
47
75

Rami, I went with the O/R designer route rather than hand-coded classes. I was then able to extend the partial classes for the requirements which the O/R couldn't account for. – Chris Butcher Aug 08 '11 at 14:49

Insert new child+parent with LINQ2SQL

1 Answers1

Linked