issue with submitChanges() inserting unwanted records in linq

Question

I am using LINQ to insert records in the database. I create these records and keep track of them using a List. Based on some logic, I delete some of the records by deleting from the List. (I am using the same DataContext object).

When I want to insert the records in the database, I do the corresponding linq table's InsertOnSubmit() followed by SubmitChanges() on datacontext object. LINQ inserts the deleted-from-List records too along with the ones that are present in the list.

example:

//list to keep track of records to insert
List list

// add the records to list
list.add(some records)

//deleted last 2 records
list.remove()

//call InsertAllOnSubmit on the linq table passing the list object with records to insert
linqTable.InsertAllOnSubmit(list)

//call SubmitChanges on datacontext object
datacontext.SubmitChanges()

I came across this msdn article Object States and Change-Tracking (LINQ to SQL)

You can explicitly request Inserts by using InsertOnSubmit. Alternatively, LINQ to SQL can infer Inserts by finding objects connected to one of the known objects that must be updated. For example, if you add an Untracked object to an EntitySet(TEntity) or set an EntityRef(TEntity) to an Untracked object, you make the Untracked object reachable by way of tracked objects in the graph. While processing SubmitChanges, LINQ to SQL traverses the tracked objects and discovers any reachable persistent objects that are not tracked. Such objects are candidates for insertion into the database.

I guess the question boils down to this - how do I change the deleted objects' state to 'Untracked'?

I tried DeleteOnSubmit after I delete the objects from list but that gives an exception (Cannot remove an entity that has not been attached).

Can someone please point me to a solution? Thanks.

I would like to know if I can achieve this using LINQ only. (I know that I can use an stored proc and insert only the records in the list.)

Answer 1

I think that the elements of your list are not being removed, because the List.Remove method determines equality using the default equality comparer.

If you don't want to write a custom comparer, I recommend you to use the RemoveAll method, which receives a predicate as its first parameter to match the elements that will be removed from the list:

list.RemoveAll( e=> /*condition to remove the element*/ );

Or the RemoveAt method, that removes the element based on the specified index:

list.RemoveAt(0); // Delete first element