Entity Framework Core Code First: Introduction, Best Practices, Repository Pattern, Clean Architecture

Introduction

This article is the resulting notes I took from a customer project on creating a database from scratch using Entity Framework Core

Please do comment if you see something wrong or if you know a better way of doing things :)

Happy coding!

Thanks EF Core

Agenda

• Basics
• Migrations
• Annotations
• Fluent API
• Using Entity Framework Core in code
• Further topics
• Repository Pattern & Clean architecture
• Tips and Tricks
• Errors and solutions

Repo

GitHub - Gabegi/EntityFrameworkCoreCodeFirst: EntityFrameworkCoreCodeFirst

Basics

How to work with databases in C#?

C# uses what is a called an Object Relational Mapper, that basically is an interface between the database and your code.

It can be used for all types of databases, SQL, NoSQL, Graph…

What is an Object Relational Mapper (ORM)?

Entity Framework Core is one of the many ORMs used to manage data.

• Interface between code and database
• Generate a database based on models in code (C# classes)
• Removes the need to use SQL and manual mapping
• Secure the connection between the database and the code
• Automates repetitve tasks

Should I use Entity Framework or Entity Framework Core?

Nope, Use Entity Frame CORE

After EF x6 was released, all active new developments happen only with EF Core only, so please use EF Core.

“Entity Framework Core is the only actively developed version of Entity Framework and we recommend using it for all new code.” Microsoft Doc

Entity Framework Support Policies

What is the difference between Code First and Database First?

• Code First takes C# code and translates it into a Database
• Database First takes a database and translates it into C# Code

How does Entity Framework Core Code First work?

• 1/ First Code the model in C# (Classes objects)
• 2/ Describe table keys (with annotations or fluentapi)
• 3/ Create the DbContext
• 4/ Setup the connection with the database
• 5/ Install the packages
• 6/ Then Use EF Migrations to create or update the database
• 7/ Finally, use the DbContext in the code to interact with the database

1/ Creating the Models

Each class is its own table in the database.

Good practice: keep the class name singular (e.g. Blog)

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
    public List<Post> Posts { get; set; }
}

2/ Creating the DbContext

The DbContext is the client that interacts with the Database

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        
    }
}

What is a DbSet?

DbSet creates a table from your class Blog

Best practice: plural name your table (e.g. Blogs)

DbContext Lifetime, Configuration, and Initialization — EF Core

3/ Set Up the Connection String for testing

Your connection string, for testing (for production use managed identity) you can set it up in your DbContext as:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(@"Server=DVT-CHANGEMENOW\SQLEXPRESS;Database=CodingWiki;TrustServerCertificate=True;Trusted_Connection=True;");
}

• Server= servername
• DataBase= databasename

4/ Migrations

First migration: How to set primary key in EF Core?

For your migrations, the model needs a primary key (PK)

Use the [Key] data annotation to do so (System.ComponentModel.DataAnnotations;)

public class Book
{
    [Key] // Sets primary key for IDBook
    public int IDBook { get; set; }

    public string Title { get; set; }
    public string ISBN { get; set; }
    public double Price { get; set; }
}

The other way to do it, is simply having a numerical field as “Id” (not case sensitive). Or just having the field ends with Id and starts with class name.

EF Core will automatically set Id as the PK

public class Book
{
    public int Id{ get; set; } // automatically sets as PK by EF Core

    public string Title { get; set; }
    public string ISBN { get; set; }
    public double Price { get; set; }
}

This also works

public class Book
{
    public int BookId{ get; set; } // automatically sets as PK by EF Core

    public string Title { get; set; }
    public string ISBN { get; set; }
    public double Price { get; set; }
}

Code First Conventions - EF6

5/ Entity Framework Core Packages Explained

• Microsoft.EntityFrameworkCore.SqlServer: Your main package to create the context and work with EF Core (already includes microsoft.entityframeworkcore package)
• Microsoft.EntityFrameworkCore.Tools: gives us the ability to use the dotnet ef command line tool + includes microsoft.entityframeworkcore.design package
• Microsoft.EntityFrameworkCore.Design: for the Web project (the starting project)

6/ Entity Framework Core migration

Once your model and bdContext are built, it’s time to migrate them to the database. For this we use migrations.

Make sure that the default project is where your DbContext is at

In the package manager console, write the commands

Create Migration

• add-migration nameofthemigration

add-migration AddBookToDb

Now you have your first migration, see automatically created ‘Migrations’ folder

Apply Migration

After creating a migration, it must be applied in the database

update-database

For the first migration, it will actually first create the database, then create SQL based on the migration and apply it.

You can now see the database in SSMS

7/ Use DbContext in your code

Example add a new product to the database

var newProduct = new Product { Name = "Laptop", Price = 999.99 };
            dbContext.Products.Add(newProduct); // Add your product in the Db
            dbContext.SaveChanges(); // Always save your changes!

Conclusion

This is the conclusion to the basic guide. You can see how easy it is to work with Entity Framework Core.

I am sure this has also left you with many questions, so the below sections will try to answer those.

Before going through the details, here are some tips

• Migration

