Understanding legacy Entity Framework

ORMs use a mapping definition to associate columns in tables to properties in classes. Then, a programmer can interact with objects of different types in a way that they are familiar with, instead of having to deal with knowing how to store the values in a relational table or another structure provided by a NoSQL data store.

Using the legacy Entity Framework 6.3 or later

<PackageReference Include="EntityFramework" Version="6.4.4" />

Understanding Entity Framework Core

EF Core 5 and later only support .NET 5 and later. EF Core 3.0 and later only run on platforms that support .NET Standard 2.1, meaning .NET Core 3.0 and later. It does not support .NET Standard 2.0 platforms like .NET Framework 4.8.

As well as traditional RDBMSs, EF Core supports modern cloud-based, nonrelational, schema-less data stores, such as Microsoft Azure Cosmos DB and MongoDB, sometimes with third-party providers.

EF Core has so many improvements that this chapter cannot cover them all. I will focus on the fundamentals that all .NET developers should know and some of the cooler new features.

There are two approaches to working with EF Core:

We will start by using EF Core with an existing database.

Creating a console app for working with EF Core

1. Use your preferred code editor to create a new solution/workspace named Chapter10.
2. Add a console app project, as defined in the following list:
   1. Project template: Console Application / console
   2. Workspace/solution file and folder: Chapter10
   3. Project file and folder: WorkingWithEFCore

Using a sample relational database

Let's take a minute to look at a diagram of the Northwind database. You can use the following diagram to refer to as we write code and queries throughout this book:

Figure 10.1: The Northwind database tables and relationships

• Each category has a unique identifier, name, description, and picture.
• Each product has a unique identifier, name, unit price, units in stock, and other fields.
• Each product is associated with a category by storing the category's unique identifier.
• The relationship between Categories and Products is one-to-many, meaning each category can have zero or more products.

Using Microsoft SQL Server for Windows

Downloading and installing SQL Server

Creating the Northwind sample database for SQL Server

Managing the Northwind sample database with Server Explorer

Using SQLite

Setting up SQLite for macOS

Setting up SQLite for Windows

Setting up SQLite for other OSes

Managing the Northwind sample database with SQLiteStudio

Setting up EF Core

Choosing an EF Core database provider

To manage data in a specific database, we need classes that know how to efficiently talk to that database.

They are distributed as NuGet packages, as shown in the following table:

You can install as many EF Core database providers in the same project as you need. Each package includes the shared types as well as provider-specific types.

Connecting to a database

To connect to an SQLite database, we just need to know the database filename, set using the parameter Filename.

• The name of the server (and the instance if it has one).
• The name of the database.
• Security information, such as username and password, or if we should pass the currently logged-on user's credentials automatically.

For backward compatibility, there are multiple possible keywords we can use in an SQL Server connection string for the various parameters, as shown in the following list:

• Data Source or server or addr: These keywords are the name of the server (and an optional instance). You can use a dot . to mean the local server.
• Initial Catalog or database: These keywords are the name of the database.
• Integrated Security or trusted_connection: These keywords are set to true or SSPI to pass the thread's current user credentials.
• MultipleActiveResultSets: This keyword is set to true to enable a single connection to be used to work with multiple tables simultaneously to improve efficiency. It is used for lazy loading rows from related tables.

Defining the Northwind database context class

Your DbContext-derived class should have an overridden method named OnConfiguring, which will set the database connection string.

To make it easy for you to try SQLite and SQL Server, we will create a project that supports both, with a string field to control which is used at runtime:

Defining EF Core models

First, we will review the three ways to define a model, with code examples, and then we will create some classes that implement those techniques.

Using EF Core conventions to define the model

• The name of a table is assumed to match the name of a DbSet<T> property in the DbContext class, for example, Products.
• The names of the columns are assumed to match the names of properties in the entity model class, for example, ProductId.
• The string .NET type is assumed to be a nvarchar type in the database.
• The int .NET type is assumed to be an int type in the database.
• The primary key is assumed to be a property that is named Id or ID, or when the entity model class is named Product, then the property can be named ProductId or ProductID. If this property is an integer type or the Guid type, then it is also assumed to be an IDENTITY column (a column type that automatically assigns a value when inserting).

Some common attributes are shown in the following table:

