C#•6d ago

✅ Do we need to explicitly set foreign keys in EF Core?

Hello guys, just a quick question. Say I want to implement the following table which contains 2 foreign keys. Can EF Core automatically infer that the MemberId and BookId are foreign key (Assume we add navigation property in each table where needed)?

C#
namespace Week5.Models;

public class Lending
{
    public int LendingId { get; set; }
    public DateTime IssueDate { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime ReturnDate { get; set; }
    
    // Foreign keys
    public int MemberId { get; set; }
    public int BookId { get; set; }
    
    // Navigation property - one lending can have one member; one lending can have one book;
    public Books? Book { get; set; }
    public Member? Member { get; set; }
    
}

C#
namespace Week5.Models;

public class Lending
{
    public int LendingId { get; set; }
    public DateTime IssueDate { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime ReturnDate { get; set; }
    
    // Foreign keys
    public int MemberId { get; set; }
    public int BookId { get; set; }
    
    // Navigation property - one lending can have one member; one lending can have one book;
    public Books? Book { get; set; }
    public Member? Member { get; set; }
    
}

C#
  protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Book - Lending : One to Many
        modelBuilder.Entity<Books>()
            .HasMany(b => b.Lendings)
            .WithOne(l => l.Book)
            .HasForeignKey(l => l.BookId);
        
        // Member - Lending : One to Many
        modelBuilder.Entity<Member>()
            .HasMany(m => m.Lendings)
            .WithOne(l => l.Member)
            .HasForeignKey(l => l.MemberId);
        
    }

C#
  protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Book - Lending : One to Many
        modelBuilder.Entity<Books>()
            .HasMany(b => b.Lendings)
            .WithOne(l => l.Book)
            .HasForeignKey(l => l.BookId);
        
        // Member - Lending : One to Many
        modelBuilder.Entity<Member>()
            .HasMany(m => m.Lendings)
            .WithOne(l => l.Member)
            .HasForeignKey(l => l.MemberId);
        
    }

34 Replies

Angius•6d ago

No need to EF works by convention in this case

FakerOP•6d ago

yeah I see, what does it look for it to identify the foreign key ? things like {Book}Id ?

Angius•6d ago

Property [Name]Id will automatically be treated as a foreign key for property [Name] Yep

FakerOP•6d ago

Yep I see, thanks !

Angius•6d ago

Same for the primary key [ClassName]Id and Id both work

FakerOP•6d ago

yep noted 👍

Jimmacle•6d ago

also fwiw, when setting the relationship you can change either the id or the actual object, you don't have to change both

FakerOP•6d ago

Yep, I guess its better to change the Id, no ?

Jimmacle•6d ago

it depends on what you have at the time sometimes you have the object, sometimes you only have the id it's not worth a database roundtrip to pull the whole entity if you just need to change the id if you're creating new entities then you don't even have an id to set and you have to set the object

Angius•6d ago

Could technically do weird shenanigans with manually attaching the entity you created from just the ID, but... don't

FakerOP•6d ago

C#
// Option 1: Set Foreign Key directly
Lending lending = new Lending
{
    MemberId = 1,  // Set FK directly
    BookId = 10,
};

// Option 2: Assign the object instead
Lending lending = new Lending
{
    Member = existingMember,  // Set Navigation Property
    Book = existingBook,
};

C#
// Option 1: Set Foreign Key directly
Lending lending = new Lending
{
    MemberId = 1,  // Set FK directly
    BookId = 10,
};

// Option 2: Assign the object instead
Lending lending = new Lending
{
    Member = existingMember,  // Set Navigation Property
    Book = existingBook,
};

This is what you guys mean by either using the id or the object? is this what you mean please

Sehra•6d ago

did you try it?

FakerOP•6d ago

For the option 1 yeah, I did it when creating a new lending object This is what I did:

C#
using (var context = new LibraryContext())
{
    // Add new Book
    var book = new Books { Author = "Jane Doe", Isbn = 4588709, Title = "Yet another book with another title" };
    context.Book.Add(book);
    await context.SaveChangesAsync();
    
    var books = context.Book.ToList();
    
    foreach (var b in books)
    {
        Console.WriteLine(b);
    }
    
    // Add new member
    var member = new Member { FirstName = "Jane", LastName = "Doe" };
    context.Member.Add(member);
    context.SaveChanges();
    
    // Querying db
    Console.WriteLine(new string('-',20));
    var query = await context.Book.OrderBy(b => b.BookId).FirstAsync();
    Console.WriteLine(query);
    
    //Updating db
    query.Author = "Paul Smith";
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, BookId = 2, MemberId = 1});
    await context.SaveChangesAsync();
}

