Foreign Keys in Entity Framework Core

Hello everyone! Today we’re going to explore how to work with foreign keys in Entity Framework Core using .NET. Foreign keys are fundamental in any relational database since they allow us to relate tables and maintain data integrity. So, why not dive right into this fascinating topic?

1 - Relating entities in entity framework core

For this post we’re going to use the same content we’ve used throughout the course. In our case, we have the entities User, which represents a system user, and WorkingExperience, which represents one of that user’s entities. What we want to do now is link both so that through the user entity, we can access the work experiences without having to make another call to the database. 

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
    [MaxLength(50)]
    public string Email { get; set; }
    
    public ICollection<Wokringexperience> Wokringexperiences { get; set; } // <- this line
}

To do this, in our User class we add an ICollection<WorkingExperience>. We use ICollection because it gives us a bit more versatility compared to IEnumerable, such as adding, removing, or updating elements. If you’re not going to use any of those features, you can use IEnumerable. IList is also a very good alternative, but as always it depends on your use case. 

And then, in WorkingExperience we can also indicate the UserId.

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

    public int UserId { get; set; } //<------ this line

    [MaxLength(50)]
    public string Name { get; set; }
    
    public string Details { get; set; }

    public string Environment { get; set; }

    public DateTime? StartDate { get; set; }

    public DateTime? EndDate { get; set; }
}

With this, the last thing left is to run the migrations. Once the migrations are done, we can see how an index and the foreign key have been added:

public partial class LinkUserAndWorkingExperiences : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateIndex(
            name: "IX_Wokringexperiences_UserId",
            table: "Wokringexperiences",
            column: "UserId");

        migrationBuilder.AddForeignKey(
            name: "FK_Wokringexperiences_Users_UserId",
            table: "Wokringexperiences",
            column: "UserId",
            principalTable: "Users",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Wokringexperiences_Users_UserId",
            table: "Wokringexperiences");

        migrationBuilder.DropIndex(
            name: "IX_Wokringexperiences_UserId",
            table: "Wokringexperiences");
    }
}

Note: Previously, it was necessary to indicate the relationship in the OnModelCreating method of our DbContext, but that’s no longer required. This was the necessary code: 

modelBuilder.Entity<Wokringexperience>()
    .HasOne(l => l.User)
    .WithMany(a => a.Wokringexperiences)
    .HasForeignKey(l => l.UserId);

2 - Insert Related Data in Entity Framework

In this example, I’m adding the data in the controller to keep it simple, but if you’re working in a professional environment, please use a project structure appropriate for your project. 

For this, we simply inject our CursoEfContext and use it. In this example, we can see how we first indicate the user and then use that same user to insert the WorkingExperience entries. 

private readonly CursoEfContext _context;

[HttpPost("InsertDataExample1")]
public async Task InsertDataExample1()
{
    User user1 = new User()
    {
        Email = $"{Guid.NewGuid()}@mail.com",
        UserName = "id1"
    };

    List<Wokringexperience> workingExperiences1 = new List<Wokringexperience>()
    {
        new Wokringexperience()
        {
            UserId = user1.Id,
            Name = "experience 1",
            Details = "details1",
            Environment = "environment"
        },
        new Wokringexperience()
        {
            UserId = user1.Id,
            Name = "experience 2",
            Details = "details2",
            Environment = "environment"
        }
    };

    await _context.Users.AddAsync(user1);
    await _context.Wokringexperiences.AddRangeAsync(workingExperiences1);
    await _context.SaveChangesAsync();
}

Entity Framework Core is smart enough to understand that our UserId column refers to the Id of the User table automatically, and you can see this in the database.

And thanks to the fact that in our User entity we’ve indicated WorkingExperiences as an ICollection<T>, we can insert everything at once simply by inserting the user: 

User user1 = new User()
{
    Email = $"{Guid.NewGuid()}@mail.com",
    UserName = "id1",
    Wokringexperiences = new List<Wokringexperience>()
    {
        new Wokringexperience()
        {
            Name = "experience 1 same object",
            Details = "details1",
            Environment = "environment"
        },
        new Wokringexperience()
        {
            Name = "experience 2 same object",
            Details = "details2",
            Environment = "environment"
        }
    }
};

3 - Query related data with Entity Framework

Finally, let’s see how we can retrieve the information. To read related tables, what we’re going to do with Entity Framework Core is use the .Include() method that is part of the DbSet. 

In this case, we’re going to query for users and request that it also returns the WorkingExperiences

[HttpGet("{userId}")]
public async Task<User?> GetExample(int userId)
    => await _context.Users
        .Include(u => u.Wokringexperiences)
        .FirstOrDefaultAsync(a => a.Id == userId);

And as you can see, it works perfectly: 

Before finishing the post, I think it’s important to mention that you have to be careful using includes everywhere, because EF Core has always had a bad reputation for the queries it creates behind the scenes, something that has honestly improved. But no matter how much the library improves, if you include all related tables in every query you make, you’re going to have tremendous bottlenecks in your database. 

And of course, be careful with loops, like when an entity calls another entity which calls the original, this can cause a lot of problems as well.