Prepare to access SQL Server using Entity Framework Core (database-first)

Operating environment

Visual Studio

• Visual Studio 2022

.NET

• .NET 8

Entity Framework Core

• Entity Framework Core 8.0

SQL Server

• SQL Server 2022

* The above is a verification environment, but it may work with other versions.

At first

Entity Framework Core is a library of O/R mappers. When accessing the database, you can access the records of the database through models (classes) defined in code, without using SQL statements directly. This provides the following benefits:

• Since SQL statements are not written directly, security risks such as SQL injection are reduced.
• Since SQL statements are strings, even if you make a mistake in the syntax, it is not checked for build errors, but since the model is a program syntax, you can check for mistakes at build time.

Entity Framework Core can automatically generate code from existing databases to connect to these models and databases. Conversely, there is a way to write the code manually and then automatically generate the database.

The former is called "database-first" and the latter is called "code-first". There's also "model-first," which generates code and databases from blueprints like ER diagrams, but it's not widely used in Entity Framework Core.

In this case, we will use a "database-first" pattern that generates code on the assumption that the database already exists.

SQL Server Setup

In order to access the SQL Server database in this tip, please set up SQL Server in advance. It can be set up on a PC in the development environment or on another PC via the network. If you can connect to SQL Server from your development environment, you're good to go. In this tip, SQL Server is installed in a separate environment.

The SQL Server setup steps are omitted because they are redundant. The following pages contain SQL Server-related tips, so if you want to know how to set it up, please refer to it.

• SQL Server Tips

Create a table

This time, we will create the following database and table as a Mr./Ms..

• Database name: TestDatabase
• Table name: User
• Table columns: [ID], [Name], [Password], [Age], [Email], [Birthday], [UpdateDateTime]

You can create it in any way, but if you don't want to do it, run the following SQL against SQL Server to generate it.

The following is database creation SQL, but since the path of database creation changes depending on the version, etc., it may be more reliable to create it with GUI or command instead of SQL.

