Managing data with EF Core

EF Core is an object-relational mapper (ORM) that uses ADO.NET underneath when working with SQL Server. Because it is a higher-level technology, it is not as efficient as using ADO.NET directly but it can be easier.

EF Core 7 targets .NET 6 so it can be used with both the Long Term Support (LTS) release of .NET 6 and the Standard Term Support (STS) release of .NET 7.

Understanding Entity Framework Core

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

There are two approaches to working with EF Core:

• Database First: A database already exists, so you build a model that matches its structure and features.
• Code First: No database exists, so you build a model and then use EF Core to create a database that matches its structure and features.

We will use EF Core with an existing database.

Scaffolding models using an existing database

Scaffolding is the process of using a tool to create classes that represent the model of an existing database using reverse engineering. A good scaffolding tool allows you to extend the automatically generated classes and then regenerate those classes without losing your extended classes.

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.

Setting up the dotnet-ef tool

.NET has a command-line tool named dotnet. It can be extended with capabilities useful for working with EF Core. It can perform design-time tasks like creating and applying migrations from an older model to a newer model and generating code for a model from an existing database.

The dotnet-ef command-line tool is not automatically installed. You must 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:

1. At a command prompt or terminal, check if you have already installed dotnet-ef as a global tool, as shown in the following command:

   dotnet tool list --global
   

2. Check in the list if an older version of the tool has been installed, like the one for .NET 5.0, as shown in the following output:

   Package Id      Version     Commands
   -------------------------------------
   dotnet-ef       5.0.0       dotnet-ef
   

3. If an old version is already installed, then uninstall the tool, as shown in the following command:

   dotnet tool uninstall --global dotnet-ef
   

4. Install the latest version, as shown in the following command:

   dotnet tool install --global dotnet-ef --version 7.0.0
   

5. If necessary, follow any OS-specific instructions to add the dotnet tools directory to your PATH environment variable, as described in the output of installing the dotnet-ef tool.

Defining EF Core models

EF Core uses a combination of conventions, annotation attributes, and Fluent API statements to build an entity model at runtime so that any actions performed on the classes can later be automatically translated into actions performed on the actual database. An entity class represents the structure of a table, and an instance of the class represents a row in that table.

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 code we will write will use the following conventions:

• 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 of 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).

  Good Practice: There are many other conventions that you should know, and you can even define your own, but that is beyond the scope of this book. You can read about them at the following link: https://docs.microsoft.com/en-us/ef/core/modeling/.

Using EF Core annotation attributes to define the model

Conventions often aren’t enough to completely map the classes to the database objects. A simple way of adding more smarts to your model is to apply annotation attributes.

Some common attributes are shown in the following table:

For example, in the database, the maximum length of a product name is 40, and the value cannot be null, as shown highlighted in the following DDL code that defines how to create a table named Products along with its columns, data types, keys, and other constraints:

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) 
);

In a Product class, we could apply attributes to specify this, as shown in the following code:

[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"
);

The Description column can be longer than the maximum 8,000 characters that can be stored in a nvarchar variable, so it needs to map to ntext instead, as shown in the following code:

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

Using the EF Core Fluent API to define the model

The last way that the model can be defined is by using the Fluent API. This API can be used instead of attributes, as well as being used in addition to them. For example, to define the ProductName property, instead of decorating the property with two attributes, an equivalent Fluent API statement could be written in the OnModelCreating method of the database context class, as shown in the following code:

modelBuilder.Entity<Product>()
  .Property(product => product.ProductName)
  .IsRequired() // only needed if you have disabled nullability checks
  .HasMaxLength(40);

This keeps the entity model class simpler. You will see an example of this in the coding task below.

Understanding data seeding with the Fluent API

Another benefit of the Fluent API is to provide initial data to populate a database. EF Core automatically works out what insert, update, or delete operations must be executed.

For example, if we wanted to make sure that a new database has at least one row in the Product table, then we would call the HasData method, as shown in the following code:

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

Our model will map to an existing database that is already populated with data, so we will not need to use this technique in our code.

Defining the Northwind database model

A Northwind class will be used to represent the database. To use EF Core, the class must inherit from DbContext. This class understands how to communicate with databases and dynamically generate SQL statements to query and manipulate data.

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

