Previous month:
April 2018
Next month:
June 2018

May 2018

Entity Framework Core: Inheritance - Table-per-Type (TPT) is not supported, is it? (Part 2 - Database First)

In the previous post we have created 2 Entity Framework Core (EF Core) models with a code first approach. One model was using the Table-per-Hierarchy (TPH) pattern and the other one Table-per-Type (TPT). In this post we want to approach a more common scenario we see in customer projects: we are using the database first approach now.

All demos are on Github.

Business data model

The business data model is the same as in the previous post. We have 3 DTOs: Person, Customer and Employee.

public class PersonDto
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class CustomerDto : PersonDto
{
    public DateTime DateOfBirth { get; set; }
}

public class EmployeeDto : PersonDto
{
    public decimal Turnover { get; set; }
}

Table-per-Hierarchy (TPH)

We start with the Table-per-Hierarchy pattern. Given is a table People containing all columns from all DTOs incl. 1 column Discriminator to be able to distinguish the customers from employees.

Remark: we are using nvarchar(max) for the sake of simplicity.

TABLE People
(
    Id uniqueidentifier NOT NULL PRIMARY KEY,
    FirstName nvarchar(max) NULL,
    LastName nvarchar(max) NULL,
    DateOfBirth datetime2(7) NULL,
    Turnover decimal(18, 2) NULL,
    Discriminator nvarchar(max) NOT NULL
)

With the following command we let EF Core scaffold the entities (or rather the entity) and the database context:

dotnet ef dbcontext scaffold "Server=(local);Database=TphDemo;Trusted_Connection=True" Microsoft.EntityFrameworkCore.SqlServer -f -c ScaffoldedTphDbContext --context-dir ./TphModel/DatabaseFirst -o ./TphModel/DatabaseFirst -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj

The result is not the one we might have expected but is pretty reasonable. The scaffolding creates just 1 entity People with all fields in it because there is no way for EF Core to guess that the table contains 3 entities and not just 1.

public class People
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime? DateOfBirth { get; set; }
public decimal? Turnover { get; set; }
public string Discriminator { get; set; }
}

First, let's fix the name of the entity because the name should be Person not People.

For that we create a class that does the pluralization/singularization and register it with the so-called IDesignTimeServices. The implementation of IDesignTimeServices doesn't need any kind of registration, EF Core will find it automatically. The actual pluralization/singularization will be done by the 3rd party-library Inflector.

public class Pluralizer : IPluralizer
{
    public string Pluralize(string identifier)
    {
        // Inflector needs some help with "People" otherwise we get "Peoples"
        if (identifier == "People")
            return identifier;

        return Inflector.Inflector.Pluralize(identifier);
}

    public string Singularize(string identifier)
    {
        return Inflector.Inflector.Singularize(identifier);
    }
}

public class DesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<IPluralizer, Pluralizer>();
    }
}

Now, the generated entity gets the name Person - but to make the model right we have to split the class in 3, manually. After manual adjustments we have 2 options: switch to code first approach or adjust the classes manually after every scaffolding to apply the changes from database. The adjusted code is virtually identical to the one of code first approach but this time the Descriminator is defined explicitly.

Remark: I've renamed Person to PersonTph so the names are the same as in the previous blog post.

public class PersonTph
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Discriminator { get; set; }
}
public class CustomerTph : PersonTph
{
public DateTime DateOfBirth { get; set; }
}
public class EmployeeTph : PersonTph
{
public decimal Turnover { get; set; }
}

The generated database context needs some adjustments as well because DbSets for customers and employees are missing and the field Discriminator has to be defined as one.

public partial class ScaffoldedTphDbContext : DbContext
{
    public virtual DbSet<Person> People { get; set; }

    public ScaffoldedTphDbContext(DbContextOptions<ScaffoldedTphDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>(entity =>
{
entity.Property(e => e.Id).ValueGeneratedNever();
entity.Property(e => e.Discriminator).IsRequired();
});
    }
}

As with the entities, the only change - compared to code first approach - is the explicit definition of the Discriminator.

public class ScaffoldedTphDbContext : DbContext
{
public virtual DbSet<PersonTph> People { get; set; }
public virtual DbSet<CustomerTph> Customers { get; set; }
public virtual DbSet<EmployeeTph> Employees { get; set; }

public ScaffoldedTphDbContext(DbContextOptions<ScaffoldedTphDbContext> options)
: base(options)
{
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<PersonTph>(entity => entity.Property(e => e.Id).ValueGeneratedNever());
modelBuilder.Entity<PersonTph>()
.HasDiscriminator(person => person.Discriminator)
.HasValue<PersonTph>(nameof(PersonTph))
.HasValue<CustomerTph>(nameof(CustomerTph))
.HasValue<EmployeeTph>(nameof(EmployeeTph));
}
}

