.NET 6.0コンソールアプリからC#のLINQでSQL Serverにアクセスしたい

#はじめに
前回の記事の続きになります。
やりたいのはLINQでSQL Serverにアクセスするということなのですが、今はEntity Framework Coreというものを使うのが主流なようです。

##環境
自分の環境はこんな感じですが、WindowsやLinuxでも同じ感じでいけるかとは思います。
あとM1 MacなのでSQL Serverの代わりにAzure SQL Edgeというものを使っていますが、実質SQL Serverと同じかと思います。詳しくは前々回の記事参照のこと。

• macOS 11.5.2 (Big Sur)
• .NET 6.0.100
• Microsoft Azure SQL Edge Developer (RTM) - 15.0.2000.1559 (ARM64)

##Entity Framework Coreのインストール
コンソールから以下のコマンドを入力します。

dotnet tool install --global dotnet-ef

##プロジェクトへのパッケージの追加
プロジェクトのトップディレクトリで以下のコマンドを実行します。

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

#DBからモデルを作成
Entity Frameworkを使い始めるときはコードファースト、DBファースト、モデルファーストなどのやり方があるようですが、今回はすでに存在するDBからコードを自動生成するDBファーストの方法で作業をすすめます。

dotnet ef dbcontext scaffoldというコマンドでDbContextクラスとDBに存在するテーブルのモデル(クラス)を生成します。
-oオプションで出力フォルダを指定、-fオプションはすでに存在するファイルを強制的に上書きします。
モデルを生成するテーブルを指定する場合は、--tableオプションで指定します。

データベースは前々回の記事で作成したTutorialDBを使用しました。TutorialDBにはCustomerというテーブルがひとつだけ存在します。

% dotnet ef dbcontext scaffold "Server=localhost;Database=TutorialDB;user id=sa;password=(YourPassword!);" Microsoft.EntityFrameworkCore.SqlServer -f -o Models
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.

コマンドが成功すると、ModelsディレクトリにTutorialDBContext.csとCustomer.csが生成されます。

Models/TutorialDBContext.cs

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace hello.Models
{
    public partial class TutorialDBContext : DbContext
    {
        public TutorialDBContext()
        {
        }

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

        public virtual DbSet<Customer> Customers { get; set; } = null!;

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning 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.
                optionsBuilder.UseSqlServer("Server=localhost;Database=TutorialDB;user id=sa;password=(YourPassword!);");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>(entity =>
            {
                entity.Property(e => e.CustomerId).ValueGeneratedNever();

                entity.Property(e => e.Email).HasMaxLength(50);

                entity.Property(e => e.Location).HasMaxLength(50);

                entity.Property(e => e.Name).HasMaxLength(50);
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

警告
生成されたソースコード中にユーザー名や生パスワードが出てくるので、コメントでも書かれているように各自対策をお願いします！

あとCustomerテーブルに対応したクラスは以下のように生成されました。

Models/Customer.cs

using System;
using System.Collections.Generic;

namespace hello.Models
{
    public partial class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; } = null!;
        public string Location { get; set; } = null!;
        public string Email { get; set; } = null!;
    }
}

DBへのアクセス

ここまで来たらあとはもう一息です。
前回の記事でHello Worldを表示していたProgram.csを以下のように書き換えるだけです。
１行目のusing hello.Models;さえやっておけば、こんな感じでLINQで自由にDBにアクセスできる感じです。
Whereで検索条件を指定して、Nameでソートするようにしてみました。

Program.cs

using hello.Models;

using (var db = new TutorialDBContext())
{
    var customers = db.Customers
        .Where(b => b.CustomerId > 1)
        .OrderBy(b => b.Name)
        .ToList();

    foreach (var c in customers) {
        Console.WriteLine($"{c.CustomerId}, {c.Name}, {c.Location}, {c.Email}");
    }
}

実行結果はこんな感じです。

% dotnet run
3, Donna, Germany, [e-mail]
4, Janet, United States, [e-mail]
2, Keith, India, [e-mail]

無事CustomerIdが1より大きいレコードが、Nameでソートされて表示されました。

#まとめ
C#のLINQでSQL Serverにアクセスしたいときは、Entity Framework Coreを使うのが良さそう。

次回は100万行くらいのテーブルを作って、LINQで少し複雑な条件でSELECTするような感じのことをしたいと思います。

#参考にしたページ
https://docs.microsoft.com/ja-jp/ef/core/get-started/overview/install#net-core-cli
https://qiita.com/takanemu/items/ebca534db398aa9cce34
https://mseeeen.msen.jp/generate-dbcontext-by-ef-core-scaffolding-from-existing-sql-server/