none
Why doesn't cascade on delete work? RRS feed

  • Question

  • Hello,

    I have the following code:

                Node node = Project.Nodes.Where(n => n.Description.Contains("Polymer Blending Header [Revalidated from PMA Expansion HAZOP (March 2017)]")).First();
                Deviation dev = node.Deviations.Where(d => d.DeviationId == 9158).First();
                Cause cause = dev.Causes.Where(c => c.CauseId == 19673).First();
                Consequence consequence = cause.Consequences.Where(cq => cq.ConsequenceId == 23822).First();
                Recommendation recommendation = consequence.Recommendations.Where(rec => rec.RecommendationId == 4459).First();
                Safeguard safeguard = consequence.Safeguards.Where(sg => sg.SafeguardId == 26988).First();
                Drawing drawing = recommendation.Drawings.Where(dw => dw.DrawingId == 3335).First();
                Remark remark = consequence.Remarks.FirstOrDefault();
    
                List<Node> nodes = Project.Nodes.ToList();
                foreach (Node n in nodes)
                {
                    Context.Entry(n).State = EntityState.Deleted;
                }
    
                EntityState d_es = Context.Entry(dev).State;
                EntityState c_es = Context.Entry(cause).State;
                EntityState cq_es = Context.Entry(consequence).State;
                EntityState sg_es = Context.Entry(safeguard).State;
                EntityState r_es = Context.Entry(recommendation).State;
                EntityState rm_es = Context.Entry(remark).State;
                EntityState dw_es = Context.Entry(drawing).State;



    This code is supposed to delete a whole bunch of entities. These entities are:

    * Nodes
    * Deviations
    * Causes
    * Consequences
    * Recommendations
    * Safeguards
    * Remarks
    * Drawings

    These entities form a hierarchy. Nodes are at the top. Deviations have a foreign key reference to Node, putting them directly under nodes. Causes are directly under deviations (they have a FK reference to them). And so on and so forth for all the other entities. The only time this patterns breaks is for Recommendations, Safeguards, and Remarks, all three of which are direct children to Consequences. Drawings are direct children of Recommendations.

    In the EDMX model, I set all relation to cascade on delete. What this means is that in order to delete everything, I should only have to delete Nodes. But when I run the code above (the foreach loop in particular), this is not what happens. All nodes are marked as deleted, all deviations are marked as modified, and all other entities are marked as unchanged.

    Why isn't the cascade setting working?

    Here's an example of how I set cascading on delete for the relation between nodes and deviations:



    • Edited by gib898 Tuesday, March 13, 2018 10:21 PM using code block
    Tuesday, March 13, 2018 10:20 PM

