none
SubmitChanges performs INSERTs before DELETEs RRS feed

  • Question

  •  

    Hello,

     

    suppose you habe a LINQ2SQL class for a SQL Server table that has a primary key on an IDENTITY column and a UNIQUE constraint on some other text column.

     

    In a DataContext, an existing row is marked for delete by calling DeleteOnSubmit and a new row is being added by calling InsertOnSubmit. The row that is to be deleted and the new row have the same value for the UNIQUE column.

     

    When I call .SubmitChanges now, I see that first the INSERT for the new row is attempted. This fails, because the server still has the row that I want to delete, so the entire SubmitChanges call fails. This is not a concurrency problem, so I cannot make SubmitChanges to just continue.

     

    How could I resolve such a problem?

     

    Thanks

    Karlo

    Wednesday, November 19, 2008 9:36 AM

Answers

  • LINQ to SQL does not track dependencies between rows via unique keys/columns so there is no way for it to get the ordering correct for you.  It does attempt to do this for primary keys.

     

    Monday, November 24, 2008 3:40 AM
    Moderator

All replies

  • I don't understand why and how you find yourself in such a situation

    you could store the rows you need to add somewhere (list)
    then first you delete the other rows, submit, then you add the new rows, submit


    it depends on how you want to do this, are you working in a bound datagridview?
    in that case you could:
    1. temporarily suspend the binding
    2. delete some records with a new query (not throu the bindingsource)
    3. add the new ones
    4. resume binding




    Friday, November 21, 2008 10:44 AM
  • In my particular situation the user decides the order of data manupulation and the point in time to save the data. There is no way for the program to dictate this. The user has the freedom to change the state of the data context the way he/she wants and eventually decides whether or not the changes shall be made persistent.

    Friday, November 21, 2008 6:25 PM
  • LINQ to SQL does not track dependencies between rows via unique keys/columns so there is no way for it to get the ordering correct for you.  It does attempt to do this for primary keys.

     

    Monday, November 24, 2008 3:40 AM
    Moderator
  •  

    Thanks!

     

    I assume there's no way to influence the order of execution of the SQL statements, right?

     

    I didn't work with entity data classes yet. These classes handle this case?

     

    Karlo

     

     

    Friday, November 28, 2008 3:35 PM
  • Greetings,

    I have a problem similar to Karlo's

    Here's my code

     NCISDataContext dbContext = new NCISDataContext(); 
     
            AMailListGroupMailListDeleteGroups delGroups = new AMailListGroupMailListDeleteGroups { MailId = instance.Id }; 
            dbContext.AMailListGroupMailListDeleteGroups.Attach(delGroups); 
            dbContext.AMailListGroupMailListDeleteGroups.DeleteOnSubmit(delGroups); 
     
            MailingList other = new MailingList { _Id = instance._Id, _Mobile = string.Empty }; 
     
            dbContext.MailingLists.Attach(other); 
     
            var groups = (LinkedList<int>)HttpContext.Current.Items["groups"]; 
     
            foreach(var groupId in groups) 
                other.MailListGroupMailLists.Add(new MailListGroupMailList { GroupId = groupId }); 
     
            other.Email = instance._Email; 
            other.Mobile = instance._Mobile; 
            other.Name = instance._Name; 
     
            dbContext.SubmitChanges(); 

    What I'm trying to do is to delete old mailing list associations then add new ones, but I get the DELETE statement executed after the INSERT

    Is there really no way to control the statements generation order?

    I'm not convinced with Valnuke's solution as going to the db twice performs less than doing everything in a single submit
    Moreover, this operation should be transactinal, I don't want to delete the old associations then fail to add the new ones
    Thursday, February 19, 2009 4:53 PM
  • Instead of having LINQ2SQL try to figure out the right order of commands to apply the changes in the DB, why not just use the same order in which changes were applied to the data context ?
    I think that could solve the problem (if linq to sql is able to capture this sequence).

    Friday, March 27, 2009 1:47 PM
  • At last I knew how to do it, but alas, so late.

    The solution is to make them all like Valnuke said, but inside a TransactionScope, so my code becomes:


    NCISDataContext dbContext =  new  NCISDataContext(); 
     
    AMailListGroupMailListDeleteGroups delGroups = new  AMailListGroupMailListDeleteGroups { MailId = instance.Id }; 
    dbContext.AMailListGroupMailListDeleteGroups.Attach(delGroups); 
    dbContext.AMailListGroupMailListDeleteGroups.DeleteOnSubmit(delGroups);
    var trans = new TransactionScope();

    try
    {
        dbContext.SubmitChanges();
    }
    catch
    {
        trans.Dispose();
        return;
    }
    MailingList other = new  MailingList { _Id = instance._Id, _Mobile =  string .Empty }; 
     
    dbContext.MailingLists.Attach(other); 
     
    var groups = (LinkedList<int >)HttpContext.Current.Items[ "groups" ]; 
     
    foreach (var groupId  in  groups) 
        other.MailListGroupMailLists.Add(new  MailListGroupMailList { GroupId = groupId }); 
     
    other.Email = instance._Email; 
    other.Mobile = instance._Mobile; 
    other.Name = instance._Name; 
     
    try
    {
        dbContext.SubmitChanges();
        trans.Complete();
    }
    catch
    {
    }

    trans.Dispose();



    I was guided by a paragraph in the Visual Studio documentation saying that DataContexts are compatible with TransactionScopes, while in the past, I thought that making multiple calls to SubmitChanges inside a TransactionScope will promote the transaction to a distributed one that needs the Distributed Transaction Coordinator service to be running
    • Proposed as answer by Ashraf Sabry Monday, March 30, 2009 5:09 AM
    Monday, March 30, 2009 5:09 AM