If a migration or an update-database creates an error, ALWAYS first remove the migration and then create a new one with the corrections made in the code.

Make small changes and small migrations

Always check the migration file before updating database

• Annotations vs fluent api, which one to choose?

Use annotations for small projects and fluent api for larger ones

Entity Framework Core Migrations

When to use migration?

• New class/ table added to DB
• New property/ column
• Existing property/ column modified
• Property/ column deleted
• Class/ table deleted

Update the database

How to update the database in EF Core code first?

Simply create a new migration

add-migration yourFavouriteMigrationName

You can see the results in the migation file

Please note that if you don’t run update-database, the database won’t be well…updated

Remove migration

Only use it when the migration hasn’t been pushed the database and you want to revert it

Warning: Use this command only if the migration hasn’t been applied to the database!!!

remove-migration

Do not remove any migration files in the Migrations folder or change any files directly

How to revert back to an old migration?

Copy the name of the migration you previously applied

Here the name is: “AddBookToDb”

Append the name to update-database

update-database AddBookToDb

See reverting results

To come back to the latest version, simply do

update-database

It will reapply all the migrations that have been reverted

How to revert a migration in Entity Franework Core?

Do as previous step: update-data nameofmigrationyouwanttogobackto

update-database nameofmigrationyouwanttogobackto

do the changes you wish to make, make a migration

add-migration undidChanges

How to see what migration has been applied in EntityFramework Core?

In PMC just run:

get-migration

The resulting table will indicate which migration has been applied or not

How to delete the database in EntityFramework Core?

In PMC just run:

drop-database

To get the database back, just run

update-database

All migrations will be re-applied

Entity Framework Core Data Annotations

EF Core lets you work the SQL table directly in code using only annotations!

How to change table or column name in EntityFramework Core?

Tavle names are set in DbContext:

However, we can change this using data annotation:

Then we finish by applying a new migration

How to set a field as required in EntityFramework Core?

By clicking on your project, you can see if nullable is enabled

Because of that, all string properties are required by default, to override this, we need to ask the nullable “?”, e.g.:

        public string? GenreName { get; set; }

Without the <Nullable>enable</Nullable> the properties will be nullable by default

In case you don’t have the nullable enabled and you want a property to be required, you can use:

[Required (ErrorMessage = "Genre name is required")]
public string GenreName { get; set; }

How to ensure a property doesn’t get in the table in EntityFramework Core?

Sometimes we just want the field to be available in the code but not as a column, for such case use:

[NotMapped]
public string PriceRange { get; set; }

How to create foreign keys in EntityFramework Core with Data annotations?

1:1 Relationship

In a 1:1 relationship there is a child (dependent class) and parent (main class)

A foreign key, has to be placed in the child class and a reference to the child class must be present in the parent class

public class Book
{
    public int BookId { get; set; } // the PK
    public BookDetail BookDetail { get; set; } //the child class reference
}

public class BookDetail
{
    [Key]
    public int BookDetail_Id { get; set; }

    [ForeignKey("Book")] // "Book" indicates  the class the FK applies to
    public int BookId { get; set; } // the FK
   
    public Book Book { get; set; } // the reference to the parent class

See resulting migation

How to make the primary key custom generated in EntityFramework Core Data Annotations?

Use the DatabaseGenerated annotation

• [DatabaseGenerated(DatabaseGeneratedOption.None)]: no DB generated IDs
• [DatabaseGenerated(DatabaseGeneratedOption.Computed)]: DB generated

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)] // the DB won't create the Id
public int DealerID { get; set; }

The DatabaseGenerated Attribute

1:Many Relationship

In our example we want books to have a 1:many relationship to publishers

i.e. a publisher can have many books, a book can only have one publisher

public class Book
{
    // [Key]
    public int BookId { get; set; }
    
    [ForeignKey("Publisher")]
    public int Publisher_Id { get; set; }
    public Publisher Publisher { get; set; }
}

public class Publisher
{
    [Key]
    public int Publisher_Id { get; set; }

    public List<Book> Books { get; set; } // add FK object as List
}

Many:Many Relationship

Auhtors can have many books and books can have multiple authors

public class Author
{
    [Key] 
    public int Author_Id { get; set; }

    public List<Book> Books { get; set; } // Adds relationship to Book

}

We simply add

public class Book
{
    // [Key]
    public int BookId { get; set; }

    public List<Author> Authors { get; set; } // Adds relationship to Authors
}

EF Core (version 7+) automatically creates an intermediary table (mapping table) beteen Book and Author

How to manually create the intermediate mapping table in EntityFramework Core?

Create the following table with explicit FK for many:many relationship between author and book

public class BookAuthorMap
{
    [ForeignKey("Book")]
    public int Book_Id { get; set; }

    [ForeignKey("Author")]
    public int Author_Id { get; set; }

    public Book Book { get; set; }
    public Author Author { get; set; }
}

Then add mapping to respective classes, adding BookAuthorMap

public class Author
{
    [Key] 
    public int Author_Id { get; set; }
    
    public List<BookAuthorMap> BookAuthorMap { get; set; }

}

 public class Book
 {
     // [Key]
     public int BookId { get; set; }

     public List<BookAuthorMap> BookAuthorMap{ get; set; }
 }

