3

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).

Community
  • 1
  • 1
Skary
  • 1,322
  • 1
  • 13
  • 40
  • Are you talking about transaction? I post that you reffering to the accepted answer using `TransactionScope`. – teo van kot Jan 27 '16 at 10:38
  • 1
    Can you have a unique key, which is not auto generated? – Anil Jan 27 '16 at 10:41
  • @teovankot i would avoid transaction, because mine is a batch operation (huge sequence of update each of them send to me with xml message) so i wuold like to avoid to open and close a lot of transaction. – Skary Jan 27 '16 at 10:54
  • @AnilKumar: no i have no control over the table (i can not change their format) nor over update, insert or delete message i receive – Skary Jan 27 '16 at 10:54
  • Dear all : a thing that interest a lot to me is point out if the question that i linked is right and the delete operation is the last applied over SQL Server. If the answer is yes, so i would like to know why at the moment my program seem to work well – Skary Jan 27 '16 at 10:56

1 Answers1

1

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

Anil
  • 3,722
  • 2
  • 24
  • 49
  • i apply regenerative logic, so in the table in my example i can send another message that contains for the ID 1 the SubID 1_0 and the SubID 1_1. My logic say, delete all entry with id 1 and then insert (1 , 1_0) and insert ( 1 , 1_1). In that case i expect a primary key violation (because it will first try to insert and then delete opposite of what i am expecting), but i have not recorded that error in my system. And more importat after the message i have two record on the table. If delete will be the last operation i expect 0 records – Skary Jan 27 '16 at 11:13
  • You are deleting a record and then inserting a new record with the same key? What's wrong with just updating it. – Anil Jan 27 '16 at 11:27
  • No, i have a message that told me only ( entry 1 sub entry 1_0 and sub entry 1_1). To use update logic i need to check for each entry in the message if exist (update) or if need to be inserted. After that i need to check the table if there are more entry than in the message and then delete them. It's complex so i have adopted a regenerative approach. – Skary Jan 27 '16 at 11:35
  • thanks i have understand better the life cycle of DataContext. But is not clear to me why my logic seems to work. I mean, now is clear to me that my approach is wrong, but change is a big effort now (i am in production since december), i would like to understand if i can consider reliable enought even if not optimal or if someday can stop working – Skary Jan 27 '16 at 13:35
  • @Skary, can you track the sql statements and sequences in SQL Profiler, if you have access. I belive there is something more, how your delete statement is being formed. refer http://weblogs.asp.net/zeeshanhirani/how-to-use-profiler-with-linq-to-sql-queries and http://stackoverflow.com/questions/729774/sql-server-profiler-not-showing-linq-to-sql-queries – Anil Jan 27 '16 at 13:41