Linq to sql object is marking it to insert automatically

Question

I'm making a clone of some entities to bound them to some grid and avoid the objects to be tracked for changes, something like:

var cloneEntities = DataContext.SomeTable.FirstOrDefault().
    SomeChildTable.Select (
         x => new SomeChildTable { SomeProperty = x.SomeProperty } 
); 

Some of the properties are not primitive types but DataContext objects (Other entities). After doing that the objects on cloneEntities are marked as if i did SomeChildTable.InsertAllOnSubmit(cloneEntities), which i didn't.

If i don't initialize any entity property the objects are not marked to be inserted.

How do i get a clone of the child entities without them being marked to be inserted?.

Thank you

UPATE: It seems that I'm not beign clear enough, so, let make an example.

We have a Employee and a Store tables, some employees can work on many stores, so you have a EmployeeStore table to keep track of the stores of which the employee works:

Employee
=======
Code int not null identity (1,1)
Name varchar(20)
Primary on Code

Store
=====
Code int not null identity (1,1)
Name varchar(20)
Primary on Code

EmployeeStore
=============
CodeEmployee int
CodeStore int
Primary on CodeEmployee and CodeStore

If I'm editing the employee i want to present the stores on a grid and they must be able to add new stores.

At first it works, but if i try to update some row on the grid, (The user instead of delete store A and add Store B changes the grid row with Store A and selects the Store B) i got an exception because I'm trying to modify the EmployeeStore.StoreCode which is part of the primary key.

So instead i want to do something like:

var employee = DataContext.Employee.First();
var clonedStores = new BindingList<EmployeeStore>(employee.Stores.Select (
    x => new EmployeeStore {Store = x.Store}));
//Bind to stores to grid and later on do
DataContext.EmployeeStore.DeleteAllOnSubmit(employee.Stores);
employee.Stores.AddRange(clonedStores);
DataContext.SubmitChanges();

But that doesn't work, because, the moment i create clonedStores it gets marked as if i did a InsertAllOnSubmit(), so it crash if i try to delete the Employee object.

This seems like I'm doing something terrible wrong but i cannot figure out what, because this is such a common scenario.

Thank you for your help and thoughts.

Answer 1

Have you tried detaching the object http://msdn.microsoft.com/en-us/library/bb738611.aspx? Can't say whether you have or not from your code snippet.

Answer 2

Your problem is that you are adding the child entities back to the same entity. Change the statement to:

var cloneEntities = DataContext.SomeTable.FirstOrDefault().SomeChildTable.Select(x => new { SomeProperty = x.SomeProperty }); 

UPDATE:

Based on the new information, you're going to have problems if you only change the primary key value. I would suggest something like this:

var entitiesToMove = DataContext.SomeTable.FirstOrDefault().SomeChildTable;

foreach (SomeChildTable item in entitiesToMove)
    DataContext.SomeTable.Where(a => a.SomeTable_ID == newID).SomeChildTable.Add(item);

DataContext.SubmitChanges();

This way, you don't have to worry about deleting anything...you're just updating the foreign key reference for the child records.

UPDATE #2:

The first thing you should do is change the table structure of EmployeeStore to:

EmployeeStore_ID  int not null identity (1, 1)
CodeEmployee      int     (FK to Employee)
CodeStore         int     (FK to Store)

LINQ-to-SQL works best when you've got your foreign key relationships set up correctly. It also doesn't like composite primary keys very much, which is why you were getting the exception when you tried to change the CodeStore value.

Once that's done, you should be able to reassign an employee to a different store by simply changing the EmployeeStore.CodeStore field to an existing Store.Code and calling DataContext.SubmitChanges().