CODEWITHSUNDEEP
c#sql-serverlinq
Skary
Skary

Reputation: 1362

Linq To SQL Delete + Insert best practice

As stated in the title i need to perform delete + insert, i do :

 context.DeleteAllOnSubmit ( deleteQuery ) ;

 foreach ( var entry in entries ) 
      contex.InsertOnSubmit ( entry ) ;

 context.SubmitChanges();

As wrote in that post : Linq to SQL: execution order when calling SubmitChanges()

I read that the delete operation is the last one applied, but at the moment i see my logic work (i am sure that delete+insert happen dozen of times per day). What i need is understand if the post is wrong or my logic is and for some reason (update check flag in linq to sql datamodel?) only lucky and avoid the trouble.

After that i would like to know what is the better pattern to make "update" when record cardinality changes. I mean in my table there is a primary key that identify an entity (an entity has many records) and a subkey that identify each record in the same entity (sub entity). I need to regenerate (because some sub entity may be inserted, edited or delete) so i use delete + insert (in the messagge form which i write to DB contains only entity and sub enetity that exist, not the deleted ones).

EG: 

 ID     SubID    Data
 1      1_0      Father      
 2      2_0      Father
 2      2_1      Child 1
 3      3_0      Father
 3      3_1      Child 1
 3      3_2      Child 2

I have no control nor over the table (and data format inside them) nor over the message (that i use to write or delete the table displaied above).

Upvotes: 3

Views: 2280

Answers (1)

Anil
Anil

Reputation: 3752

I read that the delete operation is the last one applied, but at the moment i see my logic work (i am sure that delete+insert happen dozen of times per day). What i need is understand if the post is wrong or my logic is and for some reason (update check flag in linq to sql datamodel?) only lucky and avoid the trouble.

Post is correct, delete actually deleted at last.

Your code is working as per design, this is not by chance.

It actually loads all records to be deleted and then deleted all one by one. This happens at last.

This will never fail or will not deleted wrong records, however it has performance issue, you can refer very good msdn article on this

Regardless of how many changes you make to your objects, changes are made only to in-memory replicas. You have made no changes to the actual data in the database. Your changes are not transmitted to the server until you explicitly call SubmitChanges on the DataContext.

When you make this call, the DataContext tries to translate your changes into equivalent SQL commands. You can use your own custom logic to override these actions, but the order of submission is orchestrated by a service of the DataContext known as the change processor.

The sequence of events is as follows: refer msdn

  1. When you call SubmitChanges, LINQ to SQL examines the set of known objects to determine whether new instances have been attached to them. If they have, these new instances are added to the set of tracked objects. This is why we are saying insertion at first

  2. All objects that have pending changes are ordered into a sequence of objects based on the dependencies between them. Objects whose changes depend on other objects are sequenced after their dependencies. then the update

After Update deletion is done

Immediately before any actual changes are transmitted, LINQ to SQL starts a transaction to encapsulate the series of individual commands.

The changes to the objects are translated one by one to SQL commands and sent to the server.

At this point, any errors detected by the database cause the submission process to stop, and an exception is raised.

All changes to the database are rolled back as if no submissions ever occurred. The DataContext still has a full recording of all changes

Upvotes: 1

Related Questions