Correct way to remove M:M in PLINQO?

Question

Suppose you have this table structure:

Patient -> PatientTag -> Tag

A typical N:M relationship between patients and tags, PatientTag being the intermediate entity with both FKs. (PatientId and TagId).

I want to remove a specific tag, I have its ID. I’m doing this but I’d like to know if there’s a better way, since these are the 1st methods I write using PLINQO, I wouldn’t want to create bad practices from the beginning.

            using ( MyDataContext dc = DataContextFactory.GetDataContext() )
            {

                var options = new DataLoadOptions();
                options.LoadWith<Paciente>(p => p.PacienteTagList);
                options.LoadWith<PacienteTag>(pt => pt.Tag);
                dc.LoadOptions = options;

                // Get the Tag we're going to remove from the DB.
                var tag = dc.Manager.Tag.GetByKey( idTag);

                // Remove each patient from the association. 
                foreach ( Paciente pac in tag.PacienteList1 )
                {
                    // we need to retrieve it, won’t let us use the ‘pac’ object.
                    var pax = dc.Manager.Paciente.GetByKey( pac.IdPaciente );
                    pax.TagList.Remove(tag);
                }

                // now remove the tag
                dc.Manager.Tag.Delete(tag.TagId);

                // And commit the changes
                dc.SubmitChanges();
            }

Thanks for any insight on the subject.

Answer 1

What about simply using a foreign key with cascading delete, then deleting the tag itself and letting the database take care of deleting all the references. If you wanted to make sure that it wasn't in use, you could check that there aren't any patients associated with it first, though you may need to wrap it in a transaction if there are other processes accessing the same database.

Answer 2

I agree with tvanfosson do it on the database. Another way (may be safer imho) would be to create a strored procedure and call it from you code. Make sure its all wrapped up in a transaction that can deal with rollbacks in case something goes wrong