Relationships in a database context define how two entities relate to each other. Entity Framework Core really shines in supporting relationships. It offers a convention-based configuration, that will configure relationships based on the model provided. For more advanced cases we can use robust Fluent API capabilities, that offer greater flexibility.

I must admit that working with relationships in Entity Framework Core 5 feels very natural for a developer and that might be simply its most important feature.

Types of relationships

Relationships in the database will mean, that two entities relate to each other. They are logically connected. Let’s take a look at a hotel model example:

We have a reservation, that has a single room and a list of guests. The room can be assigned to many reservations. A profile can be assigned to many reservations, but it has only one address related. We have 3 different types of relationships defined here:

• one-to-many – room to a reservation
• many-to-many – reservation to profile
• one-to-one – profile to address

Those types are nicely supported by Entity Framework Core, so let’s have a look at the model that corresponds to this schema. Here is a Reservation:

    public class Reservation
    {
        public int Id { get; set; }

        public int RoomId { get; set; }

        public Room Room { get; set; }

        public List<Profile> Profiles { get; set; }

        public DateTime Created { get; set; }

        public DateTime From { get; set; }

        public DateTime To { get; set; }
    }

And Room:

    public class Room
    {
        public int Id { get; set; }

        public int Number { get; set; }

        public string Description { get; set; }

        public DateTime LastBooked { get; set; }

        public int Level { get; set; }

        public RoomType RoomType { get; set; }

        public bool WithBathroom { get; set; }

        public int NumberOfPlacesToSleep { get; set; }
    }

    public enum RoomType
    {
        Standard,
        Suite
    }

And Profile:

    public class Profile
    {
        public int Id { get; set; }

        public string Ref { get; set; }

        public string Salutation { get; set; }

        public string Forename { get; set; }

        public string Surname { get; set; }

        public string TelNo { get; set; }

        public string Email { get; set; }

        public string Country { get; set; }

        public DateTime? DateOfBirth { get; set; }

        public Address Address { get; set; }

        public List<Reservation> Reservations { get; set; }
    }

And finally, Address:

    public class Address
    {
        public int Id { get; set; }

        public string Street { get; set; }

        public string HouseNumber { get; set; }

        public string City { get; set; }

        public string PostCode { get; set; }

        public int ProfileId { get; set; }

        public Profile Profile { get; set; }
    }

And a cherry on top, a PrimeDbContext:

    public class PrimeDbContext : DbContext
    {
        public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Room> Rooms { get; set; }

        public virtual DbSet<Profile> Profiles { get; set; }

        public virtual DbSet<Reservation> Reservations { get; set; }

        public virtual DbSet<Address> Address { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
    }

Please notice a very important thing, thanks to a convention-based configuration, there is no additional configuration needed in the model classes and PrimeDbContext class.

Configuration is easy

Have you noticed how easy it is to configure relationships in Entity Framework Core 5? If you have properties named correctly, then EF Core will deduct relationships on its own. A relationship is defined by a navigation property, that is an entity inside an entity. Look at the Reservation. There is a Room, that is a navigation property and there is a RoomId that will be treated as a foreign key to define a constraint. 

There are 3 ways to configure model and relationships:

• convention-based – with properly named properties, EF Core will deduct how entities are related
• data annotations – handy attributes that you can put on the entity property
• Fluent API – a fully-featured API for configuring relations and entities as you wish

Here is an example of a data annotation to set a non-standard named foreign key:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogForeignKey { get; set; }

    [ForeignKey("BlogForeignKey")]
    public Blog Blog { get; set; }
}

If the foreign key would be named BlogId, then it would be configured automatically, but a custom name has to be handled manually.

Even with many-to-many relationships there is no need to define a joining table and write additional configuration. It is a new feature available from RC1 version, so documentation available on the official Microsoft page can be misleading. Hopefully, it will be updated soon.

Luckily, in most cases, you would not need to write much of configuration manually, cause it is mainly required when coping with advanced scenarios and custom mappings.

Model First approach

Model First approach lets you define your model and relationships and use Entity Framework Core to generate SQL for you. All you need to do is to create models that you want and when you’re done, just create a database migration. This is true of course when you already have EF Core migrations in place. 

It also works for updating the model, when you need to add a related entity, EF Core migrations will handle that surprisingly well.