Table-per-Type (TPT)

Having a database using the TPT pattern we start off 3 with tables:

TABLE People
(
    Id uniqueidentifier NOT NULL PRIMARY KEY,
    FirstName nvarchar(max) NULL,
    LastName nvarchar(max) NULL
)
TABLE Customers
(
    Id uniqueidentifier NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES People (Id),
    DateOfBirth datetime2(7) NOT NULL
)
TABLE Employees
(
    Id uniqueidentifier NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES People (Id),
    Turnover [decimal](18, 2) NOT NULL
)

With the following command we create the entities and the database context:

dotnet ef dbcontext scaffold "Server=(local);Database=TptDemo;Trusted_Connection=True" Microsoft.EntityFrameworkCore.SqlServer -f -c ScaffoldedTptDbContext --context-dir ./TptModel/DatabaseFirst -o ./TptModel/DatabaseFirst -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj

The scaffolder generates 3 entities that are almost correct. The only flaw is the name of the navigational property IdNavigation pointing to the base class Person.

public partial class Person
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Customer Customer { get; set; }
    public Employee Employee { get; set; }
}
public partial class Employee
{
    public Guid Id { get; set; }
    public decimal Turnover { get; set; }
    public Person IdNavigation { get; set; }
}
public partial class Customer
{
    public Guid Id { get; set; }
    public DateTime DateOfBirth { get; set; }
    public Person IdNavigation { get; set; }
}

Luckily, this issue is very easy to fix by implementing ICandidateNamingService and registering it with IDesignTimeServices.

public class CustomCandidateNamingService : CandidateNamingService
{
    public override string GetDependentEndCandidateNavigationPropertyName(IForeignKey foreignKey)
    {
     if(foreignKey.PrincipalKey.IsPrimaryKey())
            return foreignKey.PrincipalEntityType.ShortName();

        return base.GetDependentEndCandidateNavigationPropertyName(foreignKey);
}
}

public class DesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<IPluralizer, Pluralizer>()
            .AddSingleton<ICandidateNamingService, CustomCandidateNamingService>();
    }
}

After re-running the scaffolder, we get the expected results:

public class Customer
{
public Guid Id { get; set; }
public DateTime DateOfBirth { get; set; }

public Person Person { get; set; }
}
public partial class Employee
{
public Guid Id { get; set; }
public decimal Turnover { get; set; }

public Person Person { get; set; }
}

The last part is the database context. Fortunately, we don't have to change anything.

public partial class ScaffoldedTptDbContext : DbContext
{
public virtual DbSet<Customer> Customers { get; set; }
public virtual DbSet<Employee> Employees { get; set; }
public virtual DbSet<Person> People { get; set; }

public ScaffoldedTptDbContext(DbContextOptions<ScaffoldedTptDbContext> options)
: base(options)
{
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Customer>(entity =>
{
entity.Property(e => e.Id).ValueGeneratedNever();

entity.HasOne(d => d.Person)
.WithOne(p => p.Customer)
.HasForeignKey<Customer>(d => d.Id);
});

modelBuilder.Entity<Employee>(entity =>
{
entity.Property(e => e.Id).ValueGeneratedNever();

entity.HasOne(d => d.Person)
.WithOne(p => p.Employee)
.HasForeignKey<Employee>(d => d.Id);
});

modelBuilder.Entity<Person>(entity =>
                        {
                         entity.Property(e => e.Id).ValueGeneratedNever());
                        });
}
}

With TPT we can but don't have to switch to code first approach because we can regenerate the entities and the database context at any time.

Conclusion

Database first approach works best with TPT, with TPH not so much because a relational database knows nothing about any inheritance. With TPT there is just one minor issue but thanks to the great job of the Entity Framework team we can adjust the code generation as we want without the need to copy all the code of Entity Framework Core.


Entity Framework Core: Inheritance - Table-per-Type (TPT) is not supported, is it? (Part 1 - Code First)

With O/R mappers there are a few patterns how a class hierarchy can be mapped to a relational database. The most popular ones are the Table-Per-Hierarchy (TPH) and the Table-Per-Type (TPT) patterns. The Entity Framework Core 2.x (EF Core) officially supports the Table-per-Hierarchy pattern only. The support of Table-per-Type is in the backlog of the Entity Framework team, i.e. it is not (officially) supported yet. Nevertheless, you can use TPT with the current version of EF Core. The usability is not ideal but acceptable. Especially, if you have an existing database using TPT then this short blog post series may give you an idea how to migrate to EF Core.

