Linq and DeleteAllOnSubmit pain

Question

The following code loads a gig, clears out the gigs acts collection and then adds a new act.

            Data.LinqToSQL.Gig dbGig = DBContext.Gigs.Where(x => x.ID == myGigID).SingleOrDefault();

            //Remove all references to the current acts
            if(dbGig.Acts!=null) {
                DBContext.Acts.DeleteAllOnSubmit(dbGig.Acts);
            }

            Data.LinqToSQL.Act dbAct =  new ListenTo.Data.LinqToSQL.Act();
            dbAct.ID = Guid.NewGuid();
            DBContext.Acts.InsertOnSubmit(dbAct);

            DBContext.SubmitChanges();

Please note that everytime I run this code, the value of myGigID is the same, so its always the same gig that I load.

The first time I run this code it works fine and I have a gig with 1 act.

The second time, dbGig.Acts (a collection) has a count of 0 and so the DeleteAllOnSubmit does not remove any acts. However in the database there is 1 act for this gig! Hence I end up with 2 acts once this code has run.

If I run it a 3rd time, I end up with 3 acts in total.

Any ideas what I'm doing wrong?

Answer 1

Well, you never actually associate the new act with any gig - is that what is missing?

dbGig.Acts.Add(dbAct);

or maybe:

dbAct.Gig = dbGig;

or something with the ids.

i.e. are you sure the act in the db is for the gig?

Answer 2

In my code base it does the association -

dbAct.GigID = myGigID;

I just missed it out above.

Could it be some sort of caching issue?

Answer 3

Try opening a fresh DBContext at the beginning of this block. If you're using the same context for the first and second run, it won't see the inserted record the second time around. Resetting the context should force it to see the latest rows in the table.

Answer 4

An alternative to gfrizzle's answer is unless you have a compelling reason to do so don't call SubmitChanges until after you have done all the DB work.