CREATE TABLE Products (
    ProductId       INTEGER       PRIMARY KEY,
    ProductName     NVARCHAR (40) NOT NULL,
    SupplierId      "INT",
    CategoryId      "INT",
    QuantityPerUnit NVARCHAR (20),
    UnitPrice       "MONEY"       CONSTRAINT DF_Products_UnitPrice DEFAULT (0),
    UnitsInStock    "SMALLINT"    CONSTRAINT DF_Products_UnitsInStock DEFAULT (0),
    UnitsOnOrder    "SMALLINT"    CONSTRAINT DF_Products_UnitsOnOrder DEFAULT (0),
    ReorderLevel    "SMALLINT"    CONSTRAINT DF_Products_ReorderLevel DEFAULT (0),
    Discontinued    "BIT"         NOT NULL
                                  CONSTRAINT DF_Products_Discontinued DEFAULT (0),
    CONSTRAINT FK_Products_Categories FOREIGN KEY (
        CategoryId
    )
    REFERENCES Categories (CategoryId),
    CONSTRAINT FK_Products_Suppliers FOREIGN KEY (
        SupplierId
    )
    REFERENCES Suppliers (SupplierId),
    CONSTRAINT CK_Products_UnitPrice CHECK (UnitPrice >= 0),
    CONSTRAINT CK_ReorderLevel CHECK (ReorderLevel >= 0),
    CONSTRAINT CK_UnitsInStock CHECK (UnitsInStock >= 0),
    CONSTRAINT CK_UnitsOnOrder CHECK (UnitsOnOrder >= 0) 
);

[Required] 
[StringLength(40)]
public string ProductName { get; set; }

When there isn't an obvious map between .NET types and database types, an attribute can be used.

For example, in the database, the column type of UnitPrice for the Products table is money. .NET does not have a money type, so it should use decimal instead, as shown in the following code:

[Column(TypeName = "money")]
public decimal? UnitPrice { get; set; }

Another example is for the Categories table, as shown in the following DDL code:

CREATE TABLE Categories (
    CategoryId   INTEGER       PRIMARY KEY,
    CategoryName NVARCHAR (15) NOT NULL,
    Description  "NTEXT",
    Picture      "IMAGE"
);

[Column(TypeName = "ntext")]
public string Description { get; set; }

Using the EF Core Fluent API to define the model

modelBuilder.Entity<Product>()
  .Property(product => product.ProductName)
  .IsRequired()
  .HasMaxLength(40);

This keeps the entity model class simpler.

Understanding data seeding with the Fluent API

modelBuilder.Entity<Product>()
  .HasData(new Product
  {
    ProductId = 1,
    ProductName = "Chai",
    UnitPrice = 8.99M
  });

Building an EF Core model for the Northwind tables

1. In the WorkingWithEFCore project, add two class files named Category.cs and Product.cs.
2. In Category.cs, define a class named Category, as shown in the following code:

   namespace Packt.Shared;
   public class Category
   {
   }
   

3. In Product.cs, define a class named Product, as shown in the following code:

   namespace Packt.Shared;
   public class Product
   {
   }
   

Defining the Category and Product entity classes

CREATE TABLE Categories (
    CategoryId   INTEGER       PRIMARY KEY,
    CategoryName NVARCHAR (15) NOT NULL,
    Description  "NTEXT",
    Picture      "IMAGE"
);

We will use conventions to define:

• Three of the four properties (we will not map the Picture column).
• The primary key.
• The one-to-many relationship to the Products table.

To map the Description column to the correct database type, we will need to decorate the string property with the Column attribute.

Let's go:

The two properties that relate the two entities, Category.Products and Product.Category, are both marked as virtual. This allows EF Core to inherit and override the properties to provide extra features, such as lazy loading.

Adding tables to the Northwind database context class

The DbContext-derived class can optionally have an overridden method named OnModelCreating. This is where you can write Fluent API statements as an alternative to decorating your entity classes with attributes.

Let's write some code:

1. Modify the Northwind class to add statements to define two properties for the two tables and an OnModelCreating method, as shown highlighted in the following code:

   public class Northwind : DbContext
   {
     // these properties map to tables in the database
     public DbSet<Category>? Categories { get; set; }
     public DbSet<Product>? Products { get; set; }
     protected override void OnConfiguring(
       DbContextOptionsBuilder optionsBuilder)
     {
       ...
     }
     protected override void OnModelCreating(
       ModelBuilder modelBuilder)
     {
       // example of using Fluent API instead of attributes
       // to limit the length of a category name to 15
       modelBuilder.Entity<Category>()
         .Property(category => category.CategoryName)
         .IsRequired() // NOT NULL
         .HasMaxLength(15);
       if (ProjectConstants.DatabaseProvider == "SQLite")
       {
         // added to "fix" the lack of decimal support in SQLite
         modelBuilder.Entity<Product>()
           .Property(product => product.Cost)
           .HasConversion<double>();
       }
     }
   }
   

