linq to sql Association problem with foreign keys

Question

I have two simple tables and a big problem with foreign keys:

Sale (SaleID int PK identity)
SaleDetail (SaleDetailId int PK identity, SaleID int FK)

which is a very basic one-to-many parent with children relationship. Of course there are some other fields but they're unrelated as they're not used in the relationship, so I did not include them in the listings.

I mapped the tables to classes in my code:

[Table()]
public class Sale
{
    [Column(IsPrimaryKey=true, IsDbGenerated=true, AutoSync=AutoSync.OnInsert)]
    public int SaleId
    {
        get;
        set;
    }

    private EntitySet<SaleDetail> saleDetails = new EntitySet<SaleDetail>();
    [Association(OtherKey = "SaleId", Storage = "saleDetails")]
    public EntitySet<SaleDetail> SaleDetails
    {
        get
        {
            return this.saleDetails;
        }
        set
        {
            this.saleDetails.Assign(value);
        }
    }

and the other one:

[Table()]
public class SaleDetail
{
    [Column(IsPrimaryKey=true, IsDbGenerated=true, UpdateCheck=UpdateCheck.Never)]
    public int SaleDetailId
    {
        get;
        set;
    }

    [Column()]
    public int SaleId
    {
        get;
        set;
    }

Then I try to create a new Sale and add a couple of child objects.

Sale s = new Sale();
s.SaleDetails.Add(new SaleDetail());
s.SaleDetails.Add(new SaleDetail());

and in the last step I try to add the new objects:

using (DataContext dc = new DataContext(Database.ConnectionString))
{
  var sales = dc.GetTable<Sale>();

  sales.InsertOnSubmit(s);
  dc.SubmitChanges();
}

the following procedure results in a foreign key problem - the child records are added with value 0 in the "SaleId" (FK) column. The parent record is added properly and the SaleId primary key gets its autogenerated value. What can I do to get the SaleId field set automatically in the child objects based on the value of the newly inserted parent?

Same problem here. And that's on WP7.8. I've tried committing the parent object (i.e. the Sale object in your case) first and then adding the child objects and doing another commit. No difference. The foreign key values for the child objects are still zero. — Albus Dumbledore

Emyr Emyr · Accepted Answer · 2011-07-20T11:03:42

Here, we tend to commit the parent first, although by nesting transactions you might be able to get the automatic primary key and still maintain the rollback-on-failure behaviour.

linq to sql Association problem with foreign keys

1 Answers