All replies

  • I don't bother with EF cascade deletes. I set cascade delete on the parent record in the parent table using the database administration tool like MS SQL Server Management Studio and let the database engine do the deletes when I use the delete to the parent record in EF.
    Wednesday, March 14, 2018 2:51 AM
  • Hi gib898,

    >>Why isn't the cascade setting working?

    If you use entity framework database first, As DA924x said,  you need to alter your database table with DELETE CASCADE, like this:

    ALTER TABLE [dbo].[ReportComments] (
        [Id]              INT            IDENTITY (1, 1) NOT NULL,
        [Name]            NVARCHAR (MAX) NULL,
        [Report_ReportId] INT            NOT NULL,
        CONSTRAINT [PK_dbo.ReportComments] PRIMARY KEY CLUSTERED ([Id] ASC),
        CONSTRAINT [FK_dbo.ReportComments_dbo.Reports_Report_ReportId] FOREIGN KEY ([Report_ReportId]) REFERENCES [dbo].[Reports] ([ReportId]) ON DELETE CASCADE
    );

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 14, 2018 7:45 AM
    Moderator
  • If you use entity framework database first, As DA924x said,  you need to alter your database table with DELETE CASCADE, like this:

    ALTER TABLE [dbo].[ReportComments] (
        [Id]              INT            IDENTITY (1, 1) NOT NULL,
        [Name]            NVARCHAR (MAX) NULL,
        [Report_ReportId] INT            NOT NULL,
        CONSTRAINT [PK_dbo.ReportComments] PRIMARY KEY CLUSTERED ([Id] ASC),
        CONSTRAINT [FK_dbo.ReportComments_dbo.Reports_Report_ReportId] FOREIGN KEY ([Report_ReportId]) REFERENCES [dbo].[Reports] ([ReportId]) ON DELETE CASCADE
    );

    Our application is database first. I suppose this is why setting cascade on delete in the data model doesn't work?

    I will see about setting cascade on delete in the database. Hopefully that works.

    Wednesday, March 14, 2018 2:44 PM
  • Well, neither ON DELETE CASCADE nor INSTEAD OF DELETE work.

    Cascading doesn't work because for all the objects I'd like to cascade upon deletion, there will be multiple or cyclical cascade paths which is not allowed (for some bizarre reason) in SQL Server.

    Instead of delete is a type of trigger I can attach to the delete event on a table. I tell it to delete all records with a foreign key to the record being deleted and then delete the record. This works great so far... but entity framework doesn't know about it. So when I tell entity framework to delete only the nodes (the objects at the top of the hierarchy), it thinks this operation cannot be done because there are other objects beneath nodes that must be deleted first.

    Looks like I'm stuck deleting each and every entity manually in code.

    Wednesday, March 14, 2018 8:39 PM
  • Hi gib898,

    >>Looks like I'm stuck deleting each and every entity manually in code.

    Based on your description, I create a simple with database cascade delete by using database first, I do not change anything in entity in EDMX file. it works well.

    Do you encounter any exception when update database cascade, please try to delete edmx file, and re-generate again, then check if it works.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 15, 2018 7:39 AM
    Moderator
  • Thanks Zhang,

    I managed to get cascading to work in EF but I'm still getting an error:

    "The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted."

    I've simplified the problem to just two tables:

    * Recommendations

    * Drawings

    This is a one-to-many relation; every recommendation can have multiple drawings.

    When I was trying to manually delete all drawings, my code looked like this:

                                List<Recommendation> recommendations = cq.Recommendations.ToList();
                                foreach (Recommendation r in recommendations)
                                {
                                    List<Drawing> drawings = r.Drawings.ToList();
                                    foreach (Drawing dg in drawings)
                                    {
                                        _context.Entry(dg).State = EntityState.Deleted;
                                    }
    
                                    _context.Entry(r).State = EntityState.Deleted;
                                }

    This worked (no errors) but it's extremely slow.

    Then I tried allowing EF to automatically cascade deletes:

                                List<Recommendation> recommendations = cq.Recommendations.ToList();
                                foreach (Recommendation r in recommendations)
                                {
                                    Drawing drawing = null;
                                    if (r.Drawings.Count > 0)
                                    {
                                        drawing = r.Drawings.First();
                                    }
    
                                    _context.Entry(r).State = EntityState.Deleted;
    
                                    if (drawing != null)
                                    {
                                        EntityState es = _context.Entry(drawing).State;
                                    }
                                }

    My code shows that the state of the drawing is "modified" after the recommendation is set to deleted. Drawing.RecommendationId is still set to the ID of the deleted recommendation and Drawing.Recommendation is set to null. My guess is that this is NOT what's supposed to happen if EF has cascading on delete working properly. I would guess that the state of the drawing should be "deleted".

    Friday, March 16, 2018 5:23 PM
  • This worked (no errors) but it's extremely slow.

    And letting EF do it is too slow, which you'll find out if you ever try to do it with EF sitting behind a WCF service and watch the service timeout, as example.

    Letting the DB engine do the cascading deletes is 10,000 time more faster than code you have written to do it or letting EF do it.

    You'll be better off here with writing stored procedure do it manually with T-SQL and calling if from C# code, which is still using the DB engine to do the deletes that is still 10,000 faster than trying to do it from code or using EF.

    Been there and done that!

    Friday, March 16, 2018 8:31 PM
  • This worked (no errors) but it's extremely slow.

    And letting EF do it is too slow, which you'll find out if you ever try to do it with EF sitting behind a WCF service and watch the service timeout, as example.

    Letting the DB engine do the cascading deletes is 10,000 time more faster than code you have written to do it or letting EF do it.

    You'll be better off here with writing stored procedure do it manually with T-SQL and calling if from C# code, which is still using the DB engine to do the deletes that is still 10,000 faster than trying to do it from code or using EF.

    Been there and done that!

    I was afraid someone might say that. :D

    We actually did have a stored procedure doing the work in the beginning, but due to complications in what we're trying to do, we're looking for an alternative.

    Even though deleting through a stored procedure is by far the fastest way to do it, it might be acceptable if getting cascading to work in EF is at least faster than doing all deletes in EF manually.

    But in any case, I'd like explain our original approach to the problem and why it didn't work.



    The original approach was like this:

    1) User uploads file.
    2) Delete stored procedure runs to delete old data.
    3) File is parsed and new data is made into new entities.
    4) Once file parsing is done, new entities are saved to database (with context.SaveChangesAsync()).

    The issue was that this whole procedure is done in a transaction (C#, not SQL), which locks the database. Even though the stored procedure is fast, it's the parsing of the file that takes time (can take on the order of 10 minutes or more), and during that time, the database is locked, which means no other process can access the database until step 4 above is done. We have a web API that sometimes needs to access the database, and while we have a file being uploaded/parsed, it can't. With large files, it will be blocked for a significant amount of time, in which case it usually times out. This is bad when we have clients trying to view their reports (which is what the API does).

    And by a C# transaction, I mean this:

            /// <summary>
            /// Runs at the begining of each Post request to setup 
            /// a Transaction wrapper
            /// </summary>
            void IRunOnEachRequest.Execute()
            {
                if (_httpContext.Request.HttpMethod == "POST")
                {
                    _httpContext.Items["_Transaction"] =
                        _context.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
            }
    
            /// <summary>
            /// Commits or rollsback the transaction at the end or each request dependant on the
            /// <c>HttpContext</c> <c>_Error</c> item's value
            /// This is only run for Post requests 
            /// </summary>
            void IRunAfterEachRequest.Execute()
            {
                if (_httpContext.Request.HttpMethod == "POST")
                {
                    var transaction = (DbContextTransaction) _httpContext.Items["_Transaction"];
                    var badRequest = (_httpContext.Response.StatusCode != (int)HttpStatusCode.OK &&
                        _httpContext.Response.StatusCode != (int)HttpStatusCode.Redirect &&
                        _httpContext.Response.StatusCode != (int)HttpStatusCode.MovedPermanently);
                    if (transaction?.UnderlyingTransaction?.Connection == null
                     || transaction.UnderlyingTransaction.Connection.State != ConnectionState.Open)
                    {
                        Debug.WriteLine("Skipping transaction commit or rollback because the underlying connection is closed");
                        return;
                    }
    
                    if (_httpContext.Items["_Error"] != null  || badRequest)
                    {
                       
                        transaction.Rollback();
                        transaction.Dispose();
                    }
                    else
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                }
            }

    ^ This runs on every request/response round from the client to the server (like when the user uploads a files to the point when the data from the file is saved to the database). From what I gather, it seems that during a transaction like this, as soon as it hits the database, the database is locked until the transaction is complete.

    Given all this, there are a number of approaches we tried. This one for example:

    In other words: Do all the parsing of the file first, THEN run the delete stored procedure and save. That way, it won't touch the database until much later in the process (after which running the stored procedure and saving the new entities will only take seconds). The problem with this is that running the stored procedure didn't update the EF cache, so even though the old entities were gone from the database, EF was still holding onto them, and when it came time to save, we got errors to the effect of: duplicates detected (old and new entities conflict). IOW, it was trying to save both the new entities AND the old ones. We couldn't quite find a way to update the EF cache after running the stored procedure because that removed both the new AND the old data (I'm assuming it just takes a snapshot of the database which, at that point, contains neither the old nor the new entities).

    The only reason this didn't seem to be an issue before was that between the time when the delete stored procedure was run and the time when it began to parse the file, a new context was injected into the file parsing function. I'm not too familiar with how injection works (especially injecting database contexts), but it seemed like it was able to inject an up-to-date cache (i.e. without the old entities after the stored procedure had run).

    In any case, we decided to try a different approach after that: deleting from the EF cache:

    Here, we are deleting from the EF cache first, then parsing the file, then saving. Everyone agreed (and accepted) that this would be somewhat slower than running the stored procedure, but the goal was to minimize the time during which the database is blocked (and therefore minimize the chances that the client will experience a timeout while trying to view their reports). We figured if it takes an extra while to delete the entities and then parse the file, that would be fine so long as the final step (saving to the database) took minimal time. Initially, I thought I could get cascading to work in EF. I figured that if I let EF handle all the work of deleting entities (starting with manual deletions on just a few at the topmost level of the hierarchy), then it's bound to be much quicker than doing manual deletions for every single entity (most likely not as fast as the stored procedure but hopefully faster than manual deletions through-and-through). I'm still not able to get cascading to work in EF and so deleting all the entities manually is exceptionally slow. We agreed that we would accept a bit of slowness, but not THIS slow.

    ^ So that's where I'm at now. We're trying to get cascading to work in EF in the hopes of at least making it a bit faster. But if anyone has any alternative suggestion, I'm all ears.

    Thanks

    Monday, March 19, 2018 3:46 PM
  • Hi gib898,

    If possible, could you please share a simple demo, which could reproduce the issue via OneDrive.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 20, 2018 2:18 AM
    Moderator