none
EF and Cascade Delete RRS feed

  • Question

  • If I create dummy data with just 1 row in each table, I can get cascading deletes to work properly.  When I use “real” data with dozens of rows, things fall apart and give me FK Constraint problems.

    Here are my POCO classes for the two highest tables:  cfr_pln and cfr_pln_dtl.

    For each unique Plan (a single row in cfr_pln), we’ll have lots of Plan Detail rows on cfr_pln_dtl.  So, on cfr-pln I set up an ICollection of Plan Detail objects.  On the cfr_pln_dtl POCO, I explicitely set foreign keys going back to the top Plan table.  Notice the yellow highlighting in the below code.  That represents the two ways to tag a FK … I have tried doing it one way, the other way, and using both forms at the same time, but it keeps giving me a FK issue on the Detail table when I delete the Plan.  i.e., it is NOT cascading during delete.

    <DataContract()>
    Public Class cfr_pln
        <Key()> <DatabaseGenerated(DatabaseGeneratedOption.Identity)>
        <DataMember()>
        Property cfr_pln_id As Integer
        <DataMember()>
        Property cfr_pln_typ_cd As Integer
        <DataMember()>
        Property cy As Short
        <DataMember()>
        Property tm_prd_cd As Integer
        <DataMember()>
        Property tm_prd_val As Short
        <DataMember()>
        Property cfr_pln_calc_typ_cd As Integer
        ' One to many relationship to pln_dtl
        Public Overridable Property PlanDetails As ICollection(Of cfr_pln_dtl) ' FK
    End Class
    Public Class cfr_pln_dtl
        <Key()> <DatabaseGenerated(DatabaseGeneratedOption.Identity)>
        Property cfr_pln_dtl_id As Integer
        <ForeignKey("Plan")>
        Property cfr_pln_id As Integer
        Property calc_revw_cnt As Integer
        Property run_dt As Date
        Property pln_revw_cnt As Integer
        Property lst_mod_dt As Date
        Property last_modified_bndfp_user_identifier As String
        Property cfr_revw_typ_cd As Integer
        <ForeignKey("cfr_pln_id")>
        Public Overridable Property Plan As cfr_pln
    End Class

    And here is the code that I use to actually delete a given Plan:

    Dim db As New Context
    Dim q = From tblPlan In db.cfr_pln
                        Select tblPlan
                        Where tblPlan.cfr_pln_id = id
    If q.count > 0 Then
    db.cfr_pln.Remove(q.First)
    db.SaveChanges()
    End If

    The LINQ query gets a collection of Plan table objects (technically just 1 item since the Where clause restricts it by ID).  I then call .First() on that list, and remove that item.  Again, that WORKS if I only have 1 row in Plan and 1 row in Plan Detail.  When I have 1 row in Plan but dozens of rows in Plan Detail, it blows up.

    Any help on this would be greatly appreciated.
    Thursday, July 26, 2012 9:40 PM

Answers