Inside your DbContext-derived class, you must define at least one property of the DbSet<T> type. These properties represent the tables. To tell EF Core what columns each table has, the DbSet<T> properties use generics to specify a class that represents a row in the table. That entity model class has properties that represent its columns.

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.

1. Use your preferred code editor to add a console app project, as defined in the following list:
   • Project template: Console App/console
   • Workspace/solution file and folder: Chapter02
   • Project file and folder: Northwind.Console.EFCore
2. In the Northwind.Console.EFCore project, treat warnings as errors, add package references to the EF Core data provider for SQL Server, and globally and statically import the System.Console class, as shown highlighted in the following markup:

   <Project Sdk="Microsoft.NET.Sdk">
     <PropertyGroup>
       <OutputType>Exe</OutputType>
       <TargetFramework>net7.0</TargetFramework>
       <ImplicitUsings>enable</ImplicitUsings>
       <Nullable>enable</Nullable>
       <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
     </PropertyGroup>
     <ItemGroup>
       <PackageReference
         Include="Microsoft.EntityFrameworkCore.Design" 
         Version="7.0.0" />
       <PackageReference
         Include="Microsoft.EntityFrameworkCore.SqlServer" 
         Version="7.0.0" />
     </ItemGroup>
     <ItemGroup>
       <Using Include="System.Console" Static="true" />
     </ItemGroup>
   </Project>
   

3. Build the project to restore packages.
4. At a command prompt or terminal in the Northwind.Console.EFCore folder, generate a model for all the tables in a new folder named Models, as shown in the following command:

   dotnet ef dbcontext scaffold "Data Source=.;Initial Catalog=Northwind;Integrated Security=true;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models --namespace Northwind.Console.EFCore.Models --data-annotations --context NorthwindDb
   

   Note the following:

   • The command action: dbcontext scaffold
   • The connection string: This will be different depending on if you are connecting to a local SQL Server (with or without an instance name) or Azure SQL Database.
   • The database provider: Microsoft.EntityFrameworkCore.SqlServer
   • The output folder: --output-dir Models
   • The namespace: --namespace Northwind.Console.EFCore.Models
   • The use of data annotations as well as the Fluent API: --data-annotations
   • Renaming the context from [database_name]Context: --context NorthwindDb

   If you are using Azure SQL Database or Azure SQL Edge, you will need to change the connection string appropriately.

5. Note the build messages and warnings, as shown in the following output:

   Build started...
   Build succeeded.
   To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
   

6. Open the Models folder and note the 25+ class files that were automatically generated.
7. Open Category.cs and note that it represents a row in the Categories table, as shown in the following code:

   using System;
   using System.Collections.Generic;
   using System.ComponentModel.DataAnnotations;
   using System.ComponentModel.DataAnnotations.Schema;
   using Microsoft.EntityFrameworkCore;
   namespace Northwind.Console.EFCore.Models
   {
     [Index("CategoryName", Name = "CategoryName")]
     public partial class Category
     {
       public Category()
       {
         Products = new HashSet<Product>();
       }
       [Key]
       public int CategoryId { get; set; }
       [StringLength(15)]
       public string CategoryName { get; set; } = null!;
       [Column(TypeName = "ntext")]
       public string? Description { get; set; }
       [Column(TypeName = "image")]
       public byte[]? Picture { get; set; }
       [InverseProperty("Category")]
       public virtual ICollection<Product> Products { get; set; }
     }
   }
   

   Note the following:

   • It decorates the entity class with the [Index] attribute that was introduced in EF Core 5.0. This indicates properties that should have an index. In earlier versions, only the Fluent API was supported for defining indexes. Since we are working with an existing database, this is not needed. But if we want to recreate a new empty database from our code, then this information will be used to create indexes.
   • The table name in the database is Categories but the dotnet-ef tool uses the Humanizer third-party library to automatically singularize the class name to Category, which is a more natural name when creating a single entity.
   • The entity class is declared using the partial keyword so that you can create a matching partial class for adding additional code. This allows you to rerun the tool and regenerate the entity class without losing that extra code.
   • The CategoryId property is decorated with the [Key] attribute to indicate that it is the primary key for this entity.
   • The Products property uses the [InverseProperty] attribute to define the foreign key relationship to the Category property on the Product entity class.