Nevertheless, the table BookAuthorMap needs a composite key, this can be done using Fluent API

How to add composite key in EntityFramework Core Data Annotations?

Since EF Core 7+, data annotation is available for composite key

 [PrimaryKey("ProductID", "ModelID")]
 internal class ProductModel
 {
     public int ProductID { get; set; }
     public int ModelID { get; set; }
 }

Keys - EF Core

Resulting migration with two PKs

And resulting diagram

How to create many:many relationship with skip mapping table in EntityFramework Core Data Annotations?

• Books can have multiple authors
• Authors can have multiple books

No fluent api is even required

public class Fluent_Book
{
    [Key]
    public int BookId { get; set;}
    public List<Fluent_Author>Authors{ get; set; }
}

public class Fluent_Author
{
    [Key] 
    public int Author_Id { get; set; }
    public List<Fluent_Book> Books { get; set; }
}

Resulting mapping table (Fluent_AuthorFluent_Book)

Fluent API in EntityFramework Core

Introduction

Data annotations are great but they are also clustered all over your classes, perhaps you would want to have it all one place?

Also, fluent API can add many customisation that Data annotations cannot do,

To use it, simply add code in the OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
      // Fluent API code comes here
}

How to change table name with EntityFramework Core Fluent API?

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>().ToTable("tb_Category");
}

How to change column name with EntityFramework Core Fluent API?

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>()
                .Property(c => c.ISBN)
                .HasColumnName("ISBN");
}

How to make a column required with EntityFramework Core Fluent API?

modelBuilder.Entity<Category>()
            .Property(c => c.Title)
            .IsRequired();

How to add a primary key with EntityFramework Core Fluent API?

modelBuilder.Entity<Category>()
            .HasKey(c => c.CategoryId);

How to set Max Length with EntityFramework Core Fluent API?

modelBuilder.Entity<Category>()
            .Property(c => c.CategoryName)
            .HasMaxLength(50);

How to set Not Mapped with EntityFramework Core Fluent API?

modelBuilder.Entity<Category>()
            .Ignore(c => c.DiscountedPrice);

How to set 1:1 relationship with EntityFramework Core Fluent API?

• Book detail can only have one book (HasOne)
• And book only have one book detail (WithOne)
• The connection (ForeignKey) is BookId

modelBuilder.Entity<Fluent_BookDetail>()
            .HasOne(b => b.Book) // Object in Fluent_BookDetail
            .WithOne(bd => bd.BookDetail) // Object in Fluent_Book
            .HasForeignKey<Fluent_BookDetail>(b => b.BookId); 
           // Foreign Key can be in either Fluent_BookDetail OR Fluent_Book

public class Fluent_BookDetail
{
    [Key]
    public int BookDetail_Id { get; set; }

    public int BookId { get; set; }

    public Fluent_Book Book { get; set; }
}

public class Fluent_Book
{
    [Key]
    public int BookId { get; set; }

    public Fluent_BookDetail BookDetail { get; set; }

}

How to set 1:many relationship with EntityFramework Core Fluent API?

• One publisher can have many books
• One book only has one publisher

modelBuilder.Entity<Fluent_Book>() 
            .HasOne(u => u.Publisher) 
            .WithMany(u => u.Books)
            .HasForeignKey(u => u.Publisher_Id); // FK in Fluent_Book
  // FK always in entity that has the 1 side of the 1-many relationship

public class Fluent_Book
{
    [Key]
    public int BookId { get; set; }

    public int Publisher_Id { get; set; }
    public Fluent_Publisher Publisher { get; set; }
}

public class Fluent_Publisher
{
    [Key]
    public int Publisher_Id { get; set; }

    public List<Fluent_Book> Books { get; set; }
}

Composite Keys in Entity Framework Core Fluent API

Composite keys allow two PK to be combined and form a unique PK

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BookAuthorMap>()
    .HasKey(ba => new 
    { ba.Book_Id, 
      ba.Author_Id 
    });

How to manually create many:many relationship with fluent API in EntityFramework Core?

• Books can have multiple authors
• Authors can have multiple books
• Create mapping table

MappingTables don’t need their own DbSet<MappingTable>

// Mapping Table
public class Fluent_BookAuthorMap
{
    [Key]
    public int Book_Id { get; set; }
    public int Author_Id { get; set; }

    public Fluent_Book Book { get; set; }
    public Fluent_Author Author { get; set; }
}

public class Fluent_Book
{
    [Key]
    public int BookId { get; set; }
    public List<Fluent_BookAuthorMap> BookAuthorMap { get; set; }
}

public class Fluent_Author
{
    [Key] 
    public int Author_Id { get; set; }

    public List<Fluent_BookAuthorMap> BookAuthorMap { get; set; }
}

In DbContext:

• Create Composite key
• 1:many fluent_book to fluent_book_author
• 1:many fluent_book_author to fluent_book

// composite key
modelBuilder.Entity<Fluent_BookAuthorMap>()
            .HasKey(ba => new
            {
                ba.Book_Id,
                ba.Author_Id
            });
