• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 935
  • 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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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