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

asp.net-mvc c# entity-framework-6 linq

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?

1
-1
6/12/2018 5:34:41 PM

Accepted Answer

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);
    }                   
}
1
6/13/2018 1:46:58 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow