EntityFramework6を使って、SQL Server Compact4とSQLiteでコードファーストをしてみた

EntityFrameworkを使ったコードファーストに興味があったため、

SQL Server Compact 4.0
SQLite

という、手軽に扱えるデータベースで使う場合について調査した時のメモです。

なお、SQL Server Compactについては、すでにdeprecatedとなっています。
Is SQL Server Compact discontinued from Visual Studio 2013? - Stack Overflow

また、以下のコードをほぼそのまま使ってコードファーストをやってみました。
EF 4.1の目玉機能「コード・ファースト」を理解しよう－＠IT

環境

Windows7 x64
Entity Framework 6.1.1
EntityFramework.SqlServerCompact 6.1.1 (SQL Server Compact 4.0向け)
System.Data.SQLite (x86/x64) 1.0.93.0

SQL Server Compactの場合

上記の@ITの記事よりも更に導入が簡単になっていました。

NuGetからEntityFramework.SqlServerCompact(作成者：Microsoft)をインストール
ターゲットプラットフォームを x86 に変更すれば、App.configを修正することなく、そのまま行うことができました。

データについては\bin\Debug\EntityCompact.ItemCatalog.sdfに保存されています。VisualStudioなどで内容を確認すると、テーブルが作成されていてデータも登録されていました。

SQLiteの場合

残念ながら、現時点のEntityFrameworkではSQLiteに対してDDL実行ができないようです。ただ、テーブル構造さえできていれば、データを登録することなどができました。

なお、Web上には EntityFramwork + SQLiteの記事がいくつかありましたが、バージョンによって設定する内容が異なるようで、今回は自分の環境で動作したものを記載しておきます。

NuGetにてインストール

以下をインストールすることで、EntityFrameworkも合わせてインストールされます。

System.Data.SQLite (x86/x64) (作成者： SQLite Development Team)

App.config の修正

SQL Server Compactとは異なり、App.configがそのままでは動作しないため、いくつか手を入れます。

なお、以下の方法は自分の環境でトライ＆エラーした結果なので、より良い方法があるのかもしれません。

DbProviderFactories要素の変更

NuGetからインストールした場合、App.configにいろいろと追記されますが、EF6 + SQLite 1.0.93.0ではDBに接続することができません。

そのため、invariant属性がSystem.Data.SQLite.EF6、type属性がSystem.Data.SQLite.SQLiteFactory, System.Data.SQLiteと、自動的に生成されたものをミックスしたような内容を指定します。

<DbProviderFactories>
  <remove invariant="System.Data.SQLite.EF6" />
  <add name="SQLite Data Provider" invariant="System.Data.SQLite.EF6" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>

connectionStrings要素の追加

connectionStrings要素がないと、defaultConnectionFactory要素で指定しているFactoryを使用してしまうため、connectionStringsを設定します。

なお、ポイントとしては、DbContextと同じ名称を name に設定しておくことと、SQLiteの外部キー制約を有効にするためのパラメータをセットすることになります。

<connectionStrings>
  <add name ="ItemCatalog" connectionString="Data Source=|DataDirectory|sample.sqlite;foreign keys=true;" providerName="System.Data.SQLite.EF6"/>
</connectionStrings>

SQLiteの外部キー制約についての参考

　
ちなみに、既存のdefaultConnectionFactory要素のかわりに、System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6を記載したところ以下のエラーが発生したため、defaultConnectionFactory要素はそのままにしてconnectionStrings要素を追加する形を取りました。

System.InvalidOperationException

Failed to set Database.DefaultConnectionFactory to an instance of the 'System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6' type as specified in the application configuration.See inner exception for details.

InnerException

型 'System.Data.SQLite.EF6.SQLiteProviderFactory' のオブジェクトを型 'System.Data.Entity.Infrastructure.IDbConnectionFactory' にキャストできません。

警告の解決

VisualStudioの状態によっては、以下の警告が出るかもしれません。

要素'entityFramework'には無効な子要素'providers'が含まれています。必要とされる要素は'contexts'です。

　
そのため、以下の記事を参考に Entity Framework 6 Tools for Visual Studio 2012 & 2013 をインストールすれば、警告はなくなります。
entity framework - How to resolve Warning : The element 'entityFramework' has invalid child element 'providers'. List of possible elements expected: 'contexts' - Stack Overflow

プログラムの修正

@ITのままでは動作しないため、プログラムを修正します。

DDLの追加

上記の通り、今のところはEF6 + SQLiteではDDL実行ができないようなので、別途DDL実行をするメソッドを追加して呼ぶようにします。

なお、SQL Server Compactの動きと同じような感じになるよう、

プライマリーキーをオートインクリメント
外部キー制約を設定
テーブル名は複数形で指定(MemberではなくMembers)
SQLiteのファイル(sample.sqlite)は、実行時のフォルダへ出力

と設定します。

static void ExecuteDDL()
{
    var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "sample.sqlite");
    System.Data.SQLite.SQLiteConnection.CreateFile(path);

    var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };

    using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
    {
        cn.Open();

        var sql = "CREATE TABLE Members (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Address TEXT, TelNo TEXT); ";
        sql += "CREATE TABLE Items (Id INTEGER PRIMARY KEY AUTOINCREMENT, Price INTEGER, MemberId INTEGER, Name TEXT, SoldAt datetime, FOREIGN KEY(MemberId) REFERENCES Members(Id))";

        var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
        cmd.ExecuteNonQuery();

        cn.Close();
    }
}

　
参考：
c# - Create SQLite Database and table - Stack Overflow

コードファーストで使うクラスのプロパティの型を変更

SQLiteの仕様として、INTEGER型のプライマリーキーは64bitになることからC#でプライマリーキーをint型で指定すると、以下のエラーが発生します。

The type of the key field 'Id' is expected to be 'System.Int32', but the value provided is actually of type 'System.Int64'.

そのため、プライマリーキーと外部キー制約でプライマリーキーを参照しているプロパティはlong型にします。

public class Item
{
    public long Id { get; set; }
    public int Price { get; set; }
    public long MemberId { get; set; }
    public string Name { get; set; }
    public Nullable<System.DateTime> SoldAt { get; set; }
    public virtual Member Member { get; set; }
}