Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

c# Linq statement needed to delete records from 2 tables

Posted on 2010-11-16
10
923 Views
Last Modified: 2013-12-17


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
Comment
Question by:ToString1
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 142 total points
ID: 34144322
Can you modify the table schema? Because this sort of thing would be better implemented as a cascade delete rule on your tables.
0
 
LVL 5

Assisted Solution

by:roxviper
roxviper earned 215 total points
ID: 34144386
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
 

Author Comment

by:ToString1
ID: 34144407
OK thanks

Yes I already have a cascade delete rule in my database schema but have not written any code yet
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 142 total points
ID: 34144431
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
 

Author Comment

by:ToString1
ID: 34144482
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
 
LVL 10

Assisted Solution

by:Mathiyazhagan
Mathiyazhagan earned 143 total points
ID: 34144483
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
 
LVL 5

Accepted Solution

by:
roxviper earned 215 total points
ID: 34144501
try to let only the code for deleting a single record and see if gives error
0
 

Author Comment

by:ToString1
ID: 34144673
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
 
LVL 5

Assisted Solution

by:roxviper
roxviper earned 215 total points
ID: 34144698
use one datacontext
0
 
LVL 10

Assisted Solution

by:Mathiyazhagan
Mathiyazhagan earned 143 total points
ID: 34152324
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question