Setting up the dotnet-ef tool

The dotnet ef command-line tool is not automatically installed. You have to install this package as either a global or local tool. If you have already installed an older version of the tool, then you should uninstall any existing version:

Scaffolding models using an existing database

If you know that you will never regenerate the classes using the tool, then feel free to change the code for the automatically generated classes as much as you want. The code generated by the tool is just the best approximation.

Let's see if the tool generates the same model as we did manually:

1. Add the Microsoft.EntityFrameworkCore.Design package to the WorkingWithEFCore project.
2. At a command prompt or terminal in the WorkingWithEFCore folder, generate a model for the Categories and Products tables in a new folder named AutoGenModels, as shown in the following command:

   dotnet ef dbcontext scaffold "Filename=Northwind.db" Microsoft.EntityFrameworkCore.Sqlite --table Categories --table Products --output-dir AutoGenModels --namespace WorkingWithEFCore.AutoGen --data-annotations --context Northwind
   

   Note the following:

   • The command action: dbcontext scaffold
   • The connection string: "Filename=Northwind.db"
   • The database provider: Microsoft.EntityFrameworkCore.Sqlite
   • The tables to generate models for: --table Categories --table Products
   • The output folder: --output-dir AutoGenModels
   • The namespace: --namespace WorkingWithEFCore.AutoGen
   • To use data annotations as well as the Fluent API: --data-annotations
   • To rename the context from [database_name]Context: --context Northwind

   For SQL Server, change the database provider and connection string, as shown in the following command:

   dotnet ef dbcontext scaffold "Data Source=.;Initial Catalog=Northwind;Integrated Security=true;" Microsoft.EntityFrameworkCore.SqlServer --table Categories --table Products --output-dir AutoGenModels --namespace WorkingWithEFCore.AutoGen --data-annotations --context Northwind
   

Configuring preconvention models

As models become more complex, relying on conventions to discover entity types and their properties and successfully map them to tables and columns becomes harder. It would be useful if you could configure the conventions themselves before they are used to analyze and build a model.

protected override void ConfigureConventions(
  ModelConfigurationBuilder configurationBuilder)
{
  configurationBuilder.Properties<string>().HaveMaxLength(50);
  configurationBuilder.IgnoreAny<IDoNotMap>();
}

In the rest of this chapter, we will use the classes that you manually created.

Querying EF Core models

For now, just write the code and view the results:

Filtering included entities

Unicode characters in the Windows console

chcp 65001

Filtering and sorting products

Getting the generated SQL

1. In the FilteredIncludes method, before using the foreach statement to enumerate the query, add a statement to output the generated SQL, as shown highlighted in the following code:

   WriteLine($"ToQueryString: {categories.ToQueryString()}");
   foreach (Category c in categories)
   

2. In Program.cs, comment out the call to the QueryingProducts method and uncomment the call to the FilteredIncludes method.
3. Run the code, enter a minimum for units in stock like 99, and view the result (when run with SQLite), as shown in the following output:

   Enter a minimum for units in stock: 99 
   Using SQLite database provider.
   ToQueryString: .param set @_stock_0 99
   SELECT "c"."CategoryId", "c"."CategoryName", "c"."Description", 
   "t"."ProductId", "t"."CategoryId", "t"."UnitPrice", "t"."Discontinued", 
   "t"."ProductName", "t"."UnitsInStock"
   FROM "Categories" AS "c" 
   LEFT JOIN (
       SELECT "p"."ProductId", "p"."CategoryId", "p"."UnitPrice",
   "p"."Discontinued", "p"."ProductName", "p"."UnitsInStock" 
       FROM "Products" AS "p"
       WHERE ("p"."UnitsInStock" >= @_stock_0)
   ) AS "t" ON "c"."CategoryId" = "t"."CategoryId" 
   ORDER BY "c"."CategoryId", "t"."ProductId"
   Beverages has 2 products with a minimum of 99 units in stock.
     Sasquatch Ale has 111 units in stock. 
     Rhönbräu Klosterbier has 125 units in stock.
   ...
   

Note the SQL parameter named @_stock_0 has been set to a minimum stock value of 99.

