none
How to control the sequence of updating tables RRS feed

  • Question

  • Hi, I have a CHECK constraints in the database that uses a stored procedures to verify conditions between parent and child tables - for example "If a child row exists the child table then the parent row's childless flag must be false". This is just an example and the real constraint is more complicated.

    At the front end, I insert the first child row and set the parent row's childless flag to false in the DataContext. However, when I call SubmitChanges, the DB CHECK constraint will fail, because LINQ tries to insert the child rows first and set the parent row's childless flag to false after that. 

    When I delete the last child row and set the parent row's childless flag to true, what LINQ does again causes the CHECK constraint to fail, because it set the parent row's childless flag to true first, while the child row is not yet deleted.

    How do I control the sequence LINQ send different types of changes to different tables?


    ... But wait! There are even dumber problems! I simply added into DataContext a parent row and a few child rows, and LINQ caused a foreign key violation exception because it tried to insert into DB the child rows first! Save me! What can LINQ do???
    Wednesday, May 4, 2011 1:01 AM

Answers

  • I just figured out the answer to one of my questions - after I insert into DataContext a parent row and a few child rows, why would LINQ try to insert the child row first to cause a foreigh key violation:

    Let's say I have two tables:

     

     

    create table parent
    (
     parent_id int,
     parent_name varchar(50)
    );
    
    create table child
    (
     child_id int,
     child_name varchar(50),
     parent_id int
    );
    
    alter table child add constraint child_FK_parnet foreign key (parent_id) references parent (parent_id) ;
    

     

    The way I added the parent row and child rows is as follow:

     

    MyDataContext ctx = new MyDataContext(strConnStr);
    
    parent p = new parent();
    p.parent_id = 100;
    p.parent_name = "dad";
    ctx.parents.InsertOnSubmit();
    
    child c = new child();
    c.child_id = 200;
    c.child_name = "son";
    c.parent_id = 100;
    ctx.childs.InsertOnSubmit();
    
    ctx.SubmitChanges();
    

     

     

    I was assuming that, with the prior knowledge about the parent-child relationship between the two tables, and by saying "c.parent_id = 100", it would be obvious even to the blind that these two records are parent-child records. But LINQ seems to be really dumb. It can't figure it out. So I had to add one extra line to say "c.parent = p". After adding this line, LINQ would do the submit correctly:

     

     

    MyDataContext ctx = new MyDataContext(strConnStr);
    
    parent p = new parent();
    p.parent_id = 100;
    p.parent_name = "dad";
    ctx.parents.InsertOnSubmit();
    
    child c = new child();
    c.child_id = 200;
    c.child_name = "son";
    c.parent_id = 100;
    c.parent = p;
    ctx.childs.InsertOnSubmit();
    
    ctx.SubmitChanges();

     

     

     


    • Marked as answer by Silan Liu Wednesday, May 4, 2011 11:57 PM
    Wednesday, May 4, 2011 11:56 PM

All replies

  • I just figured out the answer to one of my questions - after I insert into DataContext a parent row and a few child rows, why would LINQ try to insert the child row first to cause a foreigh key violation:

    Let's say I have two tables:

     

     

    create table parent
    (
     parent_id int,
     parent_name varchar(50)
    );
    
    create table child
    (
     child_id int,
     child_name varchar(50),
     parent_id int
    );
    
    alter table child add constraint child_FK_parnet foreign key (parent_id) references parent (parent_id) ;
    

     

    The way I added the parent row and child rows is as follow:

     

    MyDataContext ctx = new MyDataContext(strConnStr);
    
    parent p = new parent();
    p.parent_id = 100;
    p.parent_name = "dad";
    ctx.parents.InsertOnSubmit();
    
    child c = new child();
    c.child_id = 200;
    c.child_name = "son";
    c.parent_id = 100;
    ctx.childs.InsertOnSubmit();
    
    ctx.SubmitChanges();
    

     

     

    I was assuming that, with the prior knowledge about the parent-child relationship between the two tables, and by saying "c.parent_id = 100", it would be obvious even to the blind that these two records are parent-child records. But LINQ seems to be really dumb. It can't figure it out. So I had to add one extra line to say "c.parent = p". After adding this line, LINQ would do the submit correctly:

     

     

    MyDataContext ctx = new MyDataContext(strConnStr);
    
    parent p = new parent();
    p.parent_id = 100;
    p.parent_name = "dad";
    ctx.parents.InsertOnSubmit();
    
    child c = new child();
    c.child_id = 200;
    c.child_name = "son";
    c.parent_id = 100;
    c.parent = p;
    ctx.childs.InsertOnSubmit();
    
    ctx.SubmitChanges();

     

     

     


    • Marked as answer by Silan Liu Wednesday, May 4, 2011 11:57 PM
    Wednesday, May 4, 2011 11:56 PM
  • Good Finding
    Thursday, May 5, 2011 12:31 AM