change foreign key for a linq entity

Question

I have a CRUD repository as fallowing:

public class CrudRepository<T> : ICrudRepository<T>
        where T : class, IUnique
    {
        static DataContext db = new DataContext();

        protected DataContext DataContext { get { return db; } }

        public virtual IQueryable<T> GetAll()
        {
            return db.GetTable<T>();
        }

        public virtual void Add(T item)
        {
            db.GetTable<T>().InsertOnSubmit(item);
        }

        public virtual void Save()
        {
            db.SubmitChanges();
        }

        public virtual T Get(int id)
        {
            return GetAll().FirstOrDefault(t => t.Id.Equals(id));
        }
    }

I use static data context for all instance off repository. I want to change foreign key entity so i try fallowing solution:

CrudRepository<Employee> employeeRepository = new CrudRepository<Employee >();
Employee employee = employeeRepository.Get(employeeId)
employee.OfficeId = officeId;
employeeRepository.Save();

But it throw fallowing exception :

ForeignKeyReferenceAlreadyHasValueException

So i try fallowing second solution:

CrudRepository<Employee> employeeRepository = new CrudRepository<Employee >();
Employee employee = employeeRepository.Get(employeeId)
employee.Office = new CrudRepository<Office>().Get(officeId);
employeeRepository.Save();

But it throw exception with fallowing message:

An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext

what can i do? what is the problem?

Answer 1

Three things jump out at me.

employee.OfficeId = officeId; 

If the Employee class has an OfficeId property and an Office property, you must use the Office property to make changes. The Office property is auto-generated from the relationship in the linq designer.

If you want to use Id based manipulations instead, delete the relationship between employee and office in the designer (note: this does not change the database, it just changes the mappings used by the code generator).

new CrudRepository<Employee >();  
new CrudRepository<Office>().Get(officeId); 

Each Crud Repository has its own datacontext. Objects loaded from different datacontexts are not allowed to co-mingle. Suppose they were allowed to co-mingle - when you call SubmitChanges, which DataContext is responsible for saving?

Ultimately, this means your CrudRepository implementation is going to be something you want to move away from if you want to continue using LinqToSql. Supporting Crud operations on a single class just isn't that useful. At least it's only passthrough calls, and will be easy to replace with direct DataContext method calls.

static DataContext db = new DataContext(); 

This is damnable. DataContext is not threadsafe.