Enter a minimum for units in stock: 99
Using SqlServer database provider.
ToQueryString: DECLARE @__stock_0 smallint = CAST(99 AS smallint);
SELECT [c].[CategoryId], [c].[CategoryName], [c].[Description], [t].[ProductId], [t].[CategoryId], [t].[UnitPrice], [t].[Discontinued], [t].[ProductName], [t].[UnitsInStock]
FROM [Categories] AS [c]
LEFT JOIN (
    SELECT [p].[ProductId], [p].[CategoryId], [p].[UnitPrice], [p].[Discontinued], [p].[ProductName], [p].[UnitsInStock]
    FROM [Products] AS [p]
    WHERE [p].[UnitsInStock] >= @__stock_0
) AS [t] ON [c].[CategoryId] = [t].[CategoryId]
ORDER BY [c].[CategoryId], [t].[ProductId]

Logging EF Core using a custom logging provider

To monitor the interaction between EF Core and the database, we can enable logging. This requires the following two tasks:

• The registering of a logging provider.
• The implementation of a logger.

Filtering logs by provider-specific values

1. Modify the Log method in ConsoleLogger to only output events with an Id of 20100, as highlighted in the following code:

   public void Log<TState>(LogLevel logLevel, EventId eventId,
     TState state, Exception? exception,
     Func<TState, Exception, string> formatter)
   {
     if (eventId.Id == 20100)
     {
       // log the level and event identifier
       Write("Level: {0}, Event Id: {1}, Event: {2}",
         logLevel, eventId.Id, eventId.Name);
       // only output the state or exception if it exists
       if (state != null)
       {
         Write($", State: {state}");
       }
       if (exception != null)
       {
         Write($", Exception: {exception.Message}");
       }
       WriteLine();
     }
   }
   

2. In Program.cs, uncomment the QueryingCategories method and comment out the other methods so that we can monitor the SQL statements that are generated when joining two tables.
3. Run the code, and note the following SQL statements that were logged, as shown in the following output that has been edited for space:

   Using SQLServer database provider.
   Categories and how many products they have:
   Level: Debug, Event Id: 20100, State: Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
   SELECT [c].[CategoryId], [c].[CategoryName], [c].[Description], [p].[ProductId], [p].[CategoryId], [p].[UnitPrice], [p].[Discontinued], [p].[ProductName], [p].[UnitsInStock]
   FROM [Categories] AS [c]
   LEFT JOIN [Products] AS [p] ON [c].[CategoryId] = [p].[CategoryId]
   ORDER BY [c].[CategoryId], [p].[ProductId]
   Beverages has 12 products.
   Condiments has 12 products.
   Confections has 13 products.
   Dairy Products has 10 products.
   Grains/Cereals has 7 products.
   Meat/Poultry has 6 products.
   Produce has 5 products.
   Seafood has 12 products.
   

Logging with query tags

You can annotate a LINQ query using the TagWith method, as shown in the following code:

IQueryable<Product>? products = db.Products?
  .TagWith("Products filtered by price and sorted.")
  .Where(product => product.Cost > price)
  .OrderByDescending(product => product.Cost);

This will add an SQL comment to the log, as shown in the following output:

-- Products filtered by price and sorted.

Pattern matching with Like

Defining global filters

1. In Northwind.cs, modify the OnModelCreating method to add a global filter to remove discontinued products, as shown highlighted in the following code:

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
     ...
     // global filter to remove discontinued products
     modelBuilder.Entity<Product>()
       .HasQueryFilter(p => !p.Discontinued);
   }
   

2. Run the code, enter the partial product name che, view the result, and note that Chef Anton's Gumbo Mix is now missing, because the SQL statement generated includes a filter for the Discontinued column, as shown highlighted in the following output:

   SELECT "p"."ProductId", "p"."CategoryId", "p"."UnitPrice",
   "p"."Discontinued", "p"."ProductName", "p"."UnitsInStock" 
   FROM "Products" AS "p"
   WHERE ("p"."Discontinued" = 0) AND "p"."ProductName" LIKE @__Format_1 
   Chef Anton's Cajun Seasoning has 53 units in stock. Discontinued? False 
   Queso Manchego La Pastora has 86 units in stock. Discontinued? False 
   Gumbär Gummibärchen has 15 units in stock. Discontinued? False
   

Loading patterns with EF Core

• Eager loading: Load data early.
• Lazy loading: Load data automatically just before it is needed.
• Explicit loading: Load data manually.

In this section, we're going to introduce each of them.

Eager loading entities

This works because when we wrote the query, we enabled eager loading by calling the Include method for the related products.

Let's see what happens if we do not call Include:

1. Modify the query to comment out the Include method call, as shown in the following code:

   IQueryable<Category>? categories =
     db.Categories; //.Include(c => c.Products);
   

