Deleting hierarchical objects using LINQ To SQL RRS feed

  • Question

  • Hi


    I have one to many relationship between client and client_address entity. When I try to delete client I get the following error


    "The DELETE statement conflicted with the REFERENCE constraint "FK_client_address_client". The conflict occurred in database "test", table "dbo.client_address", column 'client_id'.

    When it comes to constructing the client entity, it does correctly retrieve child records, but why doesn’t it semantically deletes the records i.e. deleting the child records before parent? Doesn’t LINQ to SQL support the concept of Cascade delete?


    Code Snippet



    public void CanDeleteEntity()


    client newClient = new client


    title = "Mr.",

    first_name = "First Name",

    last_name = "Last Name"



    newClient.client_addresses.Add(new client_address


    hierarchy = 1,

    is_default = true,

    post_code = 3030,

    state = "vic",

    street = "Some cool street",

    suburb = "hawthorn"



    DataClasses1DataContext dc = new DataClasses1DataContext();

    // Insert the cleint.




    // Retrieve inserted cleint.

    client retClient = (from c in dc.clients.ToList()

    where c.client_id == newClient.client_id

    select c).FirstOrDefault();


    // Delete the client.






    How would I achieve this if there is no native support?



    Friday, October 24, 2008 12:00 AM

All replies

  • LINQ to SQL does support cascade delete, in the database.  You have to set up your tables to do automatic cascade delete. 


    Friday, October 24, 2008 2:12 AM
  • Is there a way for orchestrating it, if you are not allowed to setup cascade delete on the table?


    How would we manage objects generated by different DataContext in a layered application? Or, LINQ to SQL is not matured enough for layered apps?


    I use repositories responsible for managing data in my application. I hit into issues like “cannot delete an entity which is not attached” and when I attach it gives “entity was created by another DataContext”.

    How does LINQ support tiered architecture?


    Does seem to support many to many relationship (intermediate table approach) !!!



    Friday, October 24, 2008 5:23 AM
  • Any pointers on this!!!


    Tuesday, October 28, 2008 10:30 PM
  • You'll have to manually delete all the children.  You can use Table<T>.DeleteAllOnSubmit() for this.


    Another alternative is to write your own DeleteXXX() method on your DataContext, where xxxx is your parent object's type name.  This is the basic mechanism that L2S uses to override insert, update and delete behaviors for hooking up SP.  However, you can use this same mechanism for your own needs.


    Specifically, you could use it to inject a call to the database to delete all child rows, and then use ExecuteDynamicDelete to get the normal default L2S delete behavior.



    public partial class MyDataContext



       private void DeleteMyParent(MyParent parent)


           this.ExecuteCommand("delete from child_table as c where c.ParentID = {0}", parent.ID);





    Thursday, October 30, 2008 2:03 AM