Let’s say I had a profile entity and I wanted to add an Address entity in a one-to-one relationship. You can take a look at the code of those both classes above. When I add a new migration from dotnet CLI, I get a new migration like this, already generated for me, based on my model changes.

    public partial class AddAddress : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Address",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Street = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    HouseNumber = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    City = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    PostCode = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    ProfileId = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Address", x => x.Id);
                    table.ForeignKey(
                        name: "FK_Address_Profiles_ProfileId",
                        column: x => x.ProfileId,
                        principalTable: "Profiles",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_Address_ProfileId",
                table: "Address",
                column: "ProfileId",
                unique: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Address");
        }
    }

Nice and easy, but what’s most important – it works!

Summary

Configuring relationships in Entity Framework Core 5 is as simple as it can possibly be. Most of the mapping can be done automatically by the framework, just by naming properties right. If you’re struggling with more advanced scenarios you can go with Fluent API, which offers a lot and is rather compact. In just a few lines you can define how to map a view to an entity.

My favorite part, though, is a Model First approach, where you create a model that you would like to work with and generate SQL with EF Core migrations. 

All code mentioned here is available on my GitHub, code that uses those relationships as well! Feel free to drop by.

When working with a fast-evolving project changes happen rapidly not only to the project’s code but also to the database schema. It happens especially when working on a micro-service from the start when its purpose shifts.

How EF Core 5 migrations work

In Entity Framework Core 5 migrations we are adding a migration as a delta changes between our DbContext class and existing [DbContext name]ModelSnapshot. When generating new migration a CLI Tool will generate only the differences between those two and put them in two methods: Up and Down. In the first one, there will be a change to apply the migration and in the second one, to remove the migration.

After the migration is applied, its name is noted in the __EFMigrationsHistory table.

Merging multiple migrations 

Let’s say that after changing a schema multiple times in the early stages our project is now stable. We have a bunch of migrations that could be merged into one, that would create a model once, without many small updates. 

When we can remove everything

The easiest way to merge all migrations would be removing everything! What I mean is:

• remove Migrations directory with all migrations
• clear __EFMigrationHistory table
• remove all tables and other database objects that were added via migrations
• create new migration with all changes

This is a drastic way of merging migrations because we will lose all the data. However, it’s super simple and it might work in some cases.

When we need to preserve data

This means that we cannot remove all already created database objects, but we can merge migration files in our code. Let’s see how that can be done.

1. Delete all migration scripts from Migrations folder
2. Add a new migration with command dotnet ef migrations add MergedMigration
3. Copy the entire file and clear both Up and Down methods
4. Update the database and apply MergedMigration migration with command dotnet ef database update
5. After that, replace the content of the MergedMigration file with earlier generated code

As a result, you will have only one migration file. In my example the __EFMigrationHistory table looks like this.

And now in the Visual Studio, I can see only on migration.

That contains changes from all my previous migrations merged together.

It worked!

Tip! You can also name you merged migration as the first one, that was already applied so that you wouldn’t have to update the database and add it to the __EFMigrationHistory table. 

It will not work in every case

We can easily merge migrations when they are applied only to the database that we control. I addition, it will not work for environments that don’t have all merged migrations applied. The process is easy, but there are certain things that need to be taken into account.

On the other hand, do we need to keep all migrations, even when we know that we won’t run them ever again? I don’t think so. At this point, it would be great to merge old migrations but leave the newest ones. It could be accomplished with a very similar process.

1. Revert last N migrations locally, one by one, and move them somewhere
2. Check out the project in the place matching the migrations
3. Merge all existing migrations
4. Check out the project in the newest state
5. Add saved newest migrations, one by one

In this case, we will keep the newest migrations and create a big initial migration, that would be consistent with your project. If there will be a case, where all migrations would need to be applied to a database, the database schema wouldn’t be broken.

Summary

Merging migrations in Entity Framework Core 5 is possible and I can say it’s surprisingly easy. However, it involves an automatic generation process and you need to check if merged migration does exactly the same thing as all migrations applied one by one. Moreover, there is more than one way to merge migrations and you need to choose the one that is best for you.

All code posted here is available at my GitHub, so you can download it and play with it. Take a look also at this post on how to run it: PrimeHotel – how to run this project.

Thanks for reading and hopefully see you again 🙂