2. In Program.cs, comment out all methods except QueryingCategories.
3. Run the code and view the result, as shown in the following partial output:

   Beverages has 0 products. 
   Condiments has 0 products. 
   Confections has 0 products.
   Dairy Products has 0 products. 
   Grains/Cereals has 0 products. 
   Meat/Poultry has 0 products.
   Produce has 0 products. 
   Seafood has 0 products.
   

Each item in foreach is an instance of the Category class, which has a property named Products, that is, the list of products in that category. Since the original query is only selected from the Categories table, this property is empty for each category.

Enabling lazy loading

• Reference a NuGet package for proxies.
• Configure lazy loading to use a proxy.

Let's see this in action:

Explicit loading entities

Categories and how many products they have:
Enable eager loading? (Y/N): n 
Enable explicit loading? (Y/N): y
Level: Debug, Event Id: 20100, State: Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "c"."CategoryId", "c"."CategoryName", "c"."Description" FROM "Categories" AS "c"
Explicitly load products for Beverages? (Y/N): y
Level: Debug, Event Id: 20100, State: Executing DbCommand [Parameters=[@ p_0='?'], CommandType='Text', CommandTimeout='30'] 
SELECT "p"."ProductId", "p"."CategoryId", "p"."UnitPrice",
"p"."Discontinued", "p"."ProductName", "p"."UnitsInStock"
FROM "Products" AS "p"
WHERE ("p"."Discontinued" = 0) AND ("p"."CategoryId" = @ p_0)
Beverages has 11 products.
Explicitly load products for Condiments? (Y/N): n 
Condiments has 0 products.
Explicitly load products for Confections? (Y/N): n 
Confections has 0 products.
Explicitly load products for Dairy Products? (Y/N): n 
Dairy Products has 0 products.
Explicitly load products for Grains/Cereals? (Y/N): n 
Grains/Cereals has 0 products.
Explicitly load products for Meat/Poultry? (Y/N): n 
Meat/Poultry has 0 products.
Explicitly load products for Produce? (Y/N): n 
Produce has 0 products.
Explicitly load products for Seafood? (Y/N): y
Level: Debug, Event ID: 20100, State: Executing DbCommand [Parameters=[@ p_0='?'], CommandType='Text', CommandTimeout='30'] 
SELECT "p"."ProductId", "p"."CategoryId", "p"."UnitPrice",
"p"."Discontinued", "p"."ProductName", "p"."UnitsInStock"
FROM "Products" AS "p"
WHERE ("p"."Discontinued" = 0) AND ("p"."CategoryId" = @ p_0) 
Seafood has 12 products.

Inserting entities

Updating entities

Now, let's modify an existing row in a table:

Deleting entities

Now let's see how to delete rows from a table:

If multiple product names started with Bob, then they are all deleted. As an optional challenge, modify the statements to add three new products that start with Bob and then delete them.

Pooling database contexts

A feature of ASP.NET Core that is related to EF Core is that it makes your code more efficient by pooling database contexts when building websites and services. This allows you to create and dispose of as many DbContext-derived objects as you want, knowing that your code is still as efficient as possible.

Working with transactions

Transactions are ACID, which is an acronym explained in the following list:

• A is for atomic. Either all the operations in the transaction commit, or none of them do.
• C is for consistent. The state of the database before and after a transaction is consistent. This is dependent on your code logic; for example, when transferring money between bank accounts, it is up to your business logic to ensure that if you debit $100 in one account, you credit $100 in the other account.
• I is for isolated. During a transaction, changes are hidden from other processes. There are multiple isolation levels that you can pick from (refer to the following table). The stronger the level, the better the integrity of the data. However, more locks must be applied, which will negatively affect other processes. Snapshot is a special case because it creates multiple copies of rows to avoid locks, but this will increase the size of your database while transactions occur.
• D is for durable. If a failure occurs during a transaction, it can be recovered. This is often implemented as a two-phase commit and transaction logs. Once the transaction is committed it is guaranteed to endure even if there are subsequent errors. The opposite of durable is volatile.

Controlling transactions using isolation levels

Defining an explicit transaction

Code First EF Core models

For example, we might need to create an application for managing students and courses for an academy. One student can sign up to attend multiple courses. One course can be attended by multiple students. This is an example of a many-to-many relationship between students and courses.

Let's model this example:

Figure 10.6: Viewing the Academy database in SQL Server using Visual Studio 2022 Server Explorer

Understanding migrations