8. Open ProductsAboveAveragePrice.cs and note it represents a row returned by a database view rather than a table, so it is decorated with the [Keyless] attribute.
9. Open NorthwindDb.cs and review the class, as shown in the following edited-for-space code:

   using System;
   using System.Collections.Generic;
   using Microsoft.EntityFrameworkCore;
   using Microsoft.EntityFrameworkCore.Metadata;
   namespace Northwind.Console.EFCore.Models
   {
     public partial class NorthwindDb : DbContext
     {
       public NorthwindDb()
       {
       }
       public NorthwindDb(DbContextOptions<Northwind> options)
           : base(options)
       {
       }
       public virtual DbSet<AlphabeticalListOfProduct> 
         AlphabeticalListOfProducts { get; set; } = null!;
       public virtual DbSet<Category> Categories { get; set; } = null!;
       ...
       public virtual DbSet<Supplier> Suppliers { get; set; } = null!;
       public virtual DbSet<Territory> Territories { get; set; } = null!;
       protected override void OnConfiguring(
         DbContextOptionsBuilder optionsBuilder)
       {
         if (!optionsBuilder.IsConfigured)
         {
   #warning To protect potentially sensitive ...
           optionsBuilder.UseSqlServer("Data Source=.;Initial Catalog=Northwind;Integrated Security=true;TrustServerCertificate=true;");
         }
       }
       protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
         modelBuilder.Entity<AlphabeticalListOfProduct>(entity =>
         {
           entity.ToView("Alphabetical list of products");
         });
         ...
         modelBuilder.Entity<Product>(entity =>
         {
           entity.Property(e => e.ReorderLevel).HasDefaultValueSql("((0))");
           entity.Property(e => e.UnitPrice).HasDefaultValueSql("((0))");
           entity.Property(e => e.UnitsInStock).HasDefaultValueSql("((0))");
           entity.Property(e => e.UnitsOnOrder).HasDefaultValueSql("((0))");
           entity.HasOne(d => d.Category)
                     .WithMany(p => p.Products)
                     .HasForeignKey(d => d.CategoryId)
                     .HasConstraintName("FK_Products_Categories");
           entity.HasOne(d => d.Supplier)
                     .WithMany(p => p.Products)
                     .HasForeignKey(d => d.SupplierId)
                     .HasConstraintName("FK_Products_Suppliers");
         });
         ...
         OnModelCreatingPartial(modelBuilder);
       }
       partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
     }
   }
   

   Note the following:

   • The NorthwindDb data context class is partial to allow you to extend it and regenerate it in the future. We used the name NorthwindDb because Northwind is used for a namespace.
   • NorthwindDb has two constructors: a default parameter-less one and one that allows options to be passed in. This is useful in apps where you want to specify the connection string at runtime.
   • The DbSet<T> properties that represent tables are set to the null-forgiving value to prevent static compiler analysis warnings at compile time. It has no effect at runtime.
   • In the OnConfiguring method, if options have not been specified in the constructor, then it defaults to using the connection string used during scaffolding. It has a compiler warning to remind you that you should not hardcode security information in this connection string.
   • In the OnModelCreating method, the Fluent API is used to configure the entity classes, and then a partial method named OnModelCreatingPartial is invoked. This allows you to implement that partial method in your own partial Northwind class to add your own Fluent API configuration, which will not be lost if you regenerate the model classes.

10. Delete the #warning statement. We are treating warnings as errors so we cannot leave this in.
11. Close the automatically generated class files.

Querying the Northwind model

Now we can query the model:

