SQLite

C# で SQLite を便利に使うサンプルコード(LINQ to SQLite)【改訂版】

.NET 6 C# で SQLite3 を使う方法について整理し、サンプルプログラムにまとめました。

C# では、文字列ベースのクエリよりも遙かに効率的かつミスが少なくプログラムすることができます。

サンプルプログラムは GitHub に上げてあります。

(補足)
本記事は、以前の記事を時代に合わせて改訂したものです。


使用するパッケージ

C# で SQLite を使うためには、Microsoft.EntityFrameworkCore.Sqlite パッケージを使用します。

以前はどのパッケージ(ライブラリ)を使うか少し迷うところもありましたが、現在は Entity Framework Core (EF Core) 一択でいいのではないでしょうか。

Microsoft.EntityFrameworkCore.Sqlite のインストール

NuGetインストールは簡単です。

  1. Visual Studio を起動し、SQLite を使いたいプロジェクトを開きます。
  2. メニューの[ツール|NuGet パッケージマネージャー|ソリューションの NuGet パッケージの管理]をクリック。
  3. 検索窓に「Microsoft.EntityFrameworkCore.Sqlite」と入力して SQLite パッケージを検索。
  4. 検索結果の Microsoft.EntityFrameworkCore.Sqlite を選択して、SQLite を使いたいプロジェクトにチェックを入れ、インストールボタンをクリック。関連するパッケージも含めて自動でインストールが終わります。
以上でインストール完了です。

テーブル構造定義クラスの作成

データへのアクセスを簡単・分かりやすくするために、テーブル構造を定義するクラスを作成します。

簡易名簿テーブル「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() するだけです。

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 には引き続き対象レコードが存在することになり、きちんと結果表示も行われます。

サンプルコードについて