// 1:many between fluent_book & fluent_book_author
modelBuilder.Entity<Fluent_BookAuthorMap>()
            .HasOne(b => b.Book)
            .WithMany(ba => ba.BookAuthorMap)
            .HasForeignKey(b => b.Book_Id);

// 1:many between fluent_author & fluent_book
modelBuilder.Entity<Fluent_BookAuthorMap>()
            .HasOne(a => a.Author)
            .WithMany(ba => ba.BookAuthorMap)
            .HasForeignKey(a => a.Author_Id);

How to set Cascade options in Fluent API EntityFramework Core?

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Blog>()
        .HasOne(e => e.Owner)
        .WithOne(e => e.OwnedBlog)
        .OnDelete(DeleteBehavior.ClientCascade);
}

Cascade Delete — EF Core

What does onDelete: ReferencetialAction.Cascade mean in EntityFramework Core?

Cascade means that if the parent record, here the book, is deleted, then the book detail will also be deleted

Other options include:

onDelete: ReferencetialAction.NoAction:

• If the book is deleted, no action will be performed

onDelete: ReferencetialAction.Restrict:

• Will generate an error message if trying to delete

How to organise clusters in fluent API in EntityFramework Core?

As we add more configurations in protected override void OnModelCreating(ModelBuilder modelBuilder), it might become clustered and hard to read.

The solution is to create its own file for each entity

See here class added for the entity “FluentBookDetail”:

• Uses the interface IEntityTypeConfiguration that takes one entity

public class FluentBookDetailConfig : IEntityTypeConfiguration<Fluent_BookDetail>
{
    public void Configure(EntityTypeBuilder<Fluent_BookDetail> modelBuilder)
    {
        // no need to have Entity<Fluent_BookDetail>() anymore as we are using IEntityTypeConfiguration
        modelBuilder.HasOne(b => b.Book)
                    .WithOne(bd => bd.BookDetail)
                    .HasForeignKey<Fluent_BookDetail>(b => b.BookId);
    }
}

then add the reference to the DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    
    modelBuilder.ApplyConfiguration(new FluentBookDetailConfig());

}

Using Entity Framework Core in code

Querying data

var allEntities = dbContext.YourEntities.ToList();

var filteredEntities = dbContext.YourEntities.Where(e => e.SomeProperty == "SomeValue").ToList();

Find vs FirstOrDefault vs SingleOrDefault

// Based on any fields OR none
var book = dbContext.Books.FirstOrDefault(b => b.Id == 123);

// Based on primary key
var book = dbContext.Books.Find(123);

// When there is only one record with this parameter, otherwise throws an exception
var book = dbContext.Books.Single(b => b.Id == 123);

OrderBy and ThenOrderby

// Example query: Order by Name ascending and then by DateCreated descending
    var sortedEntities = context.YourEntities
        .OrderBy(e => e.Name)          // Primary sorting
        .ThenByDescending(e => e.DateCreated) // Secondary sorting
        .ToList();

Add

var newEntity = new YourEntity { Property1 = "Value1", Property2 = "Value2" };
dbContext.YourEntities.Add(newEntity);
dbContext.SaveChanges();

Update

var book = dbContext.Books.Find(1002)
book.ISBN = "777";
dbContext.SaveChanges();

var entitiesToUpdate = dbContext.YourEntities.Where(e => e.SomeCondition).ToList();
foreach (var entity in entitiesToUpdate)
{
    entity.SomeProperty = "NewValue";
}
dbContext.SaveChanges();

Pagination

var books = dbContext.Books.Skip(0).Take(2);

Delete

var book = dbContext.Books.Find(1002)
dbContext.Books.Remove(book);
dbContext.SaveChanges();

Add multiple

List<Book> books;
_db.Books.AddRange(books);
dbContext.SaveChanges();

Remove multiple

List<Book> books;
_db.Books.RemoveRange(books);
dbContext.SaveChanges();

Async Methods

Every method has an async variant

Should you use it?

Worse case scenario it will have the same performance than without async

// GET ALL
var allEntities = await dbContext.YourEntities.ToListAsync();

// ADD
var newEntity = new YourEntity { Property1 = "Value1", Property2 = "Value2" };
dbContext.YourEntities.Add(newEntity);
await dbContext.SaveChangesAsync();

// UPDATE
var entityToUpdate = await dbContext.YourEntities.FindAsync(id);
entityToUpdate.SomeProperty = "NewValue";
await dbContext.SaveChangesAsync();

// DELETE
var entityToDelete = await dbContext.YourEntities.FindAsync(id);
dbContext.YourEntities.Remove(entityToDelete);
await dbContext.SaveChangesAsync();

Further topics

How to seed data in EntityFramework Core? Aka how to add initial data in EntityFramework Core?

In the DbContext:

• add OnModelCreating method

To add a single entry

• add modelBuilder.Entity<T>.HasData(dataentry)

