locked
Cascading Deletes with Entity Framework and ADO.NET Data Services RRS feed

  • Question

  • Hi folks,

    Does anyone know how to handle the foreign key constraint when deleting a Customer in the Northwind database? (I want to deal with the constraint in code, not alter the database itself.)

    I'm using Silverlight, EF, LINQ, and ADO.NET Data Services. I've pasted in some non-working code below. Any pointers to working code or a sample would be most welcome.

    Thanks,

    Ken


    1     Private Sub btnDelete_Click _  
    2        (ByVal sender As System.Object, _  
    3         ByVal e As System.Windows.RoutedEventArgs)  
    4         Dim cust As Customer = ItemListBox.SelectedItem  
    5         svcContext = New NORTHWINDEntities _  
    6         (New Uri("NorthWindDataService.svc", UriKind.Relative))  
    7         Try 
    8             Dim q = (From c In svcContext.Customers _  
    9                      Where (c.CustomerID = "WOLZA") _  
    10                      Select c).FirstOrDefault()  
    11             Dim ord As New Orders()  
    12             For Each ord In q.Orders  
    13                 svcContext.DeleteObject(ord)  
    14                 Dim od As Order_Details  
    15                 For Each od In ord.Order_Details  
    16                     svcContext.DeleteObject(od)  
    17                 Next 
    18             Next 
    19             svcContext.DeleteObject(cust)  
    20             svcContext.BeginSaveChanges(SaveChangesOptions.Batch, _  
    21            AddressOf OnSaveChangesCompleted, q)  
    22         Catch ex As Exception  
    23             MessageBox.Show(ex.Message)  
    24         End Try 
    25     End Sub 

    Author: ASP.NET 3.5 For Dummies
    Sunday, December 14, 2008 10:01 PM

Answers

  • Hi folks,

    I found a tutorial called How to Modify or Delete Data (ADO.NET Data Services/Silverlight)

     http://msdn.microsoft.com/en-us/library/cc903956(VS.95).aspx

    In it, the code deletes all products in a category and then deletes the category. I just have to extend the principle to a three-level deletion by removing the order_details for an order, the orders for the customer, and finally the customer. It looks like this is all a bit involved for Linq, so I'll drop that aspect.

    BTW, I keep wondering if this needs to be so complicated. I guess that's the way of Asychronous data.

    Thanks to all who offered assistance!

    Ken


    Author: ASP.NET 3.5 For Dummies
    • Marked as answer by Cox, Ken Monday, December 15, 2008 11:27 PM
    Monday, December 15, 2008 11:27 PM

All replies

  • You don't mention what the error is but you might need to call DetachLink on all the references...

    svcContext.DetachLink(ord, "Order_Details", od);  
    svcContext.DetachLink(od, "Order", ord);  
     
    Monday, December 15, 2008 12:08 AM
  • The error happens in lines 8-10 (the LINQ query) and says "Specified method is not supported."

    My previous attempts to make this work returned a SQL-like error message about FK_Orders_Customers. I think I need to start from scratch.

    Author: ASP.NET 3.5 For Dummies
    Monday, December 15, 2008 4:05 AM
  •  Hi Ken,
     In Silverlight , all our GET/CUD methods are asynchronous .
     If the above code is to run inside an SL app, it has to be changed to :


    Change              Dim q = (From c In svcContext.Customers _  
    9                      Where (c.CustomerID = "WOLZA") _  
    10                      Select c).FirstOrDefault()  
    1


    to  :

                 Dim q = (From c In svcContext.Customers _  
    9                      Where (c.CustomerID = "WOLZA") _  
    10                      Select c)
    q = q.Expand("Orders,Orders/Order_Details")

    //FirstOrDefault causes an Immediate , synchronous ,execution of the
    //query and is unsupported in SL
    1

    Dim cb as AsyncCallback

    cb = CType(AddressOf CustomerQueryCallback, AsyncCallback)

    q.BeginExecute(cb,q)

    Sub CustomerQueryCallback(ByVal asyncResult As IAsyncResult)

    Dim q as DataServiceQuery(Of Customers)

    q = CType(asyncResult.AsyncState,DataServiceQuery(Of Customers))

    var firstCustomer = q.EndExecute(asyncResult).First()


      For Each ord In firstCustomer.Orders  
    13                 svcContext.DeleteObject(ord)  
    14                 Dim od As Order_Details  
    15                 For Each od In ord.Order_Details  
    16                     svcContext.DeleteObject(od)  
    17                 Next 
    18             Next
    19  svcContext.DeleteObject(cust)  
    20             svcContext.BeginSaveChanges(SaveChangesOptions.Batch, _  
    21            AddressOf OnSaveChangesCompleted, q)  
    22         Catch ex As Exception  
    23             MessageBox.Show(ex.Message)  
    24         End Try 
    25     End Sub 
    End Sub


     my VB is a bit rusty , the above code provides an idea of how this would work in Silverlight. Let me know if this helps.


    Phani Raj Astoria
    Monday, December 15, 2008 7:00 PM
    Moderator
  • Hi folks,

    I found a tutorial called How to Modify or Delete Data (ADO.NET Data Services/Silverlight)

     http://msdn.microsoft.com/en-us/library/cc903956(VS.95).aspx

    In it, the code deletes all products in a category and then deletes the category. I just have to extend the principle to a three-level deletion by removing the order_details for an order, the orders for the customer, and finally the customer. It looks like this is all a bit involved for Linq, so I'll drop that aspect.

    BTW, I keep wondering if this needs to be so complicated. I guess that's the way of Asychronous data.

    Thanks to all who offered assistance!

    Ken


    Author: ASP.NET 3.5 For Dummies
    • Marked as answer by Cox, Ken Monday, December 15, 2008 11:27 PM
    Monday, December 15, 2008 11:27 PM
  • Hi Phani,

    Thanks for your answer. Unfortunately, the compiler doesn't like Expand here:

     'Expand' is not a member of 'System.Linq.IQueryable(Of SLObsvColl.NWServiceReference.Customer)'.

    Ken

    Author: ASP.NET 3.5 For Dummies
    Monday, December 15, 2008 11:38 PM