The Investigation
Entity Framework Core simplifies working with databases for .NET developers. Instead of creating tables and stored procedures manually, EF creates tables automatically from classes created in C# and generates queries to fetch the data and update it. EF is known as an object relational mapper (ORM). What is that? Well, C# is an object-oriented language and SQL Server is a relational database, EF is the bridge or translator, if you will. But is it actually possible to create classes in C# with inheritance and have EF magically translate it into a relational database? The DotNet Detective investigates.
To investigate, we create a project with some simple classes using .NET/EF Core 8. At the root is a User class, the children are Regular User, Premium User, and Admin User. Regular User also has a child class: Short Term User. It looks like this:
We also need a Database Context with a connection string. In this case, we are connecting to SQL Server. Finally, we add a bit of code to create one of each kind of user.
After we create our first migration and run the
project to populate the data, we can see the result, a single table that looks
like this:
As we can see, all classes have been combined into one table
and a Discriminator column has been added to distinguish them. While this solution is
functional, it is also pretty horrific database design. Not much relational going on
here and all those NULLs!
The Discovery
Ideally, we would see one table for each class. In the case
of inheritance, a JOIN query would combine the data from the Parent class with
the Child class.
In order to achieve this, we need to add an event handler
for the OnModelCreating event. And here we specify each table to be created:
protected override
void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>().ToTable("Users");
modelBuilder.Entity<RegularUser>().ToTable("RegularUsers");
modelBuilder.Entity<PremiumUser>().ToTable("PremiumUsers");
modelBuilder.Entity<AdminUser>().ToTable("AdminUsers");
modelBuilder.Entity<ShortTermUser>().ToTable("ShortTermUsers");
}
Well, look at that. That’s more like it. But what about the
query? Let’s retrieve a Short Term User and see if the query joins all three
tables in the inheritance chain:
ShortTermUser stUser = context.ShortTermUsers.FirstOrDefault();
And the output window shows us the query:
SELECT
TOP(1) [u].[Id], [u].[CreatedDate], [u].[Email], [u].[Retired],
[r].[ItemLimit], [s].[ExpirationDate]
FROM
[Users] AS [u]
INNER
JOIN [RegularUsers] AS [r] ON [u].[Id] = [r].[Id]
INNER JOIN [ShortTermUsers] AS [s] ON [u].[Id] = [s].[Id]
The Arresting Conclusion
Entity Framework Core can indeed be used as a bridge between
object-oriented code and relational data stores. Note that this feature is not
available in EF 5 and earlier.
To learn more, follow these links:
https://learn.microsoft.com/en-us/ef/core/modeling/inheritance
https://learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/inheritance?view=aspnetcore-8.0
