In Linq to SQL, why does assigning a related entity create a ChangeSet insert ?

Question

Why is the following adding an Insert (of the new Address) to the DataContent ChangeSet, and how can I stop it from doing so?

var db = new DataClasses1DataContext();
var a = new Address();
a.StateProvince = db.StateProvinces.First();
Console.WriteLine(db.GetChangeSet().Inserts.Count);

Answer 1

It is doing it because you are retreiving a "StateProvince" and setting the foreign key on the new address to the existing state province, becuase the address is new then the change set will build an insert for it becuase i suspect you have changed the state province by impicitly adding the new address to the Addresses collection on the StateProvince entity. You could try removing the child property from the assoication as it is probably not usefull to have a collections of associated addresses on a state province entity.

You could also try the following:

DataClasses1DataContext db = new DataClasses1DataContext();
db.ObjectTrackingEnabled = false;
Address a = new Address();
a.StateProvince = db.StateProvinces.First();

You may also notice i have removed your blatant abuse of var ;-)

Answer 2

Hey,

Because the data context knows about the state province, it automatically queues up Address for an insertion. As long as you don't submit it, the address will never be created. Also, to work around it, set:

Address.StateProvinceID = db.StateProvinces.First().StateProvinceID;

If you can work without a direct object reference. There is no direct way to detach an entity in LINQ to SQL, except through PLINQO (http://plinqo.com/default.aspx?AspxAutoDetectCookieSupport=1).

Answer 3

Address a = new Address();

This creates a new instance of Address. It is not associated with a datacontext in any way.

db.StateProvinces.First();

This loads an instance of StateProvince. The instance is tracked by the datacontext that loaded it (db). db is waiting for notifications of changes from this instance.

a.StateProvince =

Here is assignment to a property. If you inspect the autogenerated property "StateProvince", you'll see that it also updates the related property "Addresses" on the StateProvince instance. Now the StateProvince instance has changed. This causes db to be notified, and as a result - the address instance is now tracked by db as a new, ready-to-insert instance.

The simplest way out of this is, before using db, set:

db.ObjectTrackingEnabled = false;

PS. var is awesome like hotdogs are awesome.