public class ApplicationDbContext : DbContext
{

public DbSet<Book> Books { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>().HasData(
            new Book
            {
                BookId = 1,
                Title = "Professional C# 7 and .NET Core 2.0",
                ISBN = "978-1-119-44926-0",
                Price = 45.00M
            });
}

To add more entries

public class ApplicationDbContext : DbContext
{
public DbSet<Book> Books { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var bookList = new Book[]
{
    new Book { BookId = 2, ISBN = "978-1-119-44926-0", Title = "Professional C# 7 and .NET Core 2.0", Price = 45.00M },
    new Book { BookId = 3, ISBN = "978-1-119-44926-0", Title = "Professional C# 7 and .NET Core 2.0", Price = 45.00M },
    new Book { BookId = 4, ISBN = "978-1-119-44926-0", Title = "Professional C# 7 and .NET Core 2.0", Price = 45.00M },
};
// add a list of Book objects
modelBuilder.Entity<Book>().HasData(bookList);
}

Finish by adding a migration

Seed data in migration

Create an empty migration in the PM (e.g. add-migration seedingDb)

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("INSERT INTO Categories (Name) VALUES ('Programming')");
}

Nullable Fields

Since .Net 6, in your project cs file, you will find the following

  <PropertyGroup>
    <Nullable>enable</Nullable>
  </PropertyGroup>

The <Nullable>enable</Nullable> means that:

• All properties are by default non-nullable reference types
• You can use the ? suffix to declare a nullable reference type.

e.g.:

    [Key]
    public int DiscountID { get; set; } // non-nullable by default
    
    public DateTime? EndDate { get; set; } // explicitly set as nullable "DateTime?"
}

DbContext Independency injection

GetConnectionString will automatically get to the appsettings.json

In the program.cs of your start project in the solution

builder.Services.AddDbContext<ApplicationDbContext>
    (options => options.UseSqlServer(
builder.Configuration.GetConnectionString("DefaultConnection")));

{"Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=DVT-CHANGEMENOW\\SQLEXPRESS;Database=CodingWiki;TrustServerCertificate=True;Trusted_Connection=True;"
  }
}

In your appsettings.json, add the DefaultConnection

Then in your dbContext.cs add the ApplicationDbContext constructor

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

Explicit loading & Eager Loading & Lazy Loading

Problem: the underlying code would make a trip to the database for each book

public async Task<IActionResult> Index()
{
    List<Book> objList = _dbContext.Books.ToList();
    foreach (var obj in objList)
    {;
        obj.Publisher = await _dbContext.Publishers.FindAsync(obj.Publisher_Id);
    }
    return View(objList);
}

Solution 1: use explicit loading, it will look at the object you’re querying, here Publisher, so it reduces the amount of trips to the DB

public async Task<IActionResult> Index()
{
    List<Book> objList = _dbContext.Books.ToList();
    foreach (var obj in objList)
    {;
// use load to explicitly load the data
        _dbContext.Entry(obj).Reference(u => u.Publisher).Load();
    }
    return View(objList);
}

Solution 2: use eager loading, it basically not only queries the entity (here book) but also all the other entities it is related to (here Publisher).

So when retrieving Book, we also retrieve Publisher

public async Task<IActionResult> Index()
{
  // eager loading is done using the Include statement
    List<Book> objList = _dbContext.Books.Include(u => u.Publisher).ToList();
    return View(objList);
}

Eager loading is most optimal solution, as it retrieves both entities using a join and therefore only one query/ trip to the database.

Solution 3: lazy loading the related data is loaded only when accessed

            // Assume there is an author with AuthorId = 1 in the database
            var author = context.Authors.Find(1);

            // At this point, the Books collection is not loaded
            // It will be loaded when accessed for the first time
            foreach (var book in author.Books)
            {
                Console.WriteLine($"Book Title: {book.Title}");
            }

Which one to choose? Although each situation has its own requirements, eager loading is often the most suitable approach

Deferred Execution

Entity Framework Core is smart about when to execute the query or not. It will only do it when the code actually needs it.

What might seem like some delay is actually a performance optimisation.

public async Task<IActionResult> Playgound()
{
    // Get executed right away
    var bookTemp = _dbContext.Books.FirstOrDefault(); 
    bookTemp.Price = 100;

    // No query is executed
    var bookCollection = _dbContext.Books;
    decimal totalPrice = 0;

    // Now the query is executed
    foreach(var book in bookCollection)
    {
        totalPrice += book.Price;
    }

    return RedirectToAction(nameof(Index));
}

IQueryable vs IEnumerable

Query with IEnumerable:

• Select all books
• Filter in memory (client side)

Query with IQueryable

• Selects all books with a filter (WHERE statement), (database side)

public async Task<IActionResult> Queryable()
{
    IEnumerable<Book> books = _dbContext.Books;
    var filteredBooks = books.Where(b => b.Price > 500).ToList();

    IQueryable<Book> books2 = _dbContext.Books;
    var filteredBooks2 = books2.Where(b => b.Price > 500).ToList();

    return View(books2);
}

IQueryable works great when we have lots of records we want to filter

Entit Framework Core tracking

When you execute a query and retrieve entities using methods like Find, FirstOrDefault, or ToList, the retrieved entities are automatically tracked by the DbContext.

// Tracked
var blog = context.Blogs.SingleOrDefault(b => b.BlogId == 1);
blog.Rating = 5;
context.SaveChanges();