USE [master]
GO
CREATE DATABASE [TestDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\TestDatabase.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'TestDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\TestDatabase_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [TestDatabase] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestDatabase].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TestDatabase] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [TestDatabase] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [TestDatabase] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [TestDatabase] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [TestDatabase] SET ARITHABORT OFF 
GO
ALTER DATABASE [TestDatabase] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [TestDatabase] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [TestDatabase] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [TestDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [TestDatabase] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [TestDatabase] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [TestDatabase] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [TestDatabase] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [TestDatabase] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [TestDatabase] SET  DISABLE_BROKER 
GO
ALTER DATABASE [TestDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [TestDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [TestDatabase] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [TestDatabase] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [TestDatabase] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [TestDatabase] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [TestDatabase] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [TestDatabase] SET RECOVERY FULL 
GO
ALTER DATABASE [TestDatabase] SET  MULTI_USER 
GO
ALTER DATABASE [TestDatabase] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [TestDatabase] SET DB_CHAINING OFF 
GO
ALTER DATABASE [TestDatabase] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [TestDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [TestDatabase] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [TestDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'TestDatabase', N'ON'
GO
ALTER DATABASE [TestDatabase] SET QUERY_STORE = ON
GO
ALTER DATABASE [TestDatabase] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO
ALTER DATABASE [TestDatabase] SET  READ_WRITE 
GO

Table creation SQL.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
  [ID] [int] NOT NULL,
  [Name] [nvarchar](20) NOT NULL,
  [Password] [nvarchar](20) NOT NULL,
  [Age] [int] NULL,
  [Email] [nvarchar](200) NULL,
  [Birthday] [date] NULL,
  [UpdateDateTime] [datetime2](7) NULL,
  CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Record Append SQL.

USE [TestDatabase]
GO
INSERT [dbo].[User] ([ID], [Name], [Password], [Age], [Email], [Birthday], [UpdateDateTime]) VALUES (1, N'氏名１', N'aaaa', 20, N'aaaa@example.com', CAST(N'2020-04-01' AS Date), CAST(N'2021-03-14T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[User] ([ID], [Name], [Password], [Age], [Email], [Birthday], [UpdateDateTime]) VALUES (2, N'氏名２', N'bbbb', 30, N'bbbb@example.com', CAST(N'2010-04-01' AS Date), CAST(N'2021-03-14T00:00:00.0000000' AS DateTime2))
GO

Visual Studio Setup

We assume that you have already set this up as well. If you want to know the setup procedure, etc., it is summarized on the following page.

• Visual Studio Tips

Create a project

Entity Framework Core is not dependent on any particular execution environment, so it can be used in many projects. In this article, we'll use Entity Framework Core in a simple console application environment.

In the new project, select Console App.

The project has been created. It doesn't matter what the project name is.

Get the Entity Framework Core package

Get a package for using Entity Framework Core with NuGet.

Right-click the dependency and select Manage NuGet Packages.

With "Browse" selected from the tab, enter in the EntityFrameworkCore search field. You should then see the Entity Framework Core-related packages in the list.

Since we will use SQL Server this time, we will install the following packages.

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.Tools
• Microsoft.EntityFrameworkCore.SqlServer

Microsoft.EntityFrameworkCore In this example, we are installing . Install the other two as well.

Select the installation target and click the Install button. For the version, select the latest stable.

The dialog is basically OK and you can click OK.

Install the other two as well.

I think the package looks like this:

Create a model (code) from a database table structure

To have the model and other code automatically generated, first build the project to make sure there are no errors. If there is an error, the model creation fails. If you've already verified that there are no errors, you don't need to build it.

From Visual Studio, open the Package Manager Console. If you don't have one, you can open it from the menu "Tools", "NuGet Package Manager", "Package Manager Console".

The following window will be displayed, so make sure that the "Default Project" in the upper right corner is the project for which you want to create a model. (You need to be careful if you have multiple projects)

Enter the following text in the input field: The parameters change depending on the environment, so please change them according to the following explanations in a timely manner.

Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer -Connection "Data Source=<サーバー名>\<インスタンス名>;Database=<データメース名>;user id=<接続ユーザー名>;password=<接続パスワード>;TrustServerCertificate=true" -f -OutputDir "<出力フォルダパス>" -Context "<コンテキストクラス名>" -UseDatabaseNames -DataAnnotations -NoPluralize

Example Input

Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer -Connection "Data Source=TestServer;Database=TestDatabase;user id=TestUser;password=pass;TrustServerCertificate=true" -f -OutputDir "Models\Database" -Context "TestDatabaseDbContext" -UseDatabaseNames -DataAnnotations -NoPluralize

When you press Enter to run it, the code is automatically generated: If an error occurs, the reason will be displayed, so please respond according to the error content.

User The model code of the table is as follows.

using System.ComponentModel.DataAnnotations;

namespace SetupSqlServerDatabaseFirst.Models.Database;

public partial class User
{
  [Key]
  public int ID { get; set; }

  [StringLength(20)]
  public string Name { get; set; } = null!;

  [StringLength(20)]
  public string Password { get; set; } = null!;

  public int? Age { get; set; }

  [StringLength(200)]
  public string? Email { get; set; }

  public DateOnly? Birthday { get; set; }

  public DateTime? UpdateDateTime { get; set; }
}

By the way, the warning is displayed because the connection string is written as it is in the code of the generated context class. If possible, the connection string should be stored in a separate place and set at runtime, but in this case, it is for the purpose of checking the operation, so I will leave it as it is.

Try retrieving and displaying records

Now that we have the code to access the records in the database, let's try retrieving the records and displaying them on the console.

Program.cs and modify it as follows.

// See https://aka.ms/new-console-template for more information
using SetupSqlServerDatabaseFirst.Models.Database;
using System.Text.Json;

Console.WriteLine("Hello, World!");

// データベースコンテキストのインスタンスを生成する
using var dbContext = new TestDatabaseDbContext();

// データベースから User 一覧を取得する
var users = dbContext.User.ToList();

// 取得した User 情報をコンソールに書き出す
foreach (var user in users)
{
  Console.WriteLine(JsonSerializer.Serialize(user));
}

Generate the auto-generated DbContext class new in . It is declared in so that database connections can using var be automatically destroyed.

dbContext generates properties to access each model, so in this case User , you can manipulate the records in the table by User accessing the properties. The SQL that is issued is automatically generated internally and does not need to be noticed.

ToList Here, an extension method is used to User retrieve all the records in the table.

foreach The rest is done using the and JsonSerializer.Serialize methods to User display the information on the console. As User mentioned above, each column in the table is declared as a property, so it is possible to retrieve the value individually.