In the 1st part we will set up 2 EF Core models incl. database migrations for TPH and TPT using code first approach. In the 2nd part we are going to use the database first approach.

Remarks: this blog post is not about what approach is the best for your solution :)

All demos are on Github.

Business data model

In both cases we are going to use the following business data model. For our outward-facing interface, we are using DTOs. We have a PersonDto with 3 fields and 2 derived classes CustomerDto and EmployeeDto, both having 1 additional field.

public class PersonDto
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class CustomerDto : PersonDto
{
  public DateTime DateOfBirth { get; set; }
}

public class EmployeeDto : PersonDto
{
  public decimal Turnover { get; set; }
}

Table-Per-Hierarchy (TPH)

Now, let's look at the solution to have internal entities based on TPH. At first, we need to define the entity classes. Thanks to the native support of TPH and the very simple data model the entities are identical to the DTOs.

public class PersonTph
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class CustomerTph : PersonTph
{
public DateTime DateOfBirth { get; set; }
}
public class EmployeeTph : PersonTph
{
public decimal Turnover { get; set; }
}

We can implement the database context to be able to access customers and employees like this:

public class TphDbContext : DbContext
{
public DbSet<PersonTph> People { get; set; }
public DbSet<CustomerTph> Customers { get; set; }
public DbSet<EmployeeTph> Employees { get; set; }

public TphDbContext(DbContextOptions<TphDbContext> options)
: base(options)
{
}
}

And for the sake of completion we will be using Entity Framework Core Migrations to create and update the database schema. For that we execute the following command:

dotnet ef migrations add Initial_TPH_Migration -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj -c TphDbContext -o ./TphModel/CodeFirst/Migrations

As expected we have 1 table with all fields from person, customer and employee and 1 additional column Descriminator, so EF Core is able to differentiate customers from employees.

public partial class Initial_TPH_Migration : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable("People",
table => new
{
Id = table.Column<Guid>(nullable: false),
FirstName = table.Column<string>(nullable: true),
LastName = table.Column<string>(nullable: true),
DateOfBirth = table.Column<DateTime>(nullable: true),
Turnover = table.Column<decimal>(nullable: true),
Discriminator = table.Column<string>(nullable: false)
},
constraints: table => table.PrimaryKey("PK_People", x => x.Id));
}

protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("People");
}
}

The usage of TPH is nothing special, we just use the appropriate property on the TphDbContext.

TphDbContext ctx = ...

// Create a customer
ctx.Customers.Add(new CustomerTph()
{
Id = Guid.NewGuid(),
FirstName = "John",
LastName = "Foo",
DateOfBirth = new DateTime(1980, 1, 1)
});

// Fetch all customers
var customers = ctx.Customers
    .Select(c => new CustomerDto()
    {
         Id = c.Id,
         FirstName = c.FirstName,
         LastName = c.LastName,
         DateOfBirth = c.DateOfBirth
     })
    .ToList();

Table-Per-Type (TPT) 

Ok, that was easy. Now, how can a solution for TPT look like? With the absence of native support for TPT the entities do not derive from each other but reference each other. The field Id of customer and employee is the primary key and a foreign key pointing to person. The structure of the entities is very similar to the database schema of the TPT pattern.

public class PersonTpt
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class CustomerTpt
{
[ForeignKey(nameof(Person))]
public Guid Id { get; set; } // PK and FK pointing to PersonTpt
public PersonTpt Person { get; set; }

public DateTime DateOfBirth { get; set; }
}
public class EmployeeTpt
{
[ForeignKey(nameof(Person))]
public Guid Id { get; set; } // PK and FK pointing to PersonTpt
public PersonTpt Person { get; set; }

public decimal Turnover { get; set; }
}

The database context of TPT is identical to the one of TPH.

public class TptDbContext : DbContext
{
public DbSet<PersonTpt> People { get; set; }
public DbSet<CustomerTpt> Customers { get; set; }
public DbSet<EmployeeTpt> Employees { get; set; }

public TptDbContext(DbContextOptions<TptDbContext> options)
: base(options)
{
}
}

Next, we will create an EF Core migration with the following command

dotnet ef migrations add Initial_TPT_Migration -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj -c TptDbContext -o ./TptModel/CodeFirst/Migrations

The migration creates 3 tables with correct columns, primary keys and foreign keys.

