TheLazy.Dev
Portfolio GitHub

Blog

csharp |entityframeworkcore |aspnetcore

[EntityFramework Core] Try relationship 1

masanori_msl

Masui Masanori

Posted on March 9, 2021

[EntityFramework Core] Try relationship 1

Intro

This time, I will try EF Core about relationship, for example setting foreign key, "Include" method, etc.

Environments

  • ASP.NET Core ver.6.0.100-preview.1.21103.13
  • Microsoft.EntityFrameworkCore ver.6.0.0-preview.1.21102.2
  • Npgsql.EntityFrameworkCore.PostgreSQL ver.6.0.0-preview1
  • Microsoft.EntityFrameworkCore.Design ver.6.0.0-preview.1.21102.2
  • Microsoft.AspNetCore.Mvc.NewtonsoftJson ver.6.0.0-preview.1.21103.6

DB tables

Alt Text

DB Migrations

First, I will try setting foreign key on DB migrations.

With only properties(no IDs)

Author.cs 

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookStoreSample.Models
{
    [Table("author")]
    public record Author
    {
        [Key]
        [Column("id")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; init; }
        [Required]
        [Column("name")]
        public string Name { get; init; } = "";
        // Many to 1
        public List<Book> Books { get; init; } = new List<Book>();
    }
}
Enter fullscreen mode Exit fullscreen mode

Book.cs 

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookStoreSample.Models
{
    [Table("book")]
    public record Book
    {
        [Key]
        [Column("id")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; init; }
        [Required]
        [Column("name")]
        public string Name { get; init; } = "";
        // 1 to many
        public Author? Author { get; init; }
        // Many to 1
        public List<BookPrice> BookPrices { get; init; } = new List<BookPrice>();
        // Many to many
        public List<BookStore> BookStores { get; init; } = new List<BookStore>();
    }
}
Enter fullscreen mode Exit fullscreen mode

BookStore.cs 

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BookStoreSample.Models
{
    [Table("book_store")]
    public record BookStore
    {
        [Key]
        [Column("id")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; init; }
        [Required]
        [Column("name")]
        public string Name { get; init; } = "";
        // Many to many
        public List<Book> Books { get; init; } = new List<Book>();
    }
}
Enter fullscreen mode Exit fullscreen mode

BookStoreContext.cs 

using Microsoft.EntityFrameworkCore;
namespace BookStoreSample.Models
{
    public class BookStoreContext: DbContext
    {
        public BookStoreContext(DbContextOptions<BookStoreContext> options): base(options)
        {   
        }
        public DbSet<Author> Authors => Set<Author>();
        public DbSet<BookStore> BookStores => Set<BookStore>();
        public DbSet<Book> Books => Set<Book>();
        public DbSet<BookPrice> BookPrices => Set<BookPrice>();
    }
}
Enter fullscreen mode Exit fullscreen mode

Results

  • "book" table was added a column named "AuthorId"(The foreign key of "author").
  • A table named "BookBookStore" was created. It had "BookId" and "BookStoreId".

When I want to add foreign key by migrations, I don't need do anything like below.

BookStoreContext.cs 

...
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Book>()
                .HasOne(b => b.Author!)
                .WithMany(a => a!.Books)
                .HasForeignKey(b => b.AuthorId);
        }
...
Enter fullscreen mode Exit fullscreen mode

With only IDs(no properties)

Author.cs 

...
    [Table("author")]
    public record Author
    {
        [Key]
        [Column("id")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; init; }
        [Required]
        [Column("name")]
        public string Name { get; init; } = "";
    }
...
Enter fullscreen mode Exit fullscreen mode

Book.cs 

