Will this safely delete my record?

Question

I hate these three tables that. Two tables have a many to many relationship and as such it generates a third table.

I'm using Linq-to-SQL and in the .dbml file I've dragged all the folder there to the graphic surface.

Here is the method I'm using to delete an Area safely. Remember that documents are associated to an Area, so I can't just delete it and leave documents hanging.

        ScansDataContext db = new ScansDataContext();

        /// <summary>
        /// Deletes an Area object from the database along with all associations in the database.
        /// </summary>
        /// <param name="area">Area object to save</param>
        public void Delete(Area area)
        {
            db.DocumentAreaRelations.DeleteAllOnSubmit(area.DocumentAreaRelations);
            db.Areas.DeleteOnSubmit(area);
            db.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
        }

Answer 1

Assuming that you still have the constraints in the database that enforce the relationships...that looks like you should be good to go to me. If you don't have the keys set up in the database, you need to add them.

You're deleting all the relationships first, the actual entity second, and SubmitCahnges() wraps everything in a local transaction so that it's a single unit of work.

Answer 2

Yes that should delete the record. But be sure that you have set the database constrain correctly e.g.

CONSTRAINT [FK_tblCategory_tblCompany] FOREIGN KEY([CompanyID]) 
REFERENCES [tblCompany] ([ID])ON DELETE CASCADE ON UPDATE CASCADE

or you can set this in your dataentity

Answer 3

I've a few tables that do something similiar, using MS SQL Server 2008.

I have the foreign keys set to cascade on delete. So given you're table situation, I would delete an area, and once the delete command was sent to the server it would automatically cascade down to the join table. An OnDelete Trigger can be used in the join table to assure the documents are safely removed, after their join record is taken out.

LINQ has never complained, but to be safe I always had a "Scrubbing" function in my LINQ classes that clean out the foreign key relationships of a single object if they've been enumerated. This is mostly for objects being edited though, as opposed to being deleted.

Partial Class MyLINQObject  
    Public Sub ScrubRelationships()  
        _RelatedTableObjects = New EntityRef(Of RelatedTableObject)  
    End Sub  
End Class

Hope that helps!