public partial class Initial_TPT_Migration : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable("People",
                        table => new
{
Id = table.Column<Guid>(nullable: false),
FirstName = table.Column<string>(nullable: true),
LastName = table.Column<string>(nullable: true)
},
constraints: table => table.PrimaryKey("PK_People", x => x.Id));

migrationBuilder.CreateTable("Customers",
table => new
{
Id = table.Column<Guid>(nullable: false),
DateOfBirth = table.Column<DateTime>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Customers", x => x.Id);
table.ForeignKey("FK_Customers_People_Id",
                                                    x => x.Id,
                                                    "People",
                                                    "Id",
                                                    onDelete: ReferentialAction.Cascade);
});

migrationBuilder.CreateTable("Employees",
table => new
{
Id = table.Column<Guid>(nullable: false),
Turnover = table.Column<decimal>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Employees", x => x.Id);
table.ForeignKey("FK_Employees_People_Id",
                                                    x => x.Id,
                                                    "People",
                                                    "Id",
                                                    onDelete: ReferentialAction.Cascade);
});
}

protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("Customers");
migrationBuilder.DropTable("Employees");
migrationBuilder.DropTable("People");
}
}

The biggest difference - compared to TPH - is in the usage of the entities. To get to the fields of the person (i.e. the base type) we have to use the navigational property Person. This may seem cumbersome at first, but it is not a hindrance in practice.

TptDbContext ctx = ...

// Fetch all customers
var customers = ctx.Customers
.Select(c => new CustomerDto()
{
Id = c.Id,
FirstName = c.Person.FirstName,
LastName = c.Person.LastName,
DateOfBirth = c.DateOfBirth
})
.ToList();

// Create a customer
ctx.Customers.Add(new CustomerTpt()
        {
        Person = new PersonTpt()
           {
             Id = Guid.NewGuid(),
             FirstName = "John",
             LastName = "Foo"
             },
DateOfBirth = new DateTime(1980, 1, 1)
});

 Voila!

Conclusion

With Entity Framework Core we can use both the Table-Per-Hierarchy and Table-Per-Type patterns. At least with a code first approach. Whether and how the patterns are applicable using the database first approach we will see in the next blog post.

Stay tuned.


Entity Framework Core 2.1 Performance: Beware of N+1 Queries (Revisited)

In the previous post we have identified some Entity Framework (EF) LINQ queries that are affected by so called N+1 queries problem. In the meantime a new version (2.1-RC1) of Entity Framework has been released so we check the SQL statement generation yet another time.

Samples: Github-Repo 

Positive thing(s) first...

In the previous version the selection of a filtered collection was affected by the problem - with and without ToList() but not anymore

var groups = Context.ProductGroups
.Where(g => g.Name.Contains("Group"))
.Select(g => new
{
ProductGroup = g,
Products = g.Products.Where(p => p.Name.Contains("1")).ToList()
})
.ToList();

Adding ToList() leads to 2 SQL statements instead of N+1 where N is the number of selected product groups.

1 query for fetching of the product groups:

SELECT
    [g].[Id], [g].[Name]
FROM
    [ProductGroups] AS [g]
WHERE
    CHARINDEX(N'Group', [g].[Name]) > 0

And 1 query for fetching of the products:

SELECT
    [g.Products].[Id], [g.Products].[GroupId], [g.Products].[Name], [t].[Id]
FROM
    [Products] AS [g.Products]
    INNER JOIN
    (
        SELECT
            [g0].[Id]
        FROM
            [ProductGroups] AS[g0]
        WHERE
            CHARINDEX(N'Group', [g0].[Name]) > 0
    ) AS [t]
        ON [g.Products].[GroupId] = [t].[Id]
WHERE
    CHARINDEX(N'1', [g.Products].[Name]) > 0
ORDER BY
    [t].[Id]

Alas, the usage of FirstOrDefault() is still producing N+1 queries

var groups = Context.ProductGroups
.Where(g => g.Name.Contains("Group"))
.Select(g => new
{
ProductGroup = g,
Product = g.Products.FirstOrDefault()
})
.ToList();

and at the moment GroupBy() is not as powerful as in EF 6 so the following query fetches the whole table instead of the first product for each product group.

var firstProducts = Context.Products
.GroupBy(p => p.GroupId)
.Select(g => g.FirstOrDefault())
.ToList();

The corresponding SQL statement is:

SELECT
    [p].[Id], [p].[GroupId], [p].[Name]
FROM
    [Products] AS [p]
ORDER BY
    [p].[GroupId]

 

There is a lot of work to do but we are getting there... until then keep using your favorite profiling tool.