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
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>();
}
}
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>();
}
}
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>();
}
}
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>();
}
}
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);
}
...
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; } = "";
}
...
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; }
...
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; } = "";
}
...
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; }
}
}
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);
}
}
}
}
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);
}
}
...
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();
...
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.
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();
...
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();
}
...
Result
[{
"id":1,
"name":"Store1",
"books":[]
},
{
"id":2,
"name":"Store2",
"books":[]
},
{
"id":3,
"name":"Store3","books":[]}]
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();
}
...
Result
[{
"id":1,
"name":"Store1",
"books":[{
"bookStoreId":1,
"bookId":2,
"book":null
}]
}]
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();
}
...
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
}]
}]
...
Latest comments (0)