none
entity framework use old data and new data to check the unique constraints? RRS feed

  • Question

  • I am using entity framework 4.0 and I have a table. In this table I have a unique constraint in one of the fields.

    When I add a new register, I do the following:

    1.- in the existing records, I recalculate the value of the unique field. This value depends on the new register that I want to add and change the value in all the registers.

    2.- I calculate the unique field of the new record.

    3.- I add the new register with the unique value.

    In the moment in which I do the save changes, I check the values of the register I can see that in the unique field there are no duplicate values.

    However, when I save the changes, I get an exception of unique value. This is because I change the value of one of the existing registers, change it and assign this value to the new record.

    I thing that entity framework first add the new record and how other record still has the old value, I get the exception. wouldn't EF first update the existing records and then add the new record to ensure that compare the correct information?

    Really what I guess is the normal bahavior of EF? If this true, how can I solve this problem?

    Thanks.

    Monday, April 15, 2013 5:22 PM

Answers

  • Hi ComptonAlvaro,

    To understand the real problem, would you like to post some code snippet to reproduce the issue?

    For example, I created a table in database contains Id as primary key and Name as unique key. I can use the following code to add new record. Is it the same scenario?

    using (var context = new Ef_use_old_dataEntities())
    {
        var item = context.Table_1.Find(5);
        string name = item.Name;
        item.Name = "test";
        var newitem = new Table_1 { Id = 6, Name = name };
        context.Table_1.Add(newitem);
        context.SaveChanges();
    }
    

    The executed sql command traced by SQL Server Profiler are:

    exec sp_executesql N'update [dbo].[Table_1]
    set [Name] = @0
    where ([Id] = @1)
    ',N'@0 nchar(10),@1 int',@0=N'test      ',@1=5

    and

    exec sp_executesql N'insert [dbo].[Table_1]([Id], [Name])
    values (@0, @1)
    ',N'@0 int,@1 nchar(10)',@0=6,@1=N'name5     '
    Best regards,

    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by ComptonAlvaro Tuesday, September 16, 2014 10:48 AM
    Wednesday, April 17, 2013 7:56 AM
    Moderator

All replies

  • Hi ComptonAlvaro,

    To understand the real problem, would you like to post some code snippet to reproduce the issue?

    For example, I created a table in database contains Id as primary key and Name as unique key. I can use the following code to add new record. Is it the same scenario?

    using (var context = new Ef_use_old_dataEntities())
    {
        var item = context.Table_1.Find(5);
        string name = item.Name;
        item.Name = "test";
        var newitem = new Table_1 { Id = 6, Name = name };
        context.Table_1.Add(newitem);
        context.SaveChanges();
    }
    

    The executed sql command traced by SQL Server Profiler are:

    exec sp_executesql N'update [dbo].[Table_1]
    set [Name] = @0
    where ([Id] = @1)
    ',N'@0 nchar(10),@1 int',@0=N'test      ',@1=5

    and

    exec sp_executesql N'insert [dbo].[Table_1]([Id], [Name])
    values (@0, @1)
    ',N'@0 int,@1 nchar(10)',@0=6,@1=N'name5     '
    Best regards,

    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by ComptonAlvaro Tuesday, September 16, 2014 10:48 AM
    Wednesday, April 17, 2013 7:56 AM
    Moderator
  • Well, I have the following table in my data base:

    Trees(IDNode, IDRootNode, IDParentNode, Left, Right, ...)

    IDNode is an autonumber.

    This Table has the following unique constraints:

    Left_Constraint: IDRootNode, Left

    Right_Constraint: IDRootNode, Right

    Imagine that I have three nodes, the root node and two childs, the information that have the nodes are:

    Root Node: IDNode = 1; IDRootNode = 1; IDParentNode = 1; Left = 1, Right = 6;

    Child Node: IDNode = 2; IDRootNode = 1; IDParentNode = 1; Left = 2; Right = 3;

    Child Node: IDNode = 3; IDRootNode = 1; IDParentNode = 1; Left = 4; Right = 5;

                                                                  Node = 1

                                                                       |

                                                                       |

                                                     __________|________

                                                     |                                |

                                               Node = 2                     Node = 3

    Now, I want to add a new node. This node it is a child of the Node = 2. I following the next steps:

    1.- I do a query to the database to get all the tree. I use a raw sql query that is the following: select * from Trees where IDRootNode = 1;

    2.- Change the values of left and right in the existing nodes. the ne values are:

    IDNode = 1; left = 1; right = 8;

    IDNode = 2; left = 2; right 5;

    IDNode = 3; left = 6; right = 7;

    3.- I add the new node, I use this data:

    IDNode = 4; IDRootNode = 1; IDParentNode = 2; Left = 3; Right = 4; How you can see, I have not used the same left or right values on two nodes.

    4.- I save the changes.

    I get the exception of unique constraint in the right_Constraint (1, 5). This is because the right value of the node 3 was "5", but I have updated this value to the new one, "7", and use the value "5" in the field of the new node. however, entity framework uses the old value of the node 3 to check if is used the same value in two nodes.

    Thanks.


    Wednesday, April 17, 2013 8:21 AM