LinqToSql associations loading

Question

This may have been asked already, but I can't find it, so here goes.

We have a generic read of a table, using Context.GetTable( ) and then attaching an expression to find the single record in the table. This table has associations. Unfortunately, we're trying to change a field on which an association is made, so;

Foo is parent of Bar on FooId

When we're going to change the FooId, it's crashing because the HasAlreadyLoaded property of the EntityRef is set to true. Unless I'm not understanding LinqToSql well enough, I believe it's the act of calling SingleOrDefault on this recordset that is actually firing the query and thus loading all the associated objects up.

Is there a way to stop this loading occuring? (ObjectTrackingEnabled and DeferredLoading set to true) and if not what is the common solution for this problem? We don't know about individual relationships at this point, and I don't want to have to look at the possibility of going figuring out the associations and loading them up based on the id.

My preferred method would have been a less generic one, and I could have done .Foo = Context.Foo.Select( x => x.Id == FooId ), but we don't have that luxury now.

Answer 1

In LINQ to SQL, to update an assocation property, you must move the child object from one parent's EntitySet<> to another parent's EntitySet<>, and during the move LINQ to SQL automatically updates the child's association property.

For example:

        // Get the source parent
        Foo foo1 = context.Foos.Where(f => f.ID == 1).SingleOrDefault();
        Bar bar1 = foo1.Bars[0];

        // (Note: you don't necessarily need to get foo1 first - 
        // you could just get bar1 directly and proceed from here...)

        // Get the destination parent
        Foo foo2 = context.Foos.Where(f => f.ID == 2).SingleOrDefault();

        // Move the child over.  LINQ to SQL automatically updates bar1.FooID from 1 to 2 here.
        foo2.Bars.Add(bar1);

        // Done.
        context.SubmitChanges();