// Not tracked
var author = context.Authors.AsNoTracking().FirstOrDefault(a => a.AuthorId == 1); 
author.Name = "Updated Author"; // Changes will not be automatically detected
// You need to use other methods to update or insert changes explicitly

So why not tracking everything?

For operations such as READ that wouldn’t need to be audited, we can remove the tracking so that the performance improves.

The tracking behavior can be controlled at various levels, including at the query level (AsNoTracking), globally for all queries (UseQueryTrackingBehavior), or when retrieving specific entities.

Tracking vs. No-Tracking Queries - EF Core

Adding SQL queries in Entity Framework Core

If there is at some point a real need to use SQL with EF Core, it is possible doing the following two methods:

Method 1:

• Create an empty migration
• In the Up/Down insert your SQL Code

using Microsoft.EntityFrameworkCore.Migrations;

public partial class YourMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Your other migration operations...

        // Execute a raw SQL statement
        migrationBuilder.Sql("INSERT INTO YourTable (ColumnName) VALUES ('SomeValue')");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Your migration rollback operations...

        // Execute a raw SQL statement for rollback
        migrationBuilder.Sql("DELETE FROM YourTable WHERE ColumnName = 'SomeValue'");
    }
}

Method 2:

• use context.FromSqlInterpolated

using (var context = new YourDbContext())
{
    // Define a parameterized SQL query using string interpolation
    var parameterValue = "SomeValue";
    var results = context.YourEntity
        .FromSqlInterpolated($"SELECT * FROM YourTable WHERE Column = {parameterValue}")
        .ToList();
}

Repository Pattern & Clean Architecture

Repository Pattern & Unit of work

EF Core uses the DbContext as a client to speak with the database.

Using a repository pattern enables you to:

• Test the DbContext
• Create specific methods for different classes (Repositories)

Implementing the Repository and Unit of Work Patterns in an ASP.NET MVC Application (9 of 10)

The Repository Pattern

GitHub - Gabegi/EF_Core-Repository-Pattern_RestAPI

1/Db Context

public class ApplicationDbContext: DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options): base(options)
    {
        
    }
    public DbSet<Villa> Villas { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

2/ Generic repository

public interface IRepository<T> where T : class
{
    Task<List<T>> GetAllAsync(Expression<Func<T, bool>>? filter = null, bool tracked = true);
    Task<T> GetAsync(Expression<Func<T, bool>>? filter = null, bool tracked = true);
    Task CreateAsync(T entity);
    Task RemoveAsync(T entity);
    Task SaveAsync();
}

In the context of a generic type constraint in C#, the where T : class constraint specifies that the generic type T must be a reference type (i.e., a class, interface, delegate, or array). It excludes value types like struct.

3/ Implementation

public class Repository<T> : IRepository<T> where T : class
{
    private readonly ApplicationDbContext _applicationDbContext;
    internal DbSet<T> _dbSet;

    public Repository(ApplicationDbContext applicationDbContext)
    {
        _applicationDbContext = applicationDbContext;
        _dbSet = _applicationDbContext.Set<T>();
    }


    public async Task CreateAsync(T entity)
    {
        await _dbSet.AddAsync(entity);
        await SaveAsync();
    }

    public async Task<T> GetAsync(Expression<Func<T, bool>> filter = null, bool tracked = true)
    {
        IQueryable<T> query = _dbSet;

        if (!tracked)
        {
            query = query.AsNoTracking();
        }

        if (filter != null)
        {
            query = query.Where(filter);
        }
        return await query.FirstOrDefaultAsync(); // query will be executed here, deffered execution
    }

    public async Task<List<T>> GetAllAsync(Expression<Func<T, bool>> filter = null, bool tracked = true)
    {
        IQueryable<T> query = _dbSet;

        if (!tracked)
        {
            query = query.AsNoTracking();
        }

        if (filter != null)
        {
            query = query.Where(filter);
        }
        return await query.ToListAsync(); // query will be executed here, deffered execution

    }

    public async Task RemoveAsync(T entity)
    {
        _dbSet.Remove(entity);
        await SaveAsync();
    }

    public async Task SaveAsync()
    {
        await _applicationDbContext.SaveChangesAsync();
    }
}

Now we have a generic class to interact with our DbContext, we can simply use it by passing a Class and a Db.

Which is great if we have many classes that use the same DbContext but also it enables to test the DbContext!

But what if we want something specific to one class?

This is where the unit of work comes in!

4/ Create Unit of work interface

 public interface IUnitOfWork
 {
     IVillaRepository Villa { get; }
     Task SaveAsync();
 }

5/ Implement it

public class UnitOfWork : IUnitOfWork
{
    private readonly ApplicationDbContext _db;
    public IVillaRepository Villa { get; private set; }

    public UnitOfWork(ApplicationDbContext db)
    {
        _db = db;
        Villa = new VillaRepository(_db);
    }

    public async Task SaveAsync()
    {
        await _db.SaveChangesAsync();
    }
} 

6/ Use it!

public async Task<ActionResult<APIResponse>> CreateVilla([FromBody]VillaCreateDTO createDto)
{
    // some code here

    await _unitOfWork.Villa.CreateAsync(model);

    // some code there

    return CreatedAtAction(nameof(GetVilla), new { id = model.Id }, _response);
}

Clean architecture and Entity Framework Core

Clean architecture is a method to keep your solution well organised.

There are many similar architectural patterns and clean architecture is one of them.

It is great to properly designing your application to good software engineering principles.

You might have seen this picture before

In Clean architecture, the DbContext is at the outer layer (in blue).

Therfore, one may ask:

Where do classes used to create the database tables with the DbContext should reside?

In Clean Architectue we learn that domain should reside at the core and not the outter layer. However, these classes are only meant to create the database and should therefore not be shared with the other layers and thus serve as entities for the database mapping.

Mapping between the data layer and the domain is done using a data mapper (e.g. AutoMapper)

Why and How to use Repository pattern with Clean Architecture in .Net?

The repository pattern provides an abstraction layer between the application’s business logic and the data access code, offering a clean and organised way to manage data access in an application.

You can read all about it in my other post with the respective GitHub Repo

Theory

Implementation

The Repo if you need the code

EF_Core-Repository-Pattern_RestAPI/BrandApplication at main ·…

Here is my full post on Repository Pattern!

Repository Pattern C# ultimate guide: Entity Framework Core, Clean Architecture, DTOs, Dependency…

Entity Framework Core Tips & Tricks

How to connect to SQL Server Mamagement Studio?

You can test locally using your local sql-server:

Go to SSMS (SQL Server Management Studio), don’t forget to install sql-express or to your server explorer in VS

click connect to database (plug icon)

Manually enter the sql-server name, then select the database name

• click Test Connection
• click on advanced to get your connection string
• click ok

add sql-connection string to appsettings.json

{
  "sqlDbConnectionString": "XXXXXXXXXX"
}

How to automatically create database diagrams in Visual Studio?

Go to Tools > Extensions > Download “EF Core Power Tools”

Once installed, right click on the project where your DbContext is at and select EF Core Power Tools

Select, Add DbContext Diagram

How to make custom changes to the database in EF Core?

This is what the OnModelCreating method is for.

It can add specific details to the table that aren’t available in annotations.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>().Property(b => b.Price).HasPrecision(10, 5);
}