Runサンプルコード(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 インメモリデータベースについて記載。



LINQ to SQLite で共通カラム部分をジェネリックで運用する

LINQ to SQLite を使っている中で、複数のテーブルに共通するカラム(列)がある場合、ヘルパー関数などをそれぞれのテーブルごとに手書きするのは大変だしメンテナンス性も悪い。ひとまとめにできないだろうか、というのを試行錯誤した結果、インターフェース+ジェネリックでいけることが分かったので整理しておく。

なお、LINQ to SQLite の基本的な事柄については「C# で SQLite を便利に使うサンプルコード(LINQ to SQLite)【改訂版】」を参照。

テーブル構造

フルーツの一覧を格納するフルーツテーブルと、肉の一覧を格納する肉テーブルがあるとする。フルーツテーブルと肉テーブルそれぞれのカラムのうち、ID と名前については両者共にカラムがあるものとする。

この場合、テーブル構造を定義するクラスは以下のようにする。

まず、共通カラム部分をインターフェースとしてまとめる。
public interface IFoodData
{
    // ID
    Int32 Id { get; set; }

    // 名前
    String Name { get; set; }
}

そのうえで、テーブル構造を定義するクラスにインターフェースを実装するようにする。例えばフルーツテーブルなら
[Table(Name = "t_fruit")]
public class TFruitData : IFoodData
{
    // --------------------------------------------------------------------
    // IFoodData 実装
    // --------------------------------------------------------------------

    // ID
    [Column(Name = "fruit_id", DbType = LinqUtils.DB_TYPE_INT32, CanBeNull = false, IsPrimaryKey = true)]
    public Int32 Id { get; set; }

    // 名前
    [Column(Name = "fruit_name", DbType = LinqUtils.DB_TYPE_STRING, CanBeNull = false)]
    public String Name { get; set; }

    // --------------------------------------------------------------------
    // TFruitData 独自項目
    // --------------------------------------------------------------------

    // 色
    [Column(Name = "fruit_color", DbType = LinqUtils.DB_TYPE_STRING, CanBeNull = true)]
    public String Color { get; set; }
}

のようにする。

共通カラムの操作

フルーツテーブルと肉テーブルに共通するカラム(ID、名前)について、操作をジェネリックでまとめることができる。例えば、名前でレコードを検索・表示する関数は以下のようになる。
private void QueryFoodByName<T>(String oKeyword) where T : class, IFoodData
{
    Console.WriteLine(LinqUtils.TableName(typeof(T)) + " 内で名前に「" + oKeyword + "」を含むレコードを検索");
    using (SQLiteConnection aConnection = CreateDatabaseConnection(DB_NAME_GENERIC))
    using (DataContext aContext = new DataContext(aConnection))
    {
        Table<T> aTableTest = aContext.GetTable<T>();
        IQueryable<T> aQueryResult =
                from x in aTableTest
                where x.Name.Contains(oKeyword)
                select x;
        Console.WriteLine("検索結果:" + aQueryResult.Count() + " 件");
        foreach (T aRecord in aQueryResult)
        {
            Console.WriteLine(aRecord.Name);
        }
    }
}

関数を呼びだす際は、
QueryFoodByName<TFruitData>("hoge");
QueryFoodByName<TMeatData>("fuga");

のようにする。

以上により、テーブル名が異なる複数のテーブルに共通するカラムの操作を、1 つのコードにまとめることができた。

継承でやってはどうか?

テーブル構造を定義するクラスを、インターフェースではなく継承で作るのはどうだろうか。

つまり、IFoodData をインターフェースではなく通常のクラスとして宣言してそれを基底クラスとし、TFruitData や TMeatData をそこから派生させる形にする。

しかし残念ながらそれはうまくいかなかった。

データベース内にテーブルを作成する段階で、「型 'TestLinqToSqlite.IFoodData' のデータ メンバー 'Int32 Id' は型 'TFruitData' のマッピングの一部ではありません。メンバーは継承階層のルートより上のメンバーですか?」というようなエラーが発生してしまう。

サンプルコード

サンプルコードは GitHub にて。

サンプルコードアプリの使い方

  • TestLinqToSqlite_Genericジェネリックの「DB 作成」ボタンをクリックすると、フルーツテーブルと肉テーブルを持つデータベースが作成される。
  • 「検索」ボタンをクリックすると、フルーツテーブルと肉テーブルそれぞれから検索が行われ、結果がコンソールに表示される。なお、検索はジェネリックを使用した 1 つのコードで行われている。
なお、サンプルコードアプリには基本操作の欄もあるが、こちらについては「C# で SQLite を便利に使うサンプルコード(LINQ to SQLite)【改訂版】」を参照。

参考資料

更新履歴

  • 2019/04/09 初版。
  • 2022/01/03 参考資料のリンクを改訂版のものに更新。


















LINQ で SQLite warning (284)

C# の LINQ to SQLite を使って作ったアプリを動かしていると、
SQLite warning (284): automatic index on sqlite_sq_C473B20(program_id)
という警告が Visual Studio の出力画面に表示された。

program_id 列に自動的にインデックスを張ったよ、という警告だが、手動で program_id にインデックスを張っているにも関わらず警告が出る。というよりテーブル名が sqlite_sq_C473B20 というような名前なので、何らかの一時テーブルっぽい。

ネットを探してもこれという解決策が見つからなかったが、LINQ で左外部結合(LEFT OUTER JOIN)をしているのが悪さをしているのかもしれない。

LINQ to SQLite で左外部結合をする際は、
var aQueryResult =
from s in aTableSong
from p in aTableProgram.Where(x => x.Id == s.ProgramId).DefaultIfEmpty()
where s.Name == "hoge"
select new
{
s,
p
};
もしくは
var aQueryResult =
from s in aTableSong
join pp in aTableProgram on s.ProgramId equals pp.Id into gj
from p in gj.DefaultIfEmpty()
where s.Name == "hoge"
select new
{
s,
p
};
のように記述しているが、LINQ を使わずに
oCmd.CommandText = "SELECT * FROM t_song LEFT OUTER JOIN t_program ON t_song.program_id = t_program.program_id"
+ " WHERE t_song.song_name = 'hoge'";
のような古来の記述にして ExecuteReader() したところ、警告が出なくなった。

自動でインデックスを張る作業が重いのか、警告メッセージを表示する作業が重いのか、修正前のコードは実行速度もすこぶる遅い。

該当部分だけできちんと測定したわけではないが、全体の実行処理速度は、修正前で 6 分かかっていたものが、修正後は数秒で完了するようになった。

ひとまずは解決したが、できれば LINQ で記述したい……。

LINQ での解決策をご存じの方がいらっしゃいましたらご教示下さい。


C# で SQLite を便利に使うサンプルコード(LINQ to SQLite)

【ご案内】
改訂版を公開しましたので、改訂版をご覧ください。

------------------------------
以下は改定前の旧記事です
------------------------------

.NET Framework C# で SQLite3 を使う方法について、すでにいくつか情報はあるものの、数が少なかったりするので、自分がハマった罠なども含めて、ここにまとめておく。合わせて、サンプルプログラムも公開する。

どの SQLite ライブラリを使うか?

C# で SQLite を使うための方法はいくつかあり、自分が把握している範囲では以下のようになる。
  • C 言語用 DLL を P/Invoke で使う……本家本元の DLL を使い、従来型の文字列によるクエリでプログラミング。最新のライブラリを使える(本稿執筆時点で 3.9.2)。しかし、C 言語レベルの生産性しかなく、労多くして功少なしなので、どうしても最新のバージョンを使いたい時以外はお薦めしない。
  • System.Data.SQLite……SQLite 本家による .NET 用ライブラリ。データベースを便利に使う ADO.NET に対応しており、生産性が向上。C 言語用 DLL よりは多少バージョンが古い(3.8.11.1 ベース)ものの、総合的に一番お薦め。
  • Mono.Data.SqliteClient…….NET のオープン・クロスプラットフォーム実装である Mono プロジェクトが作成したライブラリ。ADO.NET 対応。文字コードが Unicode(恐らく UTF-16)と記載があるのが気になる(本家は UTF-8)。別途本家 C 言語用 DLL が必要な模様。
  • csharp-sqlite……マネージドコードのみで作成されたライブラリ。ADO.NET 対応。2011 年で開発停止の模様。バージョンは 3.7.7.1。マネージドコードしか使えない制約がある場合に重宝しそう。
  • sqlite-net……コンパクトなライブラリ。ADO.NET 非対応。できる限り実行ファイルサイズを小さくしたい場合に活躍しそう。2012 年あたりで開発が停止しているようだ。
以降では、System.Data.SQLite を使う。

ADO.NET とは?

System.Data.SQLite は ADO.NET に対応しているが、そもそも ADO.NET とは何か。ADO.NET の詳しい解説は @IT の記事などに記載があるが、プログラマーから見たメリットを三行で書くとすれば、
  • エディタのコード補完で楽ちんかつ安全にクエリを書ける(LINQ)
  • 設計時からデータ構造をビジュアル化できる(Entity Framework)
  • 使うデータベース(SQLite/Oracle……)によらず同じコードを使い回せる
あたりになるのではと理解している。

これにより、C 言語で SQLite をいじるよりもはるかに効率的にデータベースプログラミングが行える。

なお、本稿では、LINQ は使うが Entity Framework は使わない。

System.Data.SQLite のインストール

NuGetインストールは非常に簡単である。ここでは、Visual Studio 2015 Community / .NET Framework 4.5 の環境で実行しているものとする。
  1. Visual Studio を起動し、SQLite を使いたいプロジェクトを開く
  2. メニューの[ツール|NuGet パッケージマネージャー|ソリューションの NuGet パッケージの管理]をクリック
  3. 検索窓に「SQLite」と入力して SQLite パッケージを検索
  4. 検索結果の System.Data.SQLite を選択して、インストールボタンをクリック。関連するライブラリも含めて自動でインストールが終わる
たったこれだけ。

以前は、NuGet をコマンドラインで使う必要があった時代もあったようだが、現在は GUI で使える。

NuGet の欠点は、プロジェクトごとにインストールの必要があるため(プロジェクトに最適なバージョンを選んでインストールしてくれる)、SQLite を使うプロジェクトが複数ある場合は、ディスクをどんどん消費していく。その場合は、ダミープロジェクトでインストールしたバイナリを使い回したり、手動でバイナリをダウンロードして共用するといいかもしれない。

System.Data.SQLite の基本

データベースファイル(*.db とか *.sqlite3 とか)を開くには、SQLiteConnection クラスを使う。

SQLiteConnection を new する際にパラメーターを文字列で渡すのだが、SQLiteConnectionStringBuilder というお助けクラスを使うと、パラメーター文字列をミスなく生成できる。

SQLiteConnectionStringBuilder aConnectionString = new SQLiteConnectionStringBuilder
{
    DataSource = @"R:\Test.db"
};
using (SQLiteConnection aConnection = new SQLiteConnection(aConnectionString.ToString()))
{
    aConnection.Open();

    // ここにデータベース処理コードを書く
}


従来型の文字列によるコマンドを発行したい場合は、SQLiteCommand クラスを使う。

using (SQLiteCommand aCmd = new SQLiteCommand(aConnection))
{
    aCmd.CommandText = "CREATE TABLE IF NOT EXISTS t_test (test_id INTEGER NOT NULL PRIMARY KEY, test_name NVARCHAR NOT NULL, test_height REAL);";
    aCmd.ExecuteNonQuery();
}


テーブル構造定義クラスの導入

データへのアクセスを簡単・分かりやすくするために、テーブル構造を定義するクラスを作成する。

簡易名簿テーブル「t_test」が以下のような構造になっているとする。
フィールド名NULL備考
test_idINTEGER不可連番
test_nameNVARCHAR不可氏名
test_heightREAL身長

この場合、テーブル構造定義クラスは以下のようになる。

[Table(Name = "t_test")]
public class TTestData
{
    // ID
    [Column(Name = "test_id", DbType = "INT", CanBeNull = false, IsPrimaryKey = true)]
    public Int32 Id { get; set; }

    // 氏名
    [Column(Name = "test_name", DbType = "NVARCHAR", CanBeNull = false, UpdateCheck = UpdateCheck.Never)]
    public String Name { get; set; }

    // 身長
    [Column(Name = "test_height", DbType = "REAL", CanBeNull = true)]
    public Double? Height { get; set; }
}


フィールドをプロパティーとして定義しているシンプルなクラスである。

CREATE TABLE で作成した際のテーブル情報に合わせて、クラスメンバーの属性を記述している。例えば ID フィールドであれば、Name = "test_id" でデータベース上のフィールド名、DbType = "INT" でデータ型、CanBeNull = false で NULL 不可であることを表している。

DbType の型について注意点が 2 つ。
  • 整数型は DbType = "INT" とする。INTEGER ではエラーになる。
  • 文字列型は DbType = "NVARCHAR" とする。TEXT ではエラーになる。
特に、文字列型を TEXT としてしまうと、原因が分かりづらいエラーが発生し、ハマることになる。

身長フィールドは、NULL を許可している。CanBeNull = true にすると共に、プロパティーの型を「Double?」というように「?」を付けて nullable にしている。

レコードの挿入

テーブル構造定義クラスを導入したことにより、データベースの取扱が非常に簡単になる。

レコードの挿入を行うコードは、以下のようになる。SQLiteCommand クラスを使わなくて済むので全然 SQL っぽくなく、普通のコードのように書ける。

using (DataContext aConText = new DataContext(aConnection))
{
    Table<TTestData> aTableTest = aConText.GetTable<TTestData>();
    aTableTest.InsertOnSubmit(new TTestData { Id = 1, Name = "Fukada Kyoko" });
    aTableTest.InsertOnSubmit(new TTestData { Id = 2, Name = "Eda Ha", Height = 180.0 });
    aTableTest.InsertOnSubmit(new TTestData { Id = 3, Name = "Dan Gerou", Height = 150.5 });
    aTableTest.InsertOnSubmit(new TTestData { Id = 4, Name = "Baba Takashi" });
    aTableTest.InsertOnSubmit(new TTestData { Id = 5, Name = "Aikawa Ai", Height = 145.6 });
    aConText.SubmitChanges();
}


ID 1 や 4 では身長を設定していないので、データベース上 NULL として格納される。

レコードの検索

レコードの検索は LINQ to SQLite と呼ばれる手法で行う。ちょっと変わった書き方だが、エディタのコード補完が効くのでミスが減る。

using (DataContext aConText = new DataContext(aConnection))
{

    Table<TTestData> aTableTest = aConText.GetTable<TTestData>();
    IQueryable<TTestData> aQueryResult =
            from x in aTableTest
            where x.Name == "Eda Ha" || x.Height < 150.0
            orderby x.Height
            select x;
    foreach(TTestData aData in aQueryResult)
    {
        Debug.WriteLine(aData.Name);
    }
}


from のところが SQL の発行に相当する部分。なんとなく SQL に似ているので、読めば理解はできると思う。

結果は foreach で回せるので、非常に簡単に扱える。

なお、文字列の部分一致(LIKE)を検索したい場合は String.Contains() を用いて where 句を
where x.Name.Contains("hoge")
のようにすれば良い。

レコードの削除

レコードを削除する場合、削除したいレコードを検索し、それを削除する、という流れになる。

検索については前節と同じで、その結果を、DeleteAllOnSubmit() メソッドにぶち込むだけ、とこれまた簡単。

using (DataContext aConText = new DataContext(aConnection))
{
    Table<TTestData> aTableTest = aConText.GetTable<TTestData>();
    IQueryable<TTestData> aDelTargets =
            from x in aTableTest
            where 140 < x.Height && x.Height < 160
            select x;
    aTableTest.DeleteAllOnSubmit(aDelTargets);
    aConText.SubmitChanges();
}


サンプルコード

以上をまとめたものを、サンプルコードとして公開する。
LinqUtils.cs は LINQ に関する補助クラス。CREATE TABLE や CREATE INDEX は少々コーディングが面倒くさいので、テーブル構造定義クラスの情報から自動的にテーブルを作成するためにとりまとめており、こちらも GitHub で公開している。

サンプルコードアプリの使い方

  • TestLinqToSqlite_Basic基本操作の「DB 作成」ボタンをクリックするとデータベースが作成され、「検索」ボタンをクリックすると、条件に合うレコードがコンソールに表示される(以降、表示はすべてコンソールで行われる)。
  • 「削除」ボタンをクリックするといくつかのレコードが削除されるので、その後「検索」ボタンをクリックすると、先ほどの検索結果とは異なる結果が得られる。
  • データベースをリセットするために再度「DB 作成」をクリックしてから、今度は「更新」ボタンをクリックすると、レコード内容が更新される。その後「検索」ボタンをクリックすると、先ほどの検索結果とは異なる結果が得られる。
なお、サンプルコードアプリにはジェネリックの欄もあるが、こちらについては「LINQ to SQLite で共通カラム部分をジェネリックで運用する」を参照。

参考資料

更新履歴

  • 2015/11/21 初版。
  • 2019/04/09 サンプルコードを Dropbox から GitHub に移管。
  • 2019/04/09 サンプルコードアプリの使い方を記載。
改訂版を公開しましたので、改訂版をご覧ください。



System.Data.Linq.dll で NotSupportedException(解決)

SQLite3 を C# で使いたくて、いろいろ試していたら、文字列の比較でエラーが発生。

やったことは、System.Data.SQLite(SQLite の公式 ADO.NET プロバイダー)をインストールし、LINQ to SQLite でデーターベースにアクセス。ソースコードはおよそ以下のような感じ。

    Table<TTestData> aTableTest = aConText.GetTable<TTestData>();
    IQueryable<TTestData> aQueryResult =
            from x in aTableTest
            where x.Name == "Eda Ha"
            select x;


ここで、aContext は DataContext 型のインスタンス。TTestData は以下の定義。

[Table(Name = "t_test")]
public class TTestData
{
    // ID
    [Column(Name = "test_id", DbType = "INT", CanBeNull = false, IsPrimaryKey = true)]
    public Int32 Id { get; set; }

    // 氏名
    [Column(Name = "test_name", DbType = "TEXT", CanBeNull = false, UpdateCheck = UpdateCheck.Never)]
    public String Name { get; set; }
}


NotSupportedExceptionこのようなコードを実行すると、IQueryable の結果を使う段階で、NotSupportedException が発生する。

    例外がスローされました: 'System.NotSupportedException' (System.Data.Linq.dll の中)
    追加情報:SQL Server では、NText、Text、Xml、または Image の各データ型の比較はハンドルされません。

where x.Name == "Eda Ha" の箇所が where x.ID == 1 のような数値型の比較だと例外は発生しない。

原因がわからずさんざん悩んだ。「SQL Server では~」のエラーメッセージで検索しても情報が見つからない。

結論としては、Name の DbType がまずかった。

DbType を NVARCHAR にして、CREATE TABLE する際の型も NVARCHAR にしたら、うまく動作するようになった。

どうやら、SQLite を .NET で使う場合は、文字列型は NVARCHAR にしないといけないようだ。

なお、エラーが発生した環境は、
  • Visual Studio Community 2015
  • .NET 4.5
  • System.Data.SQLite 1.0.98.0(sqlite-netFx45-setup-x86-2012-1.0.98.0.exe)
関連記事

月別アーカイブ
記事検索
最新コメント
  • ライブドアブログ