C#
using (var context = new LibraryContext())
{
    // Add new Book
    var book = new Books { Author = "Jane Doe", Isbn = 4588709, Title = "Yet another book with another title" };
    context.Book.Add(book);
    await context.SaveChangesAsync();
    
    var books = context.Book.ToList();
    
    foreach (var b in books)
    {
        Console.WriteLine(b);
    }
    
    // Add new member
    var member = new Member { FirstName = "Jane", LastName = "Doe" };
    context.Member.Add(member);
    context.SaveChanges();
    
    // Querying db
    Console.WriteLine(new string('-',20));
    var query = await context.Book.OrderBy(b => b.BookId).FirstAsync();
    Console.WriteLine(query);
    
    //Updating db
    query.Author = "Paul Smith";
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, BookId = 2, MemberId = 1});
    await context.SaveChangesAsync();
}

based on the book and member I added, I added a new record in Lending table

Sehra•6d ago

so you can just change BookId and MemberId in the last query to refer to the objects you created earlier

Jimmacle•6d ago

yeah 5 database roundtrips is not necessary, you can do it all in one also, you are inconstently using both SaveChanges and SaveChangesAsync

Sehra•6d ago

if you added both a book and a lender at the same time, you wouldn't be able to use BookId since it doesn't have an id at that point

FakerOP•6d ago

oh ok, but then I would do something like BookId = book.BookId ? hmm what do you mean pls yeah was just experimenting with but there is nothing to do with Async at this stage I think, I should remove that

Sehra•6d ago

just Book = book in that case

FakerOP•6d ago

hmm when we create the new lending, like instead of using BookId and MemberId, we say Book = book and Member = member ? oh I see what you mean, like the context.SaveChanges, thing ?

Jimmacle•6d ago

3 pings in 4 minutes is crazy

FakerOP•6d ago

ops, sorry :c when writing Book = book and Member = member, behind the scenes, EF Core does the following: Book = book.BookId and Member = member.MemberId ?

Jimmacle•6d ago

not necessarily if they're new entities then EF doesn't know their IDs yet assuming they're database generated it will produce an appropriate query for that

FakerOP•6d ago

yeah I see, so the thing is, can we write something like that:

C#
using (var context = new LibraryContext())
{
    // Add new Book
    var book = new Books { Author = "Jane Doe", Isbn = 4588709, Title = "Yet         another book with another title" };
    
    // Add new member
    var member = new Member { FirstName = "Jane", LastName = "Doe" };

    context.Book.Add(book);
    context.Member.Add(member);

    // save both changes simultaneously, no need separate context.SaveChanges()         statements
    context.SaveChanges();
    
    // Querying db
    Console.WriteLine(new string('-',20));
    var query = await context.Book.OrderBy(b => b.BookId).FirstAsync();
    Console.WriteLine(query); // query == book object

    //Updating db
    query.Author = "Paul Smith";
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, BookId = book.BookId, MemberId = member.MemberId});
    context.SaveChanges();
}

C#
using (var context = new LibraryContext())
{
    // Add new Book
    var book = new Books { Author = "Jane Doe", Isbn = 4588709, Title = "Yet         another book with another title" };
    
    // Add new member
    var member = new Member { FirstName = "Jane", LastName = "Doe" };

    context.Book.Add(book);
    context.Member.Add(member);

    // save both changes simultaneously, no need separate context.SaveChanges()         statements
    context.SaveChanges();
    
    // Querying db
    Console.WriteLine(new string('-',20));
    var query = await context.Book.OrderBy(b => b.BookId).FirstAsync();
    Console.WriteLine(query); // query == book object

    //Updating db
    query.Author = "Paul Smith";
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, BookId = book.BookId, MemberId = member.MemberId});
    context.SaveChanges();
}

Here, notice we have 2 context.SaveChanges()... what I thought is, we must first create the book and member objects to have valid foreign keys else, we won't know what book.BookId and member.MemberId are.

Jimmacle•6d ago

you also don't have to re-query to get the ID of a new entity you've saved, that should be automatically updated by EF after calling SaveChanges iirc you don't need to know what they are, EF can manage it it knows what relationships should be set based on the object graph and it will set FKs as part of the change saving process