How to remove a table with EF Core?

Just remove the DbSet<T>

E.g. here we comment it out and run a migration

public class ApplicationDbContext : DbContext
{

public DbSet<Book> Books { get; set; }
    //public DbSet<Genre> Genres { get; set; }

Resulting migration file

What is the dbo_EFMigrationsHistory file in SSMS?

Keeps track of all migrations

Stores migrations by MigrationId and EF Core Product/Version

The goal is to not apply the previous migration, keep track of changes and only apply changes that haven’t been pushing to the DB yet

What is the ApplicationDbContextModel Snapshot file?

It is for EF Core to track the changes between the code and the database

What is inside an EF Core migration file?

See migration file, e.g.:

Up indicates what to add

Down indicates what to revert (e.g. if something goes wrong)

The action creates a table:

• named “Books” as indicated in the DbSet<Book> Books
• The primary key is set as BookId
• All fields have nullable: false, this is because none properties have nullable (indicated with interrogation mark “?) such as

public string? Title { get; set; } // indicates nullable type

How to rename a column?

Column renamed

Empty migration file

Just remove the migration if that happens

Error List

Error

Introducing FOREIGN KEY constraint ‘FK_’ on table ‘tablename’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not create constraint or index. See previous errors.

Solution

I have seen many answers on this one, but here is the easiest solution that worked for me

Say you have these underlying tables, when Child1 and Child2 rows get deleted, you want the grandchild row to be deleted too, right?

Well the issue is with the parent, if it gets deleted, then the child gets deleted, then you have a conflic over who deletes the grandchild.

So the solution is to only have one child with Cascade.Delete

Enough explaining, below you can find the code

The below picture explains why it is not fine

Throws Error

On the other hand this is fine

Doesn’t throw error

internal class Parent
{
    [Key]
    public int ParentId{ get; set; }

    // Foreign Keys one to many
    public List<Child1>? Child1s{ get; set; }
    public List<Child2>? Child2s{ get; set; }
}

internal class Brand_FluentConfigurations : IEntityTypeConfiguration<Parent>
{
    public void Configure(EntityTypeBuilder<Parent> modelBuilder)
    {

        modelBuilder
            .HasMany<Child1>(b => b.Child1s)
            .WithOne(b => b.Parent)
            .HasForeignKey(b => b.ParentId)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder
            .HasMany<Child2>(b => b.Child2s)
            .WithOne(b => b.Parent)
            .HasForeignKey(b => b.ParentId)
            .OnDelete(DeleteBehavior.Cascade);
    }

Children

internal class Child1
{
    
    public int Child1Id{ get; set; }

