Extending Your Data Model and Model Configuration
Your application will grow in complexity, and with that, it will be necessary to add other entities to your context or change existing ones.
Take our EmployeeBenefits class for instance:
public class EmployeeBenefits
{
public int Id { get; set; }
public int EmployeeId { get; set; }
public BenefitType BenefitType { get; set; }
public decimal Cost { get; set; }
public Employee Employee { get; set; } = null!;
}
I can think of about a dozen different ways we could change this. In a real payroll system, a benefit would probably be a separate table with yet another table to "junction" the employee and their benefits.
Let's wholesale rip it out.
"Sheesh really is that necessary?"
There are a ton of times where abstractions that fit your current needs work, until they don't. And they don't serve the course anymore to keep things simple ;)
So let's remove it, as well as the BenefitType enum - that abstraction is being promoted to a full entity.
public class Benefit
{
public int Id { get; set; }
public required string Name { get; set; }
public required string Description { get; set; }
public decimal BaseCost { get; set; }
}
public class EmployeeBenefit
{
public int Id { get; set; }
public int EmployeeId { get; set; }
public Employee Employee { get; set; } = null!;
public int BenefitId { get; set; }
public Benefit Benefit { get; set; } = null!;
public decimal? CostToEmployee { get; set; }
}
All of our models have an
Idproperty to uniquely identify the records within the database. This is super common in databases.
We'll also comment out the EmployeeBenefits property in the Employee class and the GetEmployeeResponse class. Basically, anywhere where our Problems window/dotnet build command takes us. There's more than just the line below, so let your IDE guide your way.
//public List<EmployeeBenefit> Benefits { get; set; } = new List<EmployeeBenefit>();
Run our tests and see they're all passing.
Now let's add a migration to remove that old table.
dotnet ef migrations add RemoveOldBenefitsTable
Review the migration for accuracy.
🌶️🌶️🌶️ I never trust migration files without reviewing them first.
In this case, it's pretty straightforward - drops the EmployeeBenefits table.
🌶️🌶️🌶️ In pretty much all cases where you're making major changes to your data model, there's a whole host of other things you need to do - migrate the old date, test test test, etc. We can skip all that in this course, but know that dropping a table is usually considered a Pretty Big Deal™️.
Okay - now we can add our new entities to the context:
public class AppDbContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
public DbSet<Benefit> Benefits { get; set; }
public DbSet<EmployeeBenefit> EmployeeBenefits { get; set; }
}
But we're not ready for a migration yet - let's focus in on the EmployeeBenefit entity for a moment and think about what we want it to represent.
Ideally, it would be a many-to-many relationship between employees and benefits that denotes a unique relationship between an employee and a benefit. (Obviously benefits in real life are WAY more complex than this - but let's assume that, for the sake of this system, an employee and a benefit can have exactly 0 or 1 records.)
We should create what's called a "unique constraint" on the EmployeeId and BenefitId columns. This will ensure that we don't have duplicate relationships in our database.
Override the OnModelCreating method on AppDbContext to configure the EmployeeBenefit entity.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<EmployeeBenefit>()
.HasIndex(eb => new { eb.EmployeeId, eb.BenefitId })
.IsUnique();
}
I should also note a couple of things about this - mainly that we chose to make this a unique index, not just the single key, which we absolutely could have done.
- I find that it's very useful for even junction table records to have a unique identifier - thus I keep the
Idproperty there. - Indexes have a host of other benefits, including performance benefits when querying.
This is more of a database design concern, but a valid one in the context of creating web apps. SQL skills are highly undervalued by developers - stand out from the crowd and endeavor to learn as much about SQL as you can.
🌶️🌶️🌶️ I've been able to differentiate myself from other developers in two ways: constantly, paranoidly checking for null AND understanding the strengths, weaknesses, and capabilities of SQL as a language and as a data store. While I'd love to teach you SQL goodness in this course, it's simply outside of the scope!
More on OnModelCreating
The OnModelCreating method is a powerful tool for configuring your data model. It's a way to tell the database what you want to do, and it's a way to tell Entity Framework Core how to create the database.
You can use it to configure:
- Indexes
- Foreign Keys
- Relationships
Examples of each:
modelBuilder.Entity<Employee>()
.HasMany(e => e.Benefits)
.WithOne(eb => eb.Employee)
.HasForeignKey(eb => eb.EmployeeId);
modelBuilder.Entity<EmployeeBenefit>()
.HasKey(eb => new { eb.EmployeeId, eb.BenefitId })
.HasName("PK_EmployeeBenefit");
Most of the time, I let EF Core do this for me, but it's good to know that you have this power.
It can even manipulate column data types, which is useful when you want to model your C# classes a certain way and your database another.
modelBuilder.Entity<Employee>()
.Property(e => e.FirstName)
.HasColumnType("varchar(100)"); //this is for SQL Server, which has variable length string types
We're going to keep it simple for now, and just add a migration to update our database.
dotnet ef migrations add AddBenefitsAndEmployeeBenefits
Review the migration for accuracy.
Then, we'll update our MigrateAndSeed method to create some benefits and employee benefits.
public static void MigrateAndSeed(IServiceProvider serviceProvider)
{
var context = serviceProvider.GetRequiredService<AppDbContext>();
context.Database.Migrate();
if (!context.Employees.Any())
{
var employees = new List<Employee>
{
new Employee
{
FirstName = "John",
LastName = "Doe",
SocialSecurityNumber = "123-45-6789",
Address1 = "123 Main St",
City = "Anytown",
State = "NY",
ZipCode = "12345",
PhoneNumber = "555-123-4567",
Email = "john.doe@example.com"
},
new Employee
{
FirstName = "Jane",
LastName = "Smith",
SocialSecurityNumber = "987-65-4321",
Address1 = "456 Elm St",
Address2 = "Apt 2B",
City = "Othertown",
State = "CA",
ZipCode = "98765",
PhoneNumber = "555-987-6543",
Email = "jane.smith@example.com"
}
};
context.Employees.AddRange(employees);
context.SaveChanges();
}
if (!context.Benefits.Any())
{
var benefits = new List<Benefit>
{
new Benefit { Name = "Health", Description = "Medical, dental, and vision coverage", BaseCost = 100.00m },
new Benefit { Name = "Dental", Description = "Dental coverage", BaseCost = 50.00m },
new Benefit { Name = "Vision", Description = "Vision coverage", BaseCost = 30.00m }
};
context.Benefits.AddRange(benefits);
context.SaveChanges();
//add employee benefits too
var healthBenefit = context.Benefits.Single(b => b.Name == "Health");
var dentalBenefit = context.Benefits.Single(b => b.Name == "Dental");
var visionBenefit = context.Benefits.Single(b => b.Name == "Vision");
var john = context.Employees.Single(e => e.FirstName == "John");
john.Benefits = new List<EmployeeBenefit>
{
new EmployeeBenefit { Benefit = healthBenefit, CostToEmployee = 100m},
new EmployeeBenefit { Benefit = dentalBenefit }
};
var jane = context.Employees.Single(e => e.FirstName == "Jane");
jane.Benefits = new List<EmployeeBenefit>
{
new EmployeeBenefit { Benefit = healthBenefit, CostToEmployee = 120m},
new EmployeeBenefit { Benefit = visionBenefit }
};
context.SaveChanges();
}
}
Whew, almost done. Let's uncomment out our EmployeeBenefits endpoint and all of our other commenty-outy things we did.
/// <summary>
/// Gets the benefits for an employee.
/// </summary>
/// <param name="employeeId">The ID to get the benefits for.</param>
/// <returns>The benefits for that employee.</returns>
[HttpGet("{employeeId}/benefits")]
[ProducesResponseType(typeof(IEnumerable<GetEmployeeResponseEmployeeBenefit>), StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public async Task<IActionResult> GetBenefitsForEmployee(int employeeId)
{
var employee = await _dbContext.Employees
.Include(e => e.Benefits)
.ThenInclude(e => e.Benefit)
.SingleOrDefaultAsync(e => e.Id == employeeId);
if (employee == null)
{
return NotFound();
}
var benefits = employee.Benefits.Select(b => new GetEmployeeResponseEmployeeBenefit
{
Id = b.Id,
Name = b.Benefit.Name,
Description = b.Benefit.Description,
Cost = b.CostToEmployee ?? b.Benefit.BaseCost //we want to use the cost to employee if it exists, otherwise we want to use the base cost
});
return Ok(benefits);
}
And of course change our GetEmployeeResponseEmployeeBenefit class to match the new model.
public class GetEmployeeResponseEmployeeBenefit
{
public int Id { get; set; }
public required string Name { get; set; }
public required string Description { get; set; }
public decimal Cost { get; set; }
}
Uncomment the tests as well. Everything should be looking good! A couple of notes:
- We opted to stop returning bennies in the Employee gets mainly for convenience in this refactor. Sometimes you'll keep things like that, sometimes you won't. It really just ultimately depends on what makes sense for your application. The point of me bringing it up is to say, "hey, this is a decision you can make at some point - think about the implications."
- The
ThenIncludecall should be noted, next to the call toIncludes. It's a useful way of instructing EF Core to make sure to include that entity in its query to the database. Try commenting it out, running the tests, and see what happens! - There was a lot of database-y stuff we can't cover here, so know that you should respect the data source from which you are making your queries.
Solution
The 4-H-reviewing-everything-weve-done directory contains the final code up to this point.