FakerOP•6d ago

oh ok, so I can just write a single context.SaveChanges() at the end provided that, I write something like that:

C#
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, BookId = book, MemberId = member});

C#
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, BookId = book, MemberId = member});

Jimmacle•6d ago

it should work

FakerOP•6d ago

yep noted, I will test and let you know, ty !!

Sehra•6d ago

Book, not BookId

Jimmacle•6d ago

i didn't say anything about that because the compiler will catch it :when:

FakerOP•6d ago

Soo, so I write this:

C#
// CRUD Operations using EF Core

using Microsoft.EntityFrameworkCore;
using Week5.Context;
using Week5.Models;

using (var context = new LibraryContext())
{
    // Add new Book
    var book = new Books { Author = "John Doe", Isbn = 4568709, Title = "Harry Potter" };
    
    // Add new member
    var member = new Member { FirstName = "LeBron", LastName = "James" };
    
    context.Book.Add(book);
    context.Member.Add(member);
    
    
    // Querying db
    Console.WriteLine(new string('-',20));
    var query = await context.Book.OrderBy(b => b.BookId).FirstAsync();
    Console.WriteLine(query);
    
    //Updating db
    query.Author = "John Smith";
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, Book = book, Member = member});
    context.SaveChanges();
}

C#
// CRUD Operations using EF Core

using Microsoft.EntityFrameworkCore;
using Week5.Context;
using Week5.Models;

using (var context = new LibraryContext())
{
    // Add new Book
    var book = new Books { Author = "John Doe", Isbn = 4568709, Title = "Harry Potter" };
    
    // Add new member
    var member = new Member { FirstName = "LeBron", LastName = "James" };
    
    context.Book.Add(book);
    context.Member.Add(member);
    
    
    // Querying db
    Console.WriteLine(new string('-',20));
    var query = await context.Book.OrderBy(b => b.BookId).FirstAsync();
    Console.WriteLine(query);
    
    //Updating db
    query.Author = "John Smith";
    query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, Book = book, Member = member});
    context.SaveChanges();
}

This works well, it was save in the db. Now, what I tried to do next, is to change Book = book from Book = book.BookId and Member = member from Member = member.MemberId.

query.Lendings.Add(new Lending{DueDate = new DateTime(2025,08,08), IssueDate = DateTime.Now, Book = book.BookId, Member = member.MemberId});

But it seems I get a compiler error where Cannot convert source type 'int' to target type. Ok, so, the thing, here Book and Member represent book and member objects respectively, that's why I can directly assign an int like I was doing earlier when I was explicitly setting the foreign keys. So, question: Here, we set the navigation properties, we don't use the foreign keys them, we could also have said BookId = book.BookId, no? would there be a difference?

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Lending_Member_MemberId". The conflict occurred in database "TestDB", table "dbo.Member", column 'MemberId'.

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Lending_Member_MemberId". The conflict occurred in database "TestDB", table "dbo.Member", column 'MemberId'.

yeah I got an error when I use the foreign keys, BookId and MemberId I shouldn't use them, unless may be if I use 2 context.SaveChanges(), like we create the book and member object first? but too much of work I will stick with the navigation properties, I guess it's easier (waiting for your feedbacks :c)

Jimmacle•6d ago

setting either the navigation or the id has the same effect on updating the FK because if you haven't saved the entities yet their IDs will be 0 which isn't a problem if you save them all at once, EF can figure it out but you can't mix creating new entities and assigning specific IDs as FKs at the same time

FakerOP•6d ago

ahhh I see, like since we are creating the new entity without saving them yet, we don't really have a foreign key, that's why I can't really do BookId = book.BookId ? EF does know that is the BookId though when saving it all at once but in our code, we don't know yet ?

Jimmacle•6d ago

yes, before you call SaveChanges it's all just C# code so if book.Id is 0 and you set BookId = book.Id, BookId will be 0 EF doesn't know where that value came from so it can't figure out that you want it to match a book that you're creating but if you set the actual Book navigation, it not only knows that there's a whole new entity to create but also that you want it to be related to the lending so it can handle updating the IDs behind the scenes as part of saving

FakerOP•6d ago

yep I see It's clearer now, thanks guys ! Learn a lot of things 🔥 I guess all my doubts have been cleared, should be good for now, will come back if I have other questions, thanks once more !

Gaming

Programming

✅ Do we need to explicitly set foreign keys in EF Core?

Did you find this page helpful?