    // Foreign Keys one to many
    public List<GrandChild>? GrandChildren { get; set; }
}

// If Child1 gets deleted, then GrandChil too
internal class BrandLine_FluentConfigurations : IEntityTypeConfiguration<Child1>
{
    public void Configure(EntityTypeBuilder<Child1> modelBuilder)
    {
        modelBuilder
            .HasMany<GrandChild>(b => b.GrandChildren)
            .WithOne(b => b.Child1)
            .HasForeignKey(b => b.Child1Id)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

internal class Child2
{
    
    public int Child2Id{ get; set; }

    // Foreign Keys one to many
    public List<GrandChild>? GrandChildren { get; set; }
}

// If Child2 gets deleted, then nothing happens (no action)
internal class BrandLine_FluentConfigurations : IEntityTypeConfiguration<Child2>
{
    public void Configure(EntityTypeBuilder<Child2> modelBuilder)
    {
        modelBuilder
            .HasMany<GrandChild>(b => b.GrandChildren)
            .WithOne(b => b.Child2)
            .HasForeignKey(b => b.Child2Id)
            .OnDelete(DeleteBehavior.NoAction);
    }
}

GrandChild

internal class GrandChild
{
    public int GrandChildId { get; set; }

    // Foreign Keys many to one
    public int Child1Id { get; set; }
    public Child1 Child1 { get; set; }
    public int? Child2Id { get; set; }
    public Child2? Child2{ get; set; }
}

Error

The term ‘add-migration’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Solution

Add SDK: Microsoft.EntityFrameworkCore.Tools

Error

Your startup project ‘Plutus.ProductPricing.API’ doesn’t reference Microsoft.EntityFrameworkCore.Design. This package is required for the Entity Framework Core Tools to work. Ensure your startup project is correct, install the package, and try again.

Solution

In your program.cs of your starting project

builder.Services.AddDbContext<ProductPricingDbContext>
    (options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

In your appsettings

  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=;Database=;TrustServerCertificate=True;Trusted_Connection=True;"
  }
}

In your DbContext

public class ProductPricingDbContext: DbContext
{

    public ProductPricingDbContext(DbContextOptions<ProductPricingDbContext> options): base(options)
    {
    }

Error

Only the invariant culture is supported in globalization-invariant mode. See https://aka.ms/GlobalizationInvariantMode for more information. (Parameter ‘name’)

en-us is an invalid culture identifier.

Solution

Change to false

EF Core 8: Only the invariant culture is supported in globalization-invariant mode · Issue #2239 ·…

Error

Unable to create a ‘DbContext’ of type ‘’. The exception ‘The method or operation is not implemented.’ was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

Solution

• Add the assembly name where the dbcontext is located in your DI

builder.Services.AddDbContext<ProductPricingDbContext>
    (options => options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection"), 
        b => b.MigrationsAssembly("Plutus.ProductPricing.DataAccess")));
// Location of your DbContext

• Make sure your DbContext is set right in your PMC as Default project

• Set your starting project as the one where the dependency injection of your Db Context is done

Error

Entity Framework Core Add-Migration doesn’t create any migration files and doesn’t create the migration folder

Solution

Remove this part in your project file

  <ItemGroup>
    <Compile Remove="Migrations\**" />
    <EmbeddedResource Remove="Migrations\**" />
    <None Remove="Migrations\**" />
  </ItemGroup>

Error

Unable to create a ‘DbContext’ of type ‘’. The exception ‘The entity type ‘Book’ requires a primary key to be defined. If you intended to use a keyless entity type, call ‘HasNoKey’ in ‘OnModelCreating’.

Solution

See section set Primary Key

Error

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_Books_Publishers_Publisher_Id”. The conflict occurred in database “CodingWiki”, table “dbo.Publishers”, column ‘Publisher_Id’.

Solution

This is a FK violation, look at the migration file:

• When we update data we set publisher_Id to 0
• That violates FK condition > publisher table doesn’t have a record with publisher_id 0
• It needs a publisher to add migration

Either:

• Seed solution with correct data so a publisher exists with publisher_id 0
• Or delete previous records that don’t have a publisher_id, then update database

Sources

https://www.udemy.com/course/entity-framework-core-the-complete-guide-net-5/

Annotations (Mapping attributes)

This model is built using a set of conventions — heuristics that look for common patterns. The model can then be customized using mapping attributes (also known as data annotations) and/or calls to the ModelBuilder methods (also known as fluent API) in OnModelCreating, both of which will override the configuration performed by conventions.

Fluent API configuration has the highest precedence and will override conventions and data annotations.

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .Property(b => b.Url)
            .IsRequired();
    }

Creating and Configuring a Model — EF Core

By convention, a property whose .NET type can contain null will be configured as optional, whereas properties whose .NET type cannot contain null will be configured as required. For example, all properties with .NET value types (int, decimal, bool, etc.) are configured as required, and all properties with nullable .NET value types (int?, decimal?, bool?, etc.) are configured as optional.

Entity Properties — EF Core

Primary keys and foreign keys

Keys — EF Core

Indexes

If you just want to enforce uniqueness on a column, define a unique index rather than an alternate key (see Indexes). In EF, alternate keys are read-only and provide additional semantics over unique indexes because they can be used as the target of a foreign key.

Indexes — EF Core

Performance

Efficient Querying — EF Core

Mapping attributes and foreign keys

Mapping attributes (aka Data Annotations) for relationships — EF Core