How to insert depending LINQ to SQL entities without manual ID update in C#?

Question

I have 2 models.

Model Foo

[System.Data.Linq.Mapping.Table(Name = ...)]
public class Foo
{
  [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true)]
  public int ID { get; set; }

  [System.Data.Linq.Mapping.Column(IsPrimaryKey = true)]
  public int BarID { get; set; }
  
  // 1:1 association - foo <> bar
  [System.Data.Linq.Mapping.Association(Name = "FK_Foo_Bar", Storage = "mBar", OtherKey = "ID", ThisKey = "BarID")]
  public Bar CurrentRevision
  {
    get { return mBar.Entity; }
    set { mBar.Entity = value; }
  }
  
  private System.Data.Linq.EntityRef<Bar> mBar = new System.Data.Linq.EntityRef<Bar>();
}

Model Bar

[System.Data.Linq.Mapping.Table(Name = ...)]
public class Bar
{
  [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true)]
  public int ID { get; set; }

  [System.Data.Linq.Mapping.Column(IsPrimaryKey = true)]
  public int FooID { get; set; }
  
  // 1:1 association - bar <> foo
  [System.Data.Linq.Mapping.Association(Name = "FK_Bar_Foo", Storage = "mFoo", OtherKey = "ID", ThisKey = "FooID")]
  public Foo Folder
  {
    get { return mFoo.Entity; }
    set { mFoo.Entity = value; }
  }
  
  private System.Data.Linq.EntityRef<Foo> mFoo = new System.Data.Linq.EntityRef<Foo>();
}

I want to insert a Foo and a Bar like so

lFoo = new Foo();
DataContext.Foos.InsertOnSubmit(lFoo);    

lBar = new Bar();
DataContext.Bars.InsertOnSubmit(lBar);    

lBar.Foo = lFoo; // link foo to bar
lFoo.Bar = lBar; // link bar to foo

DataContext.SubmitChanges();

A row for lFoo and a row for lBar is added to the DB but the FooID and BarID field remains zero.

Is there a way to let the framework update column FooID and BarID with the correct IDs automatically instead of doing this manually?

score 0 · Accepted Answer · edited May 23 '17 at 12:15

The following issues causes this as i found out by reading the LINQ to SQL documentation on MSDN:

multiple IsPrimaryKey in one class indicates a composite key, the DB does not have one
the IsForeignKey attribute is missing
linking the objects bidirectionaly directly after the InsertOnSubmits() calls won't work as it raises a A cycle was detected in the set of changes exception.

So the correct way would be

Model Foo

[System.Data.Linq.Mapping.Table(Name = ...)]
public class Foo
{
  [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true)]
  public int ID { get; set; }

  [System.Data.Linq.Mapping.Column]
  public int BarID { get; set; }

  // 1:1 association - foo <> bar
  [System.Data.Linq.Mapping.Association(Name = "FK_Foo_Bar", Storage = "mBar", OtherKey = "ID",
    ThisKey = "BarID", IsForeignKey=true)]
  public Bar CurrentRevision
  {
    get { return mBar.Entity; }
    set { mBar.Entity = value; }
  }

  private System.Data.Linq.EntityRef<Bar> mBar = new System.Data.Linq.EntityRef<Bar>();
}

Model Bar

[System.Data.Linq.Mapping.Table(Name = ...)]
public class Bar
{
  [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true)]
  public int ID { get; set; }

  [System.Data.Linq.Mapping.Column]
  public int FooID { get; set; }

  // 1:1 association - bar <> foo
  [System.Data.Linq.Mapping.Association(Name = "FK_Bar_Foo", Storage = "mFoo", OtherKey = "ID",
    ThisKey = "FooID", IsForeignKey=true)]
  public Foo Folder
  {
    get { return mFoo.Entity; }
    set { mFoo.Entity = value; }
  }

  private System.Data.Linq.EntityRef<Foo> mFoo = new System.Data.Linq.EntityRef<Foo>();
}

Inserts

lFoo = new Foo();
DataContext.Foos.InsertOnSubmit(lFoo);    

lBar = new Bar();
DataContext.Bars.InsertOnSubmit(lBar);    

DataContext.SubmitChanges();    

lBar = lFoo; // link foo to bar
lFoo = lBar; // link bar to foo

DataContext.SubmitChanges();

As a side node, sadly, using the IsForeignKey attribute value alters the query behavior (e.g. LINQ to SQL generates CROSS JOINS instead of INNER JOINS) and overall program logic in my case.

How to insert depending LINQ to SQL entities without manual ID update in C#?

1 Answers1