none
Enforce Foriegn Key Constraint and deleteing Entity Gaphs. RRS feed

  • Question

  • Hi,

    1.) I am using entity framework 4.0.

    2.) I previously loaded a product graph with entity framework and then use DeleteObject to remove the object and its children. This worked fine.

    3.) Due to an enviroment error our database was updated and now the products childrens have forigen key constraints.

    3.) I created a new model in the designer.

    5.) When the code ran again I got the "The DELETE statement conflicted with the REFERENCE constraint".

    6.) If I set the "Enforce Foriegn Key Constraint" to no from the old code runs fine.

    7.) I have tried attaching and setting the state and using the delete object for each of the products children but entity framework still won't delete the child and I keep getting the same error.

    I can't change the database and I don't know what I am doing wrong.

    Friday, August 17, 2012 9:03 AM

Answers

  • Hi thanks for answering. The child table is called product company and it contains a composite primary key made up from product and company. It is in a database relationship with product and company. I wonder if my model is correct becuase I have used entity framework to delete data before on another database. The delete was against a child table with a composite but made out of a form number and a date. It wasn't in two relationships with other tables. I also wonder if my model is correct. Becuase I tried to delete with and got it working but using a seperate context to call the data and another context to perform the delete. It however did not delete anything.

     
    • Marked as answer by Inglor Tuesday, August 21, 2012 5:26 PM
    Saturday, August 18, 2012 10:43 AM

All replies

  • The child table has a compsoite primary key made up of the forigen key of product and another company table.
    Friday, August 17, 2012 9:23 AM
  • Hi,

    It sounds like your child table might be causing the problem, by not being able to be deleted. Can you clarify the relationships for me?

    From what I understand there is a products table and a child table. The child table has a primary key of the productId plus some other Id from another table, is that correct? There are no other relationships to the child or parent table?


    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 17, 2012 8:59 PM
    Moderator
  • Hi thanks for answering. The child table is called product company and it contains a composite primary key made up from product and company. It is in a database relationship with product and company. I wonder if my model is correct becuase I have used entity framework to delete data before on another database. The delete was against a child table with a composite but made out of a form number and a date. It wasn't in two relationships with other tables. I also wonder if my model is correct. Becuase I tried to delete with and got it working but using a seperate context to call the data and another context to perform the delete. It however did not delete anything.

     
    • Marked as answer by Inglor Tuesday, August 21, 2012 5:26 PM
    Saturday, August 18, 2012 10:43 AM
  • Hi Inglor,

    Welcome to MSDN Forum.

    Does the database schema like this?

    If yes, please look at the properties of your relationship, you needn't to change the 'Enforce Foreign Key Constraint', if you change it as no, the child record will not be deleted, so let it as 'Yes'. Please change the 'Delete Rule' to 'Cascade', which is in 'INSERT And UPDATE Specification' item, then the code will work.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, August 20, 2012 1:55 AM
    Moderator
  • Hi guys thanks for the replies. I have a couple of things that I want to share.

    1.) I cleaned down my build and got a fresh version from TFS. This actually fixed the problem. It seems that the old model was still there. Not wanting to delete it straight out I excluded it from the project. I did this so I could refer back to some of the structure that I had put into the old model. I believe the application was continually loading the old model which had not been updated with the new foriegn keys.

    2.) I'm using the poco template (not self tracking) from Visual Studio and not working with entities directly.

    3.) I can't modify the target database so I can set the deletes or updates to cascade.

    4.) I'm loading the parent product record along with all of its children and using the deleteobject method against the parent. So the entire graph for the product and children are loaded into the context. This delete is actually working even though cascade delete is not turned on.

    I have seen other post like Allens stating that cascade delete needs turned on. My question now is why are the deletes working.

    Monday, August 20, 2012 11:04 AM
  • Hi Inglor,

    If using 'Include' method to load all the related entities into memory, the return entity will be a result of many tables join. Please refer to the code below.

    company c = context.companies.Include("company_product").First();

    The actual T-SQL which executed in the database is

    SELECT 
    [Project1].[id] AS [id], 
    [Project1].[name] AS [name], 
    [Project1].[C1] AS [C1], 
    [Project1].[company_id] AS [company_id], 
    [Project1].[product_id] AS [product_id], 
    [Project1].[note] AS [note]
    FROM ( SELECT 
    	[Limit1].[id] AS [id], 
    	[Limit1].[name] AS [name], 
    	[Extent2].[company_id] AS [company_id], 
    	[Extent2].[product_id] AS [product_id], 
    	[Extent2].[note] AS [note], 
    	CASE WHEN ([Extent2].[company_id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    	FROM   (SELECT TOP (1) [c].[id] AS [id], [c].[name] AS [name]
    		FROM [dbo].[company] AS [c] ) AS [Limit1]
    	LEFT OUTER JOIN [dbo].[company_product] AS [Extent2] ON [Limit1].[id] = [Extent2].[company_id]
    )  AS [Project1]
    ORDER BY [Project1].[id] ASC, [Project1].[C1] ASC

    So, if you delete the company entity, all the related records will also be deleted, EF manages to generate appropriate T-SQL which will not stop the deleting.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, August 21, 2012 2:04 AM
    Moderator
  • Hi Allen,

            yes I am using the include method when I recover data.

    Tuesday, August 21, 2012 5:28 PM