...
    [Table("book")]
    public record Book
    {
        [Key]
        [Column("id")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; init; }
        [Required]
        [Column("name")]
        public string Name { get; init; } = "";
        [Required]
        [Column("author_id")]
        [ForeignKey("author")]
        // 1 to many
        public int AuthorId { get; init; }
...
Enter fullscreen mode Exit fullscreen mode

BookStore.cs 

...
    [Table("book_store")]
    public record BookStore
    {
        [Key]
        [Column("id")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; init; }
        [Required]
        [Column("name")]
        public string Name { get; init; } = "";
    }
...
Enter fullscreen mode Exit fullscreen mode

Because there are no any data what connects "book" table and "book_store" table, I add "stored_book" table.

StoredBook.cs 

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BookStoreSample.Models
{
    [Table("stored_book")]
    public record StoredBook
    {
        [Required]
        [Column("bookstore_id")]
        [ForeignKey("book_store")]
        public int BookStoreId { get; init; }
        [Required]
        [Column("book_id")]
        [ForeignKey("book")]
        public int BookId { get; init; }
    }
}
Enter fullscreen mode Exit fullscreen mode

Results

  • Colmns like "author_id" are created. But they don't refer to tables.

I can't add foreign key by "ForeignKey" attributes.

Summary

  • I can set foreign keys by adding properties.
  • If I want to control the names of foreaign keys, I should also add ID properties like "AuthorId".
  • I don't need doing anything in "OnModelCreating" method to set foreign keys.

Create

Of course, I can insert new record with IDs of reference tables.

BookService.cs 

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using BookStoreSample.Applications;
using BookStoreSample.Models;
using Microsoft.EntityFrameworkCore;

namespace BookStoreSample.Books
{
    public class BookService: IBookService
    {
        private readonly BookStoreContext context;
        public BookService(BookStoreContext context)
        {
            this.context = context;
        }
        public async Task<UploadResult> CreateBookAsync()
        {
            using var transaction = await context.Database.BeginTransactionAsync();
            try
            {
                Author author = await context.Authors.FirstAsync(a => a.Id == 2);
                Book newBook = new Book
                {
                    Name = "SampleBook",
                    AuthorId = author.Id,
                };
                await context.Books.AddAsync(newBook);
                await context.SaveChangesAsync();
                var newPrice = new BookPrice
                {
                    BookId = newBook.Id,
                    Price = 2000m,
                    StartDate = DateTime.Today,
                };
                await context.BookPrices.AddAsync(newPrice);
                await context.SaveChangesAsync();
                await transaction.CommitAsync();
                return UploadResultFactory.GetSucceeded();
            }
            catch(Exception ex)
            {
                await transaction.RollbackAsync();
                return UploadResultFactory.GetFailed(ex.Message);
            }            
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

But I also can insert with instances of the tables.

BookService.cs 

...
        public async Task<UploadResult> CreateBookAsync()
        {
            using var transaction = await context.Database.BeginTransactionAsync();
            try
            {
                Author author = await context.Authors.FirstAsync(a => a.Id == 2);
                Book newBook = new Book
                {
                    Name = "SampleBook",
                    Author = author,
                    BookPrices = new List<BookPrice>
                    {
                        new BookPrice
                        {
                            Price = 3000m,
                            StartDate = DateTime.Today
                        }
                    }
                };
                await context.Books.AddAsync(newBook);
                await context.SaveChangesAsync();
                await transaction.CommitAsync();
                return UploadResultFactory.GetSucceeded();
            }
            catch(Exception ex)
            {
                await transaction.RollbackAsync();
                logger.LogError(ex.Message);
                return UploadResultFactory.GetFailed(ex.Message);
            }

        }
...
Enter fullscreen mode Exit fullscreen mode

Change IDs

What's happened when I change some IDs before inserting?

Change primary key

I can't change the primary key. Or I will get an exception.

BookService.cs 

...
                Author author = await context.Authors.FirstAsync(a => a.Id == 2);
                author.Id = 30;

                Book newBook = new Book
                {
                    Name = "SampleBook",
                    AuthorId = author,
                };
                await context.Books.AddAsync(newBook);
                await context.SaveChangesAsync();
...
Enter fullscreen mode Exit fullscreen mode

Result 

The property 'Author.Id' is part of a key and so cannot be modified or marked as modified. To change the principal of an existing entity with an identifying foreign key, first delete the dependent and invoke 'SaveChanges', and then associate the dependent with the new principal.
Enter fullscreen mode Exit fullscreen mode

Add existed record what refers another record

BookService.cs 

...
                Author author = await context.Authors.FirstAsync(a => a.Id == 2);
                BookPrice existedPrice = await context.BookPrices.FirstAsync(p => p.BookId == 2);

                Book newBook = new Book
                {
                    Name = "SampleBook",
                    Author = author,
                    BookPrices = new List<BookPrice>
                    {
                        existedPrice
                    }
                };
                await context.Books.AddAsync(newBook);
                await context.SaveChangesAsync();
...
Enter fullscreen mode Exit fullscreen mode

Result

No new "BookPrice" records were inserted.
But the "existedPrice"'s "BookId" was changed to "newBook"'s ID.

Read

In EF Core, by default, child tables are not include the parent records automatically.

BookService.cs 

...
        public async Task<List<BookStore>> GetBookStoresAsync()
        {
            return await context.BookStores.ToListAsync();
        }
...
Enter fullscreen mode Exit fullscreen mode

Result 

[{
    "id":1,
    "name":"Store1",
    "books":[]
},
{
    "id":2,
    "name":"Store2",
    "books":[]
},
{
    "id":3,
    "name":"Store3","books":[]}]
Enter fullscreen mode Exit fullscreen mode

I can use "Include()" to include child tables.

BookService.cs 

...
        public async Task<List<BookStore>> GetBookStoresAsync()
        {
            return await context.BookStores.Include(s => s.Books)
                .ToListAsync();
        }
...
Enter fullscreen mode Exit fullscreen mode

Result 

[{
    "id":1,
    "name":"Store1",
    "books":[{
        "bookStoreId":1,
        "bookId":2,
        "book":null
    }]
}]
Enter fullscreen mode Exit fullscreen mode

Include grandchildren?

I can use "ThenInclude()" to include grandchildren.

BookService.cs 

...
        public async Task<List<BookStore>> GetBookStoresAsync()
        {
            return await context.BookStores.Include(s => s.Books)
                .ThenInclude(s => s.Book)
                .ThenInclude(b => b!.Author)
                .ToListAsync();
        }
...
Enter fullscreen mode Exit fullscreen mode

Result 

[{
    "id":1,
    "name":"Store1",
    "books":[{
        "bookStoreId":1,
        "bookId":2,
        "book":{
            "id":2,
            "name":"SampleBook",
            "authorId":2,
            "author":{
                "id":2,
                "name":"SampleAuthor",
                "books":[{
                    "id":3,
                    "name":"SampleBook",
                    "authorId":2,
                    "bookPrices":[],
                    "stores":[{
                        "bookStoreId":1,
                        "bookId":3
                    }]
                }]
...
Enter fullscreen mode Exit fullscreen mode

Resources

💖 💪 🙅 🚩
masanori_msl
Masui Masanori

Posted on March 9, 2021

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

[ASP.NET Core][Entity Framework Core] Try JWT 1
csharp [ASP.NET Core][Entity Framework Core] Try JWT 1

March 12, 2022

[EntityFramework Core] Try relationship 1
csharp [EntityFramework Core] Try relationship 1

March 9, 2021