.NET 6 C# で SQLite3 を使う方法について整理し、サンプルプログラムにまとめました。
C# では、文字列ベースのクエリよりも遙かに効率的かつミスが少なくプログラムすることができます。
サンプルプログラムは GitHub に上げてあります。
（補足）本記事は、以前の記事を時代に合わせて改訂したものです。
使用するパッケージ
C# で SQLite を使うためには、Microsoft.EntityFrameworkCore.Sqlite パッケージを使用します。
以前はどのパッケージ（ライブラリ）を使うか少し迷うところもありましたが、現在は Entity Framework Core (EF Core) 一択でいいのではないでしょうか。
Microsoft.EntityFrameworkCore.Sqlite のインストール
インストールは簡単です。- Visual Studio を起動し、SQLite を使いたいプロジェクトを開きます。
- メニューの［ツール｜NuGet パッケージマネージャー｜ソリューションの NuGet パッケージの管理］をクリック。
- 検索窓に「Microsoft.EntityFrameworkCore.Sqlite」と入力して SQLite パッケージを検索。
- 検索結果の Microsoft.EntityFrameworkCore.Sqlite を選択して、SQLite を使いたいプロジェクトにチェックを入れ、インストールボタンをクリック。関連するパッケージも含めて自動でインストールが終わります。
テーブル構造定義クラスの作成
データへのアクセスを簡単・分かりやすくするために、テーブル構造を定義するクラスを作成します。
簡易名簿テーブル「t_test」が以下のような構造になっているとします。
簡易名簿テーブル「t_test」が以下のような構造になっているとします。
| フィールド名 | 型 | NULL | 備考 |
|---|---|---|---|
| test_id | 整数 | 不可 | 連番、主キー |
| test_name | 文字列 | 不可 | 氏名、インデックス作成、ユニーク制約 |
| test_height | 浮動小数 | 可 | 身長 |
この場合、テーブル構造定義クラスは以下のようになります。
[Table("t_test")]
[Index(nameof(Name), IsUnique = true)]
internal class TTestData
{
// ID
[Key]
[Column("test_id")]
public Int32 Id { get; set; }
// 氏名
[Column("test_name")]
public String Name { get; set; } = String.Empty;
// 身長
[Column("test_height")]
public Double? Height { get; set; }
}基本的には、カラムをプロパティーとして宣言するだけです。その際、[Column] 属性でデータベースファイルのフィールド名を指定しておきます。SQLite の実際の型についてはフレームワーク側で良きに計らってくれますので、コードにする必要はありません。
主キーとなるカラムには [Key] 属性を付けておきます。
クラスには [Table] 属性を付けてテーブル名を指定します。
インデックスやユニーク制約を付けたい場合は、クラスに [Index] 属性を付けます。インデックスを複数個作成したい場合は、[Index] 属性を複数個記述します。
複合インデックス（複数カラムで 1 つのインデックス）を作成したい場合は [Index(nameof(Name), nameof(Height))] のように 1 つの [Index] 属性の中に複数のカラムを記述します。
インデックスの詳細は EF Core のドキュメントにまとめられています。
このサンプルプログラムでは、コード記述 → コードの通りにデータベース作成、の流れですが、逆に、既にデータベースファイルがある場合は、それを解析してコードを自動生成してくれるスキャフォールディングも用意されているようです。
コンテキストクラスの作成
テーブル構造定義クラスに対応したテーブルを持つデータベースにアクセスするためには、コンテキストクラスを用います。テーブル構造定義クラスが 1 つのテーブル（のレコード）、コンテキストクラスが 1 つのデータベースファイルのイメージです。
internal class TestContext : DbContext
{
// テストテーブル
public DbSet<TTestData> TestData { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
SqliteConnectionStringBuilder stringBuilder = new()
{
DataSource = "Test.sqlite3",
};
using SqliteConnection sqliteConnection = new(stringBuilder.ToString());
optionsBuilder.UseSqlite(sqliteConnection);
}
}
テーブルを DbSet<T> のプロパティーとして宣言します。1 つのデータベースファイルに複数のテーブルがある場合は、DbSet<T> なプロパティーを複数宣言します。
OnConfiguring() でデータベースファイルと対応づけます。
データベースファイルの作成
以上 2 つのクラスを作成したことで準備は整いました。
これから、実際にデータベースを操作します。
まずは空のデータベースファイルを作成するところからですが、コンテキストクラスから EnsureCreated() するだけです。
using TestContext testContext = new();
testContext.Database.EnsureCreated();
ファイルがまだ無い場合はこれでファイルが作成され、逆に、既にデータベースが存在している場合は特に何も起こりません。データベースがクリアされたりはしませんので、とりあえず EnsureCreated() しておけばデータベースにアクセスできるようになります。
レコードの挿入
1 レコードが、初めに作成した TTestData の 1 インスタンスです。レコードの挿入はテーブルプロパティーの Add() で行います。
testContext.TestData.Add(new TTestData { Name = "Fukada Kyoko" });
testContext.TestData.Add(new TTestData { Name = "Eda Ha", Height = 159.0 });
testContext.SaveChanges();
主キーが整数の場合は自動インクリメントになりますので、Id は設定する必要ありません。Height は NULL 可なので、指定しないと NULL になります。
Add() した時点では実際にはデータベースファイルには追加されず、SaveChanges() でまとめて追加されます。
文字列で SQL を記述する必要がないため、タイプミスによるエラーが発生せず、また、Visual Studio の各種サジェストによる支援も受けられます。確実・簡単にデータベースを操作できます。
レコードの検索
レコードの検索は、テーブルプロパティーから LINQ で行います。
IQueryable<TTestData> queryResult = testContext.TestData.Where(x => x.Name == "Eda Ha" || x.Height < 150.0).OrderBy(x => x.Height);
結果は foreach で回せるので、簡単に扱えます。
検索で注意が必要なのは、文字列を「含む」検索（部分一致検索）です。大文字小文字を区別して含む検索をしたい場合（小文字の a を含む）は、String.Contains() を用いて
Where(x => x.Name.Contains("a"))
のようにします。
一方で、大文字小文字を区別せずに含む（a でも A でも含む）検索をしたい場合、
Where(x => x.Name.Contains("a", StringComparison.OrdinalIgnoreCase))
とすると、「The LINQ expression '～' could not be translated」というエラーになります（InvariantCultureIgnoreCase や CurrentCultureIgnoreCase も同様です）。代わりに、
Where(x => EF.Functions.Like(x.Name, $"%a%"))
のように Like() 関数を用います。
レコードの削除
レコードを削除する場合、削除したいレコードを検索し、それを削除する、という流れになります。
検索については前節と同様で、その結果を、RemoveRange() するだけです。
検索については前節と同様で、その結果を、RemoveRange() するだけです。
IQueryable<TTestData> queryResult = testContext.TestData.Where(x => x.Height == null || x.Height < 150.0);
testContext.TestData.RemoveRange(queryResult);
testContext.SaveChanges();
SaveChanges() をお忘れなく。
レコードの更新
レコードの削除と同様、更新したいレコードを検索し、そのプロパティーを更新する、という流れになります。
こちらも更新後の SaveChanges() をお忘れなく。
サンプルコードでは、更新の時のみ、検索結果を IQueryable<TTestData> ではなく List<TTestData> で受けています。
IQueryable で SQLite を使う場合は遅延評価されるようで、foreach 等で実際に TTestData を取得して初めて検索が行われます。大量にデータがあり途中で foreach を打ち切った場合など、後半の不要な検索が行われず効率的です。
しかし、サンプルコードの場合、身長 155 cm の人は検索対象なのできちんと 145 cm に更新されるのですが、結果表示の際は「身長 >= 150」を満たさなくなり、結果には表示されなくなってしまいます。
これを防止するために、List で検索結果を受けています。ToList() の際に検索が行われるので、後で身長が変化しても、List には引き続き対象レコードが存在することになり、きちんと結果表示も行われます。
サンプルコードについて
サンプルコード（Test LINQ to SQLite Gen 2）は GitHub に上げてあります。.NET 6 + Visual Studio 2022 + Windows 10 で動作確認しています。Visual Studio でソリューションを開き F5 を押せば実行できます。本記事に対応するのは、サンプルプログラム実行時に表示されるウィンドウの左半分、「基本操作」のところです。
「検索」「削除」「更新」いずれのボタンをクリックしても、まず最初にデータベースとサンプルレコードが作成されます。削除によりレコードが 0 になっていた場合もサンプルレコードが再作成されます。
「検索」ボタンをクリックすると、名前が "Eda Ha" または、身長が 150 cm 未満の人が検索されます。
「削除」ボタンをクリックすると、身長未登録、または身長が 150 cm 未満の人が削除されます。
「更新」ボタンをクリックすると、身長 150 cm 以上の人の身長を 10 cm 下げます。
「更新」してから「検索」や「削除」すると、以前と結果が変わります。
なお、ウィンドウの右半分、「ジェネリック」については、LINQ to SQLite で共通カラム部分をジェネリックで運用するをご覧ください。
インメモリデータベース
EF Core の SQLite3 でもインメモリデータベースは使用可能です。
サンプルコードでは、TestContext.OnConfiguring() のプリプロセッサ部分を true にすることでインメモリデータベースにできます。
インメモリデータベースにする場合は、OnConfiguring() で SqliteOpenMode.Memory を用いますが、その際は接続を開いておく必要があるようです。開いておかないとエラーが出ます。
インメモリデータベースは接続が切れると消滅するので、コンテキストを MainWindow クラスのメンバとして保持し続けるなどの対応が必要になるかと思います。
ジャーナルモード
EF Core はデフォルトでジャーナルモードが WAL（Write-Ahead Logging：先行書き込みログ）になっています。
ジャーナルモードを DELETE 等他のものにしたい場合は、コマンドを実行することで可能です。
サンプルコードでは、TestContext.OnConfiguring() のプリプロセッサ部分を true にすることで DELETE にできます。ジャーナルモードを変更した場合、一度データベースファイルを削除するのが確実です。
更新履歴
- 2022/01/01 初版。
- 2022/01/01 ジャーナルモードについて記載。
- 2022/01/03 インメモリデータベースについて記載。
