Entity Framework - how do I find out what's causing referential integrity bug?

Working with entity framework, my code uses LINQ to get a collection of objects that need deleting from the database. This collection is sometimes fairly large depending on the circumstances - 30 or 40 objects.

I proceed to try and de-couple these objects from their related objects so that I can delete them, then I call context.DeleteObject() against each and then finally context.SaveChanges().

Most of the time this works perfectly well, especially when the collection of objects to delete is very small. However when its large, this code sometimes throws and error around referential integrity, specifically:

The DELETE statement conflicted with the REFERENCE constraint 
\"FK_ServiceFeatureSchemaFragmentSchemaFragment_SchemaFragment\". 
The conflict occurred in database \"MyDataBase\", 
table \"dbo.ServiceFeatureSchemaFragmentSchemaFragment\".\r\n
The statement has been terminated. 

So it seems that sometimes my objects have开发者_开发百科 additional constraints that I'm failing to find and de-couple before deleting.

What I'm struggling with is that because I'm working with a relatively large collection, I can't work out which one(s) are causing this to happen. I'm looking for suggestions as to how I can move forward by identifying the problem objects in some way, for example is there a way in which you can pre-determine whether objects in a collection are going to throw these sorts of error before you delete them? Or another approach?

Suggestions gratefully received.

Write a stored procedure in SQL Server where you can see the impact of your FK/PK relationships without an additional layer of EF mapping to confuse things. This example uses either the integer based ezUserID from the User's main profile table or the ASP.NET membership GUID to start the deleting.

You could simply use Cascading Deletes when you define your relationships ...

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[ezUser_Delete]
    @ezUserID int = NULL,
    @UserName nvarchar(128) = NULL
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @UserID uniqueidentifier;

    --DECLARE @TranName VARCHAR(20);   --Generally shouldn't use named transactions in stored procedures
    --SELECT @TranName = 'MyTransaction';
    SET XACT_ABORT ON

    IF @ezUserID IS NULL AND @UserName IS NULL RETURN 0

BEGIN TRY
    BEGIN TRANSACTION;

            IF ISNULL(@ezUserID, 0) > 0

                BEGIN
                    SELECT @UserID = UserID FROM ezUsers WHERE ezUserID = @ezUserID
                    SELECT @UserName = UserName FROM aspnet_Users WHERE UserID = @UserID 
                    IF (@UserName IS NULL)
                        BEGIN
                            ROLLBACK TRANSACTION;
                            RETURN -1
                        END
                END

            ELSE IF (LEN(ISNULL(@UserName,'')) > 0)

                BEGIN
                    SELECT @UserID = UserID FROM aspnet_Users WHERE UserName = @UserName
                    SELECT @ezUserID = ezUserID FROM ezUsers WHERE UserID = @UserID  
                    IF (@ezUserID IS NULL)
                        BEGIN
                            ROLLBACK TRANSACTION;
                            RETURN -1
                        END
                END

            ELSE 
                RETURN -1

            SELECT 'DELETE' AS [Command]
                , U.UserID
                , U.UserName
                , RoleName
                , M.Email
                , EU.ezUserID
                , EU.FirstName, EU.LastName
                , CallSign
                , Gender
                , UIR.RoleID

            FROM dbo.aspnet_Users U 
                    LEFT OUTER JOIN dbo.aspnet_Membership M ON U.UserID = M.UserID

                    LEFT OUTER JOIN dbo.ezUsers EU ON U.UserID = EU.UserID      

                    LEFT OUTER JOIN dbo.aspnet_UsersInRoles UIR ON U.UserID = UIR.UserID
                    LEFT OUTER JOIN dbo.aspnet_Roles R ON UIR.RoleID = R.RoleID     
            WHERE U.UserID = @UserID 

            IF @UserID IS NULL
                RETURN -1

            DELETE FROM dbo.ezUsersDating   WHERE ezUserID = @ezUserID  
            DELETE FROM dbo.ezUsersPhysicalIdentity WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezMessages WHERE AuthorID = @ezUserID

            DELETE FROM dbo.ezTheUsual WHERE ezUserID = @ezUserID
            DELETE FROM dbo.ezRentals WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezUsersSharedActivities WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezCurrentVehicle WHERE UVID IN (SELECT UVID FROM dbo.ezUsersVehicles WHERE ezUserID = @ezUserID)
            DELETE FROM dbo.ezDriverVehicles WHERE ezUserID = @ezUserID  -- Delete this Table
            DELETE FROM dbo.ezOwnerVehicles WHERE OwnerID = @ezUserID   
            DELETE FROM dbo.ezUsersVehicles WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezPL8Messages 
                WHERE PL8ID IN (
                SELECT PL8ID FROM dbo.ezPL8s WHERE VehicleID IN (SELECT VehicleID FROM dbo.ezVehicles WHERE CreatedBy = @ezUserID))

            DELETE FROM dbo.ezPL8s WHERE VehicleID IN (SELECT VehicleID FROM dbo.ezVehicles WHERE CreatedBy = @ezUserID)

            DELETE FROM dbo.ezPL8s_Vehicles WHERE VehicleID IN (SELECT VehicleID FROM dbo.ezVehicles WHERE CreatedBy = @ezUserID)

            DELETE FROM dbo.ezVehicles WHERE CreatedBy = @ezUserID

            DELETE FROM dbo.ezImages_Users
            WHERE ImageID IN (SELECT ImageID FROM dbo.ezImages 
                                WHERE UploadedBy = @ezUserID)

            DELETE FROM dbo.ezImages_Users
            WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezImages 
            WHERE UploadedBy = @ezUserID

            DELETE FROM dbo.ezPets
            WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezAccounts
            WHERE CPID IN (SELECT CPID FROM dbo.ezCompany_People WHERE ezUserID = @ezUserID)

            DELETE FROM dbo.ezCompany_People
            WHERE ezUserID = @ezUserID          

            DELETE FROM dbo.ezMedicals
            WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezVehicles WHERE CreatedBy = @ezUserID

            DELETE FROM dbo.ezParentChildren WHERE ParentID = @UserID OR ChildID = @UserID
            DELETE FROM dbo.ezEmergencyContacts WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezDriveThrus_Customers WHERE ezUserID = @ezUserID   

            DELETE FROM dbo.ezRelationshipsP2P WHERE RelFromID = @UserID OR RelToID = @UserID

            DELETE FROM [dbo].[ezPL8Meaning] WHERE ezUserID = @ezUserID

            DELETE FROM dbo.ezUsers WHERE UserID = @UserID 

            DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE UserID = @UserID
            DELETE FROM dbo.aspnet_Profile WHERE UserID = @UserID
            DELETE FROM dbo.aspnet_UsersInRoles WHERE UserID = @UserID

            DELETE FROM dbo.aspnet_Membership WHERE UserID = @UserID    

            DELETE FROM dbo.aspnet_Users WHERE UserID = @UserID     

    COMMIT TRANSACTION;  
END TRY

BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;

    RAISERROR('Error', 16, 1)
    RETURN 0
END CATCH

END 






GO