1. In Program.cs, delete the existing statements. Add statements to create an instance of the NorthwindDb data context class and use it to query the products table for those that cost more than a given price, as shown in the following code:

   using Microsoft.Data.SqlClient; // SqlConnectionStringBuilder
   using Microsoft.EntityFrameworkCore; // ToQueryString, GetConnectionString
   using Northwind.Console.EFCore.Models; // NorthwindDb
   SqlConnectionStringBuilder builder = new();
   builder.InitialCatalog = "Northwind";
   builder.MultipleActiveResultSets = true;
   builder.Encrypt = true;
   builder.TrustServerCertificate = true;
   builder.ConnectTimeout = 10;
   WriteLine("Connect to:");
   WriteLine("  1 - SQL Server on local machine");
   WriteLine("  2 - Azure SQL Database");
   WriteLine("  3 - Azure SQL Edge");
   WriteLine();
   Write("Press a key: ");
   ConsoleKey key = ReadKey().Key;
   WriteLine(); WriteLine();
   if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
   {
     builder.DataSource = "."; // Local SQL Server
     // @".\net7book"; // Local SQL Server with an instance name
   }
   else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
   {
     builder.DataSource = // Azure SQL Database
       "tcp:apps-services-net7.database.windows.net,1433";
   }
   else if (key is ConsoleKey.D3 or ConsoleKey.NumPad3)
   {
     builder.DataSource = "tcp:127.0.0.1,1433"; // Azure SQL Edge
   }
   else
   {
     WriteLine("No data source selected.");
     return;
   }
   WriteLine("Authenticate using:");
   WriteLine("  1 - Windows Integrated Security");
   WriteLine("  2 - SQL Login, for example, sa");
   WriteLine();
   Write("Press a key: ");
   key = ReadKey().Key;
   WriteLine(); WriteLine();
   if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
   {
     builder.IntegratedSecurity = true;
   }
   else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
   {
     builder.UserID = "sa"; // Azure SQL Edge
                            // "markjprice"; // change to your username
     Write("Enter your SQL Server password: ");
     string? password = ReadLine();
     if (string.IsNullOrWhiteSpace(password))
     {
       WriteLine("Password cannot be empty or null.");
       return;
     }
     builder.Password = password;
     builder.PersistSecurityInfo = false;
   }
   else
   {
     WriteLine("No authentication selected.");
     return;
   }
   DbContextOptionsBuilder<NorthwindDb> options = new();
   options.UseSqlServer(builder.ConnectionString);
   using (NorthwindDb db = new(options.Options))
   {
     Write("Enter a unit price: ");
     string? priceText = ReadLine();
     if (!decimal.TryParse(priceText, out decimal price))
     {
       WriteLine("You must enter a valid unit price.");
       return;
     }
     // We have to use var because we are projecting into an anonymous type.
     var products = db.Products
       .Where(p => p.UnitPrice > price)
       .Select(p => new { p.ProductId, p.ProductName, p.UnitPrice });
     WriteLine("----------------------------------------------------------");
     WriteLine("| {0,5} | {1,-35} | {2,8} |", "Id", "Name", "Price");
     WriteLine("----------------------------------------------------------");
     foreach (var p in products)
     {
       WriteLine("| {0,5} | {1,-35} | {2,8:C} |",
         p.ProductId, p.ProductName, p.UnitPrice);
     }
     WriteLine("----------------------------------------------------------");
     WriteLine(products.ToQueryString());
     WriteLine();
     WriteLine($"Provider:   {db.Database.ProviderName}");
     WriteLine($"Connection: {db.Database.GetConnectionString()}");
   }
   

2. Run the console app and note the results, as shown in the following partial output:

   Enter a unit price: 60
   ----------------------------------------------------------
   |    Id | Name                                |    Price |
   ----------------------------------------------------------
   |     9 | Mishi Kobe Niku                     |   £97.00 |
   |    18 | Carnarvon Tigers                    |   £62.50 |
   |    20 | Sir Rodney's Marmalade              |   £81.00 |
   |    29 | Thüringer Rostbratwurst             |  £123.79 |
   |    38 | Côte de Blaye                       |  £263.50 |
   ----------------------------------------------------------
   DECLARE @__price_0 decimal(2) = 60.0;
   SELECT [p].[ProductId], [p].[ProductName], [p].[UnitPrice]
   FROM [Products] AS [p]
   WHERE [p].[UnitPrice] > @__price_0
   Provider:   Microsoft.EntityFrameworkCore.SqlServer
   Connection: Data Source=tcp:apps-services-net7.database.windows.net,1433;Initial Catalog=Northwind;Persist Security Info=False;User ID=markjprice;Password=s3cret-Ninja;Multiple Active Result Sets=False;Encrypt=True;Trust Server Certificate=False;Connection Timeout=10;