Delete Record RRS feed

  • Question

  • Hello,

    I am deleting a Document with Linq and the related records in DocumentsPages are deleted but the Pages are not.

    What am I missing? Here are my SQL tables and relationships which I mapped using Linq To Sql:

    create table dbo.Documents
      Id int identity not null,
      Created datetime not null,
      [File] varbinary(max) filestream constraint DF_Documents_File default
      Updated datetime not null,
        constraint PK_Documents primary key clustered(Id)
    ) -- Documents
    create table dbo.DocumentsPages
      DocumentId int not null,
      PageId int not null,
        constraint PK_DocumentsPages primary key clustered(DocumentId,
    ) -- DocumentsPages
    create table dbo.Pages
      Id int identity not null,
      Number int not null,
      [Key] uniqueidentifier not null rowguidcol constraint U_Pages_Key
      [File] varbinary(max) filestream constraint DF_Pages_File default
        constraint PK_Pages primary key clustered(Id)
    ) -- Pages
    And the following relationships:

    alter table dbo.DocumentsPages
    add constraint FK_DocumentsPages_Documents foreign key (DocumentId)
    references Documents(Id) on delete cascade on update cascade,
        constraint FK_DocumentsPages_Pages foreign key (PageId) references
    Pages(Id) on delete cascade on update cascade;
    Friday, October 16, 2009 1:48 AM


  • Hi Miguel,

    I believe the behavior is expected and correct. You have a many-to-many relationship between Document and Pages which means that One document can have one or more pages and one page can have one or more documents . So deleting a page entry when you delete a document does not makes sense because that page can be associated to some other documents also (at least your db structure says this) and this may lead to invalid foreign keys in your database.

    Are you sure there is no mistake in your db schema design and you need a Many-Many relationship here?

    Also, it would make sense if you want to delete a Page when ALL the related documents are deleted. But AFAIK you will have to manually do it in your application’s business logic or use a table trigger.

    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Yichun_Feng Thursday, October 22, 2009 1:14 AM
    Monday, October 19, 2009 6:27 AM