linq to sql Association problem with foreign keys

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?

---

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.