• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

c# Linq statement needed to delete records from 2 tables



Say I have to tables that are related by and ID (int) in a one to many join.   I want a linq statement to delete the one row with a certain ID in the “masterTable” and all the child records with the same ID in the “ChildTable”

MasterTable
ID int
Name varchar(100)

ChildTable
ID int
Region varchar(100)



0
ToString1
Asked:
ToString1
  • 3
  • 3
  • 2
  • +1
7 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
Can you modify the table schema? Because this sort of thing would be better implemented as a cascade delete rule on your tables.
0
 
roxviperCommented:
Hi,

Here is an example of mine:

            
InvoicerDataContext db = new InvoicerDataContext();
            var costCenter = db.CostCenters.SingleOrDefault(q => q.CostCentersID == CostCenterID);
            if (costCenter != null)
            {
                var invoices = db.Invoices.Where(q => q.CostCenterID == CostCenterID);
                db.Invoices.DeleteAllOnSubmit(invoices);
                db.SubmitChanges();

                db.CostCenters.DeleteOnSubmit(costCenter);
                db.SubmitChanges();
            }

Open in new window

0
 
ToString1Author Commented:
OK thanks

Yes I already have a cascade delete rule in my database schema but have not written any code yet
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Carl TawnSystems and Integration DeveloperCommented:
If you have a cascade rule then you only need to delete the header record, the cascade will take the related child records with it.
0
 
ToString1Author Commented:
I have tried the code above but get the error

Cannot remove an entity that has not been attached

It appears to remove child records but not master record
0
 
MathiyazhaganCommented:
hi, have attached comprehensive linq query to delete master and child records. but, LINQ needs each table should have primary key to Delete operation.that is , assumption of my query is, ID is primary key of Master table and child table has ChildID field as primary key field.


hope this helps.
int MasterIDToDelete =2;
//select and delete all child records
var ChildsToDelete = from  child  in db.Childtable  
          join master in db.Mastertable on child.ID equals master.ID
		  where master.ID == MasterIDToDelete
		  select child ;
db.Childtable.DeleteAllOnSubmit(ChildsToDelete);
db.SubmitChanges();
// select single master record and delete
var MasterToDelete = (from master in db.Mastertable  
				where master.ID == MasterIDToDelete 
				select master).Single();
db.Mastertable.DeleteOnSubmit(MasterToDelete);
db.SubmitChanges();

Open in new window

0
 
roxviperCommented:
try to let only the code for deleting a single record and see if gives error
0
 
ToString1Author Commented:
Hi the code posted by roxviper does delete the "many" table records but not the master.

It is to do with being attached to two different data contexts
0
 
roxviperCommented:
use one datacontext
0
 
MathiyazhaganCommented:
you can also directly delete child records filtered by child.ID = MasterIDToDelete and no need of join also...
int MasterIDToDelete =2;
//select and delete all child records
var ChildsToDelete = from  child  in db.Childtable  
          	  where child.ID == MasterIDToDelete
		  select child ;
db.Childtable.DeleteAllOnSubmit(ChildsToDelete);
db.SubmitChanges();
// select single master record and delete
var MasterToDelete = (from master in db.Mastertable  
				where master.ID == MasterIDToDelete 
				select master).Single();
db.Mastertable.DeleteOnSubmit(MasterToDelete);
db.SubmitChanges();

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now