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);
}