none
Detecting Violation of PRIMARY KEY constraint in Entity Framework RRS feed

  • Question

  • Background:
    I am using Entity Framework 4.0.

    I have a User object and multiple Product objects (A user can have multiple products).

    I am writing a routine to add products to a user.

    var product = context.Products.Single(T => T.ItemGuid == ProductGuid);
    var user= context.Users.Single(T => T.ItemGuid == UserGuid);
                          
    user.Products.Add(product);

    If a product gets added twice I get a 'Violation of PRIMARY KEY constraint' error. Which I expect.

    Question:

    I want to be able to determine if my state is valid BEFORE I call 'SaveChanges' on the context via a 'Validate' routine on the 'User' object or the 'Product' object.  I have a fairly large validation library to check for valid business conditions.  These validation routines are invoked on demand.

    What I am trying to avoid:

    I do not want to have to check to see if user.Products.Contains(product) before I call user.Products.Add(product).  This would spread my validation logic out.


    Is there a way to do this?

    Friday, August 3, 2012 2:59 PM

Answers

  • Hi Andrew,

    I agree that this looks like a bug in EF. I have created an issue for it here: http://entityframework.codeplex.com/workitem/441

    While that is investigated further I think that doing a contains before you do the add is the best workaround.

    It is possible that some other solution could be developed that would stop the exception from being thrown but otherwise the behaviour would stay the same. If we pursued this then we would write code that would detect that a duplicate has been adde and ignore it when doing SaveChanges, so EF would not attempt to save the duplicate. It would be more work, and not necesarilly any better, but we can look at it if you think it is worthwhile. 

    Out of interest, do you have .NET 4.5 installed on the machine you are running this on?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Tuesday, August 7, 2012 11:45 PM
    Moderator

All replies

  • Hi Andrew,

    If you are using CodeFirst then you can add a 'Validate' method to your user object that would check for duplicates in the products list.

    Alternatively you can pass the user off to whatever validation library you have that can validate them and tell you the result.

    There are other possibilities as well, it really depends on what your exact scenario is and the syntax and structure of your validation library.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Friday, August 3, 2012 10:03 PM
    Moderator
  • The overall mechanics of validation I am ok with.  The issue is:

    //user.Products.Count = 1
    //user.Products.First().Name = "Product #1"
    
    var product = context.Products.Single(T => T.ItemGuid == ProductGuid);
    
    //product is "Product #1"
    //product = user.Products.First()
    
    var user = context.Users.Single(T => T.ItemGuid == UserGuid);
    
    user.Products.Add(product)
    
    //user.Products.Count = 1

    In the above example after I add 'Product #1' to the user.Products collection, the collection is still of length one.  I was not expecting this.  I not able to query the user.Products collection at the end of this and determine if the collection contains only unique items, because it contains only 1 item.  

    This means I have to either:
    1) Query the user.Products collection BEFORE I attempt an insert to detect non-uniqueness
    2) Query some property of the context to try and find duplicate relationship entries (I do not know how to do this)

    Is there another way to do this such that I can see that user.Products has multiple entries for 'Product #1'?


    Monday, August 6, 2012 1:38 PM
  • Are you using Lazy Loading in this scenario? You might get a scenario like this if your products are not lazily loaded and they aren't included in the query to get your user.

    If you change your user query to be something like this:

    var user = context.Users.Include("Products").Single(T => T.ItemGuid == UserGuid);

    Does that change it to be what you expected? If so then you might want to look at Lazy Loading the products collection. In Code first you need to mark the property as virtual to allow lazy loading.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Monday, August 6, 2012 6:20 PM
    Moderator
  • I am using lazy loading.  Additionally I did try to add the include statement to prefetch the "Products".  I am still getting the same result.

    Also, I am not sure if this behavior has changed latley, but I am using EF 4.0 (Not Latest)

    Monday, August 6, 2012 6:57 PM
  • Hi Andrew,

    Are you able to put together a basic sample project that shows this behaviour and send it to glennc 'AT' microsoft 'dot' com?

    I am not as familar with EF 4.0, and so it is possible that this is the way things are supposed to work in EF 4.0. If you provide me with some sample code then I can use that to show whether or not it is a bug or something that is by design. If it is by design then we can look at the best work arounds for you. If it is a bug then we can file work out when and where it needs to be fixed.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Monday, August 6, 2012 8:32 PM
    Moderator
  • Email has been sent
    Tuesday, August 7, 2012 1:04 PM
  • Hi Andrew,

    I agree that this looks like a bug in EF. I have created an issue for it here: http://entityframework.codeplex.com/workitem/441

    While that is investigated further I think that doing a contains before you do the add is the best workaround.

    It is possible that some other solution could be developed that would stop the exception from being thrown but otherwise the behaviour would stay the same. If we pursued this then we would write code that would detect that a duplicate has been adde and ignore it when doing SaveChanges, so EF would not attempt to save the duplicate. It would be more work, and not necesarilly any better, but we can look at it if you think it is worthwhile. 

    Out of interest, do you have .NET 4.5 installed on the machine you are running this on?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Tuesday, August 7, 2012 11:45 PM
    Moderator
  • I am ok with checking the collection first before I add as a workaround.

    Regarding .Net 4.5, I do not have this installed on my workstation.

    Thursday, August 9, 2012 12:36 PM