Changing the order of LINQ to SQL Inserts

Question

I have two LINQ to SQL classes, CandyBar and DeliciousCandyBar that map to tables of the same name in SQL Server.

There is a 0..1 relationship between CandyBar and DeliciousCandyBar. i.e, A CandyBar can have 0 or 1 DeliciousCandyBars. Conversely a DeliciousCandyBar has exactly one CandyBar.

In LINQ to SQL class, they look (basically) like

class CandyBar {
  public int Id { get;set;} // this is primary key w/ autoincrement identity
  public string Name {get;set;}
  public EntityRef<DeliciousCandyBar> DeliciousCandyBar {get;set;}
}

class DeliciousCandyBar {
  public int DeliciousnessFactor {get;set;}
  public int CandyBarId {get;set;} // FK to candyBar Id
  public EntityRef<CandyBar> CandyBar {get;set;} // the association property of the FK
}

To feed the database (via l2sql), my crawler goes out and finds candy bars and delicious candy bar.

But, with the first delicious candy bar my crawler inserts into the CandyStoreDataContext, the DataContext throws an exception when SubmitChanges is called.

The crawler runs the following code for one delicious candy bar. Please note this is an example. The exact process is more complex and I use a custom DSL crawler that spits out this object structure. Essentially, the following is performed.

var dc = CandyStoreDataContext();
var bar = new CandyBar() {
    Name = "Flake",
    DeliciousCandyBar = new DeliciousCandyBar() {
      DeliciousnessFactory = 12
    }
};

dc.CandyBars.InsertOnSubmit(bar);

dc.SubmitChanges();

On SubmitChanges(), a SqlException is thrown with the message "The INSERT statement conflicted with the FOREIGN KEY constraint FK_CandyBar_DeliciousCandyBar. The conflict occured in database CandyStoreData, table 'dbo.DeliciousCandyBar', column 'CandyBarId'".

The problem became clear when I dumped the CandyStoreDataContext.Log to Console.Out, the generated insert statements were round the wrong way. LINQ to SQL was trying to insert the DeliciousCandyBar first (which tried to set an invalid value in CandyBarId column), rather than inserting CandyBar first.

My question is, how do I get Linq to SQL to swap with the order of the insert statements?

I had assumed (incorrectly) that LINQ to SQL would know the direction of relationship dependency and do it the other way around.

UPDATE:

This post suggests I have the Association the wrong way around. But that doesn't make sense to me. It makes sense when modelled in the database. How could this be done the other way around.

On CandyBar, the association attribute on DelciousCandyBar property is [Association(Name="DeliciousCandyBar_CandyBar", Storage="_DeliciousCandyBar", ThisKey="Id", OtherKey="CandyBarId", IsForeignKey=true)]

On DeliciousCandyBar, the association attribute on CandyBar property is [Association(Name="DeliciousCandyBar_CandyBar", Storage="_CandyBar", ThisKey="CandyBarId", OtherKey="Id", IsUnique=true, IsForeignKey=false)]

Okay, now I'm confused, why is the second attribute marked as the foreign key.

I'm going to try recreating that relationship between CandyBar and DeliciousCandyBar in SQL Management studio

UPDATE 2

Okay, I tried creating the relationship both ways. And SSMS makes it really clear where the primary key is located (CandyBar.Id). I had it right the first time. Otherwise, the cascading would go backwards.

Answer 1

Please try this alternative:

1. Insert the instance of CandyBar without any child DeliciousCandyBar objects, then call the SubmitChanges() method.

2. Assuming you have an ID or GUID linking the two objects - lets say, CandyBarID. Then set the value of CandyBarID on the DeliciousCandyBar instance to that of the instance of CandyBar.

3. You could then insert the DeliciousCandyBar object, and because CandyBarID has been set, the relationship should be correct on selecting CandyBar objects.

Answer 2

I would consider the possibility of it being a linq2sql bug with self referencing tables. Its a wild guess, but I recall linq2sql documentation saying somewhere it didn't support it that well. Perhaps there its the linq2sql designer that gets confused.

From the generated properties you posted, you can tell it has it backwards i.e. CandyBar has is pointing to delicious, instead of the other way around. Look at the generated code for other relations that work correctly.

Once you confirm it isn't working, and that by re-adding them in the designer doesn't sets them correctly, open the properties of a working association in the designer and make sure to have the association between candybar and deliciouscandy configured in the same way.