はじめに
急遽来月からC#でシステム立ち上げの案件に入ることになり、
最近の.NET周りの技術について勉強することに。
以前関わっていたプロジェクトでは、
ASP.Net MVCで構築し、その際ORマッパーなどは使用していなかったが、
.NET Core勉強中にふとEntity Framework CoreというORマッパーが目についたので
触ってみたところ、とても良かったので記事にした次第です。
今回は1つのテーブルからデータを取得する処理を
Entity Framework Coreを使用してやってみました。
環境
言語：C# 8.0
FW：.NET Core 3.1.401
DB：MySQL 8.0.21
その他：Docker, Adminerなど
事前準備
1. Entity Framework CoreのNuGetパッケージをインストール
EFCoreのパッケージをインストールします。
今回はDBにMySQLを使うので、.NETプロジェクト直下で以下コマンドを実行。
dotnet add package MySql.Data.EntityFrameworkCore -v 8.0.21
NuGetパッケージ名は以下サイトに記載してあります。
有名どころのDBは大体あると思う。
https://docs.microsoft.com/ja-jp/ef/core/providers/?tabs=dotnet-core-cli
2. DB構築
今回は以下のような企業の株価を保持するテーブルを準備します。
とりあえず上記テーブルに2020/9/1時点の東証1部の会社の株価データレコードを挿入します。
本当は時価総額が良かったけどデータが見つからなかった。
実装
1. Entityクラスの作成
テーブルのレコードを保持するいわゆるエンティティクラスを作ります。
public class CompanyData {
[Key]
public string StockCode {get; set;}
public string CompanyName {get; set;}
public string IndustryCode {get; set;}
public int StockPrice {get; set;}
}
プロパティは、テーブルのカラム名・型を一致させる必要があります。
また、基本的にEntityクラスには主キーのプロパティが必要で、
これが無いとクエリ発行時に例外が投げられます。
主キーにあたるプロパティに[Key]注釈をつけるか、
プロパティ名をidにすると主キーとして認識されます。
（プロパティ名をidにする場合、テーブルのカラム名もidにする必要あり）
どうしても主キーを付けられない場合は、クラスに[Keyless]注釈をつけるようです。
（振る舞いがどう変わるかは以下参考。）
https://docs.microsoft.com/ja-jp/ef/core/modeling/keyless-entity-types
2. DbContext拡張クラスを作成
DbContextを継承した拡張DbContextクラスを作成します。
public class MyContext:DbContext
{
public DbSet<CompanyData> CompanyStockPrice{get;set;} //このプロパティ名がテーブル名と一致してないとダメ
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseMySQL(@"server=localhost;database=hoge;userid=hoge;password=hoge;sslmode=none;");
}
DbSet型のプロパティとDB接続文字列を設定するOnConfiguringをオーバーライドしたメソッドを実装します。
（実際のシステムでは、接続文字列は外部ファイルや環境変数に外出しした方が良いでしょう。）
このDbSet型のプロパティ名のテーブルを参照するため、
アクセスしたいテーブル名とプロパティ名を一致させる必要があります。
これだけでORマッピングの実装は完了です。
昔はJavaでマッピング用の設定ファイルをガリガリ書いてたので
簡単すぎてびっくりしました。
3. DBアクセスロジックの実装
最後にDBにSQLクエリを発行して結果を受け取って処理するクラスを実装します。
サンプルとして、指定した業種・株価上位件数の会社の株価を表示するコードを書いてみます。
SQLクエリの構築、発行の実装方法としてLINQを使う方法とSQLクエリを書く方法があります。
3-1. LINQでSQLクエリ構築
public class CompanyStockPriceSearch {
static void Main(string[] args) {
Console.Write("業種コード入力：");
String target = Console.ReadLine();
Console.Write("上位何件？：");
int tops = int.Parse(Console.ReadLine());
Console.WriteLine("---------------------------");
using(var context = new MyContext())
{
Console.WriteLine($"業種コード「{target}」の株価Top{tops}");
// post SQL query
context.CompanyStockPrice
.Where(com => com.IndustryCode.Equals(target))
.OrderByDescending(com => com.StockPrice)
.Take(tops)
.ToList()
.ForEach(com => Console.Write($"[{com.StockCode}]{com.CompanyName} 株価={com.StockPrice}\n"));
}
}
}
上記のようにLINQでSQLクエリの構築と発行ができます。
なんと・・・これは快適・・・😇
SQL書かなくてもコードアシストでサクサク問い合わせ内容を掛けるのは良いですね！
実行結果は以下の通りです。
> dotnet run
業種コード入力：10
上位何件？：5
---------------------------
業種コード「10」の株価Top5
[7974]任天堂 株価=58590
[9435]光通信 株価=25510
[4684]オービック 株価=18830
[9605]東映 株価=15760
[4661]オリエンタルランド 株価=14285
3-2. 生SQLクエリを書く
public class CompanyStockPriceSearch {
static void Main(string[] args) {
// ~~~~[中略]~~~~
using(var context = new MyContext())
{
Console.WriteLine($"業種コード「{target}」の株価Top{tops}");
// post SQL query
context.CompanyStockPrice
.FromSqlRaw("SELECT * FROM CompanyStockPrice"
+ " WHERE IndustryCode = {0}"
+ " ORDER BY StockPrice DESC"
+ " LIMIT {1}"
, target, tops)
.ToList()
.ForEach(com => Console.Write($"[{com.StockCode}]{com.CompanyName} 株価={com.StockPrice}\n"));
}
}
}
SQLを直接書く場合は上記のようにDbSet#FromSqlRawメソッドで指定します。
バインドパラメータを使う場合は{0},{1}のように指定して、
SQL文に続く引数に値をバインドパラメータ分だけ指定します。
またSELECT句にカラムを指定する場合は、
必ずEntityクラスのすべてのプロパティのデータを返すように指定する必要があります。
結果は下記のとおり、先ほどと同じです。
> dotnet run
業種コード入力：10
上位何件？：5
---------------------------
業種コード「10」の株価Top5
[7974]任天堂 株価=58590
[9435]光通信 株価=25510
[4684]オービック 株価=18830
[9605]東映 株価=15760
[4661]オリエンタルランド 株価=14285
4. 補足
LINQで実装した場合、どんなSQLクエリが投げられてるんだろう？🤔
まさかSELECT * FROM CompanyStockPrice;で全件取ってきてないよな・・・？
と思ったのでMySQLのSQLログを有効にして発行されたSQLクエリをDBサーバ側で取ってみました。
SELECT
`c`.`StockCode`,
`c`.`CompanyName`,
`c`.`IndustryCode`,
`c`.`StockPrice`
FROM
`CompanyStockPrice` AS `c`
WHERE
`c`.`IndustryCode` = '10'
ORDER BY
`c`.`StockPrice` DESC
LIMIT 5
おぉーLINQの処理内容がそのままSQLクエリに反映された形になってますね。
リテラルに指定の値がそのまま記載されてますが
バインドパラメータが埋め込まれたSQLクエリ発行したときでも上記のようにログに出るので
LINQ時もバインドパラメータ化されてるでしょう・・・きっと。
まとめ
ORマッピングが設定ファイル無しでコードだけで済むのは本当に良いですね！
LINQでSQLクエリを構築する方法もやってみると実装が快適です。
ただ、実際のシステムのSQLクエリはもっと複雑なものもあるので
LINQで書く場合と生SQLを書く場合とで使い分けが必要そうです。
また、どちらでやる場合も↑には記載していない注意点がいくつかあるので
公式ドキュメントに目を通しておいた方が良いと思います。
参考
- 公式Entity Framework ドキュメント https://docs.microsoft.com/ja-jp/ef/