How to delete from bridge table using LINQ in C#

Question

0.00/5 (No votes)

See more:

I have 3 tables in my database. Users , Roles and a bridge table UserRoles that has composite PK in it. Now what I want is while deleting the entry from other linked tables I want to delete reference entries from the bridge table as well.

public async Task<IHttpActionResult> DeleteUser(string id)
       {
           try {
               using (hospiceEntities db = new hospiceEntities())
               {
                   Guid guid_id = Guid.Parse(id);
                   var deleteuser = db.Users.FirstOrDefault(e => e.Id == guid_id);
                   if (deleteuser == null)
                   {
                       return Content(HttpStatusCode.NotFound, "User Not Found");
                   }
                   else
                   {

                       var q =
                   from user in db.Users
                   join resource in db.Resources
                   on user.Id equals resource.UserId
                   from role in user.Roles
                   where user.Id == guid_id
                   select new
                   {
                       Users = user,
                       Resources = resource,
                       Roles = role,
                   };
                       foreach (var item in q)
                       {
                           db.Resources.Remove(item.Resources);
                           db.Users.Remove(item.Users);
                           db.Roles.Remove(item.Roles);
                       }
                       await db.SaveChangesAsync();
                   }
               }
               return Ok(Helper.SuccessResponse("User Deleted"));
           }
           catch (Exception ex)
           {
               return BadRequest(ex.Message);
           }
       }

but it is giving me error: "The DELETE statement conflicted with the REFERENCE constraint"
what i have to do make it work please help.

What I have tried:

<pre>  var q =
from user in db.Users
join resource in db.Resources
on user.Id equals resource.UserId
where user.Id == guid_id
select new
{
    Users = user,
    Resources = resource
};
    foreach (var item in q)
    {
        db.Resources.Remove(item.Resources);
        db.Users.Remove(item.Users);
     
    }
    await db.SaveChangesAsync();
}

My above query works fine and deletes the records from Users and Resources table that are linked through userId but when i add the following query to remove the reference entries from my bridge table it is giving me error. Here is my query to delete from bridge table

var q =
                   from user in db.Users
                   join resource in db.Resources
                   on user.Id equals resource.UserId
                   from role in user.Roles
                   where user.Id == guid_id
                   select new
                   {
                       Users = user,
                       Resources = resource,
                       Roles = role
                   };
                       foreach (var item in q)
                       {
                           db.Resources.Remove(item.Resources);
                           db.Users.Remove(item.Users);
                           db.Roles.Remove(item.Roles);
                       }

Posted 11-Jan-22 1:20am

Sadia Rashid Jan2022

Updated 17-Jan-22 23:12pm

v2

Add a Solution

2 solutions

Solution 1

The error message "The DELETE statement conflicted with the REFERENCE constraint" means exactly what it says: there is data in another table which references data in one or more of teh rows being deleted. Since this would break referential integrity, the delete operation cannot proceed.

There is no way to "make that work": SQL will not allow - even temporarily - invalid data to be stored.
You will have to ensure that dependant data is deleted before the data it references, not the other way round. Check for a FORIEGN KEY in your other tables that links back to this one.

Posted 11-Jan-22 1:26am

OriginalGriff

Comments

Sadia Rashid Jan2022 11-Jan-22 8:39am

But the join in my query is already deleting the entries from the linked tables can u guide what else i need to do here to get rid of this error? also i have updated my question please check

_Asif_ 11-Jan-22 9:35am

DELETE CASCADE is the alternative way to delete child table tuples as well if parent tuple gets deleted although not recommended at all in almost all scenarios.

Check this link: https://www.sqlshack.com/delete-cascade-and-update-cascade-in-sql-server-foreign-key/

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sadia Rashid Jan2022 · Accepted Answer · 2022-01-17T23:12:00

I solved it using this approach. if it can be of any help to anyone. Thanks :)

public async Task<IHttpActionResult> DeleteUser(string id)
       {
           try {
               using (hospiceEntities db = new hospiceEntities())
               {
                   Guid guid_id = Guid.Parse(id);
                   var deleteuser = db.Users.FirstOrDefault(e => e.Id == guid_id);
                   if (deleteuser == null)
                   {
                       return Content(HttpStatusCode.NotFound, "User Not Found");
                   }
                   else
                   {
                       var q =
                   from user in db.Users
                   join resource in db.Resources
                   on user.Id equals resource.UserId
                   from role in user.Roles
                   from ofc in user.Offices
                   where user.Id == guid_id
                   select new
                   {
                       Users = user,
                       Resources = resource,
                       Roles = role,
                       Offices = ofc
                   };
                       foreach (var item in q)
                       {
                           deleteuser.Roles.Remove(item.Roles);
                           deleteuser.Offices.Remove(item.Offices);
                           db.Resources.Remove(item.Resources);
                           db.Users.Remove(item.Users);
                       }
                       await db.SaveChangesAsync();
                   }
               }
               return Ok(Helper.SuccessResponse("User Deleted"));
           }
           catch (Exception ex)
           {
               return BadRequest(ex.Message);
           }
       }