How would one turn the enums used in an EF Core database context into lookup tables and add the relevant foreign keys?
You can use an enum in your code and have a lookup table in your db by using a combination of these two EF Core features:
Here below a data model example:
public class Wine
{
public int WineId { get; set; }
public string Name { get; set; }
public WineVariantId WineVariantId { get; set; }
public WineVariant WineVariant { get; set; }
}
public enum WineVariantId : int
{
Red = 0,
White = 1,
Rose = 2
}
public class WineVariant
{
public WineVariantId WineVariantId { get; set; }
public string Name { get; set; }
public List<Wine> Wines { get; set; }
}
Here the DbContext where you configure value conversions and data seeding:
public class WineContext : DbContext
{
public DbSet<Wine> Wines { get; set; }
public DbSet<WineVariant> WineVariants { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Data Source=wines.db");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<Wine>()
.Property(e => e.WineVariantId)
.HasConversion<int>();
modelBuilder
.Entity<WineVariant>()
.Property(e => e.WineVariantId)
.HasConversion<int>();
modelBuilder
.Entity<WineVariant>().HasData(
Enum.GetValues(typeof(WineVariantId))
.Cast<WineVariantId>()
.Select(e => new WineVariant()
{
WineVariantId = e,
Name = e.ToString()
})
);
}
}
Then you can use the enum values in your code as follow:
db.Wines.Add(new Wine
{
Name = "Gutturnio",
WineVariantId = WineVariantId.Red,
});
db.Wines.Add(new Wine
{
Name = "Ortrugo",
WineVariantId = WineVariantId.White,
});
Here is what your db will contain:
I published the complete example as a gist: https://gist.github.com/paolofulgoni/825bef5cd6cd92c4f9bbf33f603af4ff
Here is another example :
public class Weather {
public int Id { get; init; }
public WeatherType Type { get; init; }
}
public enum WeatherType {
Cloudy = 1,
Sunny = 2,
Rainy = 3,
}
And you can add HasConversion in a seperate class like this :
public class WeatherEntityTypeConfiguration : IEntityTypeConfiguration<Weather>
{
public void Configure(EntityTypeBuilder<Weather> builder)
{
builder.ToTable("Weather").HasKey(k => k.Id);
builder.Property(p => p.Id).IsRequired();
builder.Property(p => p.Type).HasConversion<int>().IsRequired();
// builder.Property(p => p.Type).HasConversion<string>().IsRequired();
}
}
Note : If you use HasConversion<int>() data would be stored in database as an integer but if you use HasConversion<string>() data would be stored as string (in this example : Cloudy, Sunny or Rainy )
In addition to @PaoloFulgoni, here is how you'd do it if you want a many-to-many relationship with enums i.e. you want many user roles or wine variants and work with enums moreover, you can't store it as a flag because you need to know about the roles/privileges without source code(on db side).
TLDR ;) You'd have to create a join table which contains about about who has what privilege(or roles if you want).
There is a Users table which has a list of privileges, a privilege table which has privilege definition i.e. Id, name. And a Join table which will have User and Privilege as it's key. If an entry against this user/privilege combination is present that means this user has this privilege/role.
The code:
//for enum
public enum UserPrivilegeId : int
{
AddProject = 0,
ModifyProject = 1,
DeleteProject = 2,
AddUser = 3,
ModifyUser = 4,
DeleteUser = 5
}
//User class
public record User
{
public User()
{
Privileges = new HashSet<Privilege>();
}
public int Id { get; set; }
public string Username { get; set; }
public string PasswordHash { get; set; }
public virtual ICollection<Privilege> Privileges { get; set; }
public virtual List<UserPrivilege> UserPrivileges { get; set; }
}
//Privilege Class
public record Privilege //note record is IMPORTANT here, because this forces it to compare by value, if you want to *use a class*, then make sure to override GetHashCode and Equals
{
public Privilege()
{
Users = new HashSet<User>();
}
public Privilege(UserPrivilegeId privilegeId, string privilegeName)
{
PrivilegeId = privilegeId;
PrivilegeName = privilegeName;
Users = new HashSet<User>();
}
[Key]
public UserPrivilegeId PrivilegeId { get; set; }
public string PrivilegeName { get; set; }
public virtual ICollection<User> Users { get; set; }
public virtual List<UserPrivilege> UserPrivileges { get; set; }
}
//and finally the UserPrivilege join class
public record UserPrivilege
{
public UserPrivilegeId PrivilageId { get; set; }
public Privilege Privilage { get; set; }
public int UserId { get; set; }
public User User { get; set; }
}
//The set-up in dbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Privilege>()
.HasKey(p => p.PrivilegeId);
modelBuilder.Entity<Privilege>()
.Property(p => p.PrivilegeId)
.HasConversion<int>();
modelBuilder.Entity<User>()
.HasMany(user => user.Privileges)
.WithMany(privilege => privilege.Users)
.UsingEntity<UserPrivilege>(
j => j
.HasOne(up => up.Privilage)
.WithMany(u => u.UserPrivileges)
.HasForeignKey(up => up.PrivilageId),
j => j
.HasOne(up => up.User)
.WithMany(p => p.UserPrivileges)
.HasForeignKey(up => up.UserId),
j =>
{
j.Property(u => u.PrivilageId).HasConversion<int>();
j.HasKey(u => new { u.PrivilageId, u.UserId });
});
//this adds definitions of privileges to the table
modelBuilder.Entity<Privilege>()
.HasData(
Enum.GetValues(typeof(UserPrivilegeId))
.Cast<UserPrivilegeId>()
.Select(p => new Privilege(p, p.ToString())));
base.OnModelCreating(modelBuilder);
}
Use it by creating a wrapper around it with a boolean on IsActive like this:
public class UserPrivelegesDTO
{
public UserPrivelegesDTO(UserPrivilegeId privilege, bool isActive)
{
this.PrivilegeId = privilege;
this.PrivilegeName = privilege.ToString();
this.IsActive = isActive;
}
public UserPrivilegeId PrivilegeId { get; set; }
public string PrivilegeName { get; set; }
public bool IsActive { get; set; }
}
If you want to convert from List<Privileges> to List<UserPrivilegeDTO>, you can
return await _context.Privileges.OrderBy(x => x.PrivilegeId).ToListAsync(cancellationToken);
To Convert back to List<Privileges>, simply
var privileges = _userPrivilegesViewModel.Privileges.Where(x => x.IsActive).Select(x => new Privilege(x.PrivilegeId, x.PrivilegeName));
If you want to check if the user has privilege
var user = _context.Users.Include(x => x.Privileges).FirstAsync(x => x.Id == 1);
if (request.Editor.Privileges.Any(p => p.PrivilegeId == UserPrivilegeId.ModifyUser))
return true;
When you want to update privileges
var PrivilegeChangeUser = await
_context.Users
.Include(user => user.Privileges)
.Include(user => user.UserPrivileges)
.FirstOrDefaultAsync(user => user.Id == request.UserId);
//**NOTE**you *need* to include the join table i.e. UserPrivileges in order replace the privileges, if you do not include it EF will try to add the privileges which already exist :(
//To update the privileges from an IEnumerable<UserPrivilegeIdEnum>
//first get the privileges objects and add that to users
var AllPrivileges =
await _context.Privileges
.Include(x => x.UserPrivileges)
.Include(x => x.Users)
.Where(x =>
request.Privileges
.Contains(x.PrivilegeId)
).ToListAsync(cancellationToken);
PrivilegeChangeUser.Privileges = AllPrivileges;
