To delete a row from a table of only foreign keys using Entity Framework

Question

I have a table User { UserId, Name, Age } and License { LicenseId, Name, IsActive }. Also I have a table UserLicense { UserId, LicenseId } which connects both of these and has the record for all the users who hold a license. A user can hold multiple license and those appear as different rows in the UserLicense table.

Now I need to remove a particular license from the license table. I do not want to delete it outright, so I mark IsActive = false. However I do want to delete rows from the UserLicense table for that license Id.

I am using Entity Framework.

If it is a direct table, I would have done something like :

var lic = db.Licenses.Where(l => l.Id== licenseId).FirstorDefault();
db.Licenses.Remove(lic);
db.SaveChanges();

However since UserLicense is a table of foreign keys, Entity Framework does not allow me to access it directly using

public void DeleteLicensedUsers(Guid LicenseId)
{
    db.UserLicenses.Where()
}

because the model does not contain an independent table for UserLicense as it is only a table of foreign keys.

So how do I delete all rows of UserLicense table for a particular licenseId using Linq and EF6?

Add `DbSet UserLicenses { set;get;}` to your DbContext class and then use it ? — Shyju, Jun 12 '18 at 17:16
Possible duplicate of [Entity Framework - Remove object with foreign key, preserving parent](https://stackoverflow.com/questions/22858491/entity-framework-remove-object-with-foreign-key-preserving-parent) — Daniel Frykman, Jun 12 '18 at 17:23
@Shyju no need for that btw, you can use context.Set() if you hold this type — Krzysztof Skowronek, Jun 12 '18 at 17:40

score 3 · Answer 1 · answered Jun 12 '18 at 17:20

If your entity class (I guess User), has navigation properties of type ICollection<Licence> by removing Licence from this collection you are actually removing items from UserLicense table. This is how Entity Framework handles many-to-many relationships in databases.

score 1 · Accepted Answer · answered Jun 13 '18 at 13:46

I faced a similar issue and you can solve it this way. I almost tend to avoid tables with just foreign keys and instead add another column to fix this issue.

var license = db.Licenses.Where(l => l.ID == LicenseId).FirstOrDefault();
var users = license.Users.ToList();


if (users != null)
{
    foreach (var user in users)
    {
         license.Users.Remove(user);
    }                   
}

farzaaaan · Answer 3 · 2018-06-12T17:41:25.950

0

I'm a little confused by your question. So if you mark a license as isActive = false, and still want to see that license appear on user's page, then there should still a record in UserLicense table associating the two.

That said, I believe this answer from a similar question will help you.

so essentially:

//include licenses in the user entity
var result = ctx.User.Include(x=>x.Licenses).Where(t=>t.Id == _id).FirstOrDefault()
//get the licenses you are looking for, either equal or use .Contains
var licensesOfUser = result.Where(x=>x.Licenses.Where(l=>l.id == _licenseId);
//remove them from entity and save.
result.Licenses.RemoveRange(licenseOfUser);
ctx.SaveChanges();

[edit: if you have license]

//include users in license entities  
var result = ctx.License.Include(x=>x.Users).Where(t=>t.Id == _id).FirstOrDefault()
//get the users you are looking for, either equal or use .Contains
var usersOfLicenses = result.Where(x=>x.Users.Where(l=>l.id == _userId);
//remove them from entity and save.
result.Users.RemoveRange(licenseOfUser);
ctx.SaveChanges();

edited Jun 12 '18 at 17:41

answered Jun 12 '18 at 17:26

farzaaaan

410
3
9

When I mark license as isActive = false, I do not want it to appear on User's page. So i indeed want to delete the record in UserLicense table associating the two but do nothing to either the user or the license table. – TheFallenOne Jun 12 '18 at 17:29
oh ok, I understood that wrong, that said the answer linked and the code sample will do exactly what you want . – farzaaaan Jun 12 '18 at 17:30
.Where(t=>t.Id == _id) this portion does not work as I do not have the user Id but have the license Id. – TheFallenOne Jun 12 '18 at 17:35
then do it the other way, I'll edit my answer [edit]: I've editted so now you can find users associated with a license instead of licenses associated with a your. – farzaaaan Jun 12 '18 at 17:39
var usersOfLicenses = result.Where(x=>x.Users.Where(l=>l.id == _userId); This line has an issue. Firstly results.where does not work and there is no _userId in this context. – TheFallenOne Jun 12 '18 at 17:46

To delete a row from a table of only foreign keys using Entity Framework

3 Answers3