Entity Framework - パラメータを渡してSQLを実行する

Entity Frameworkを使って直接SQLを実行したいときがあって、さらにパラメータの渡し方が気になったので試していました。

まず直接SQLを実行するメソッドは2つ用意されています。

SqlQueryはselect文、ExecuteSqlCommandはinsert・update・delete文などを実行するのに使います。（厳密にはSqlQueryはDbRawSqlQueryを返します。）どちらのメソッドもパラメータをObject型の可変長引数として渡すことができます。

今回はSqlQueryでパラメータを渡してみたいと思います。

まずはテスト用のエンティティとDbContextを用意して、

class Item {
    public int Id { get; set; }
    public string Name { get; set; }
}

class AppDbContext : DbContext {
    private static readonly string _connectionString
        = new SqlConnectionStringBuilder {
            DataSource = ".",
            IntegratedSecurity = true,
            InitialCatalog = "Test"
        }.ToString();

    public AppDbContext()
        : base(_connectionString) {
    }
}

とりあえずパラメータを使わないでSQLを実行してみます。

using (var context = new AppDbContext()) {
    var item = context.Database.SqlQuery<Item>(
        @"select 1 as Id, N'Aaa' as Name;").First();
    Console.WriteLine("{0}: {1}", item.Id, item.Name);   // 1: Aaa
}

引数に直接パラメータを渡す

パラメータをそのまま渡すと、SQLでは@p0、@p1...といった名前でパラメータを使うことができます。

using (var context = new AppDbContext()) {
    var item = context.Database.SqlQuery<Item>(
        @"select @p0 as Id, @p1 as Name;",
        2, "Bbb").First();
    Console.WriteLine("{0}: {1}", item.Id, item.Name);   // 2: Bbb
}

引数にSqlPamameterを渡す

引数にSqlParameterを使うと、任意のパラメータ名を指定できます。こっちの方が分かりやすいですが、ちょっとめんどくさいですね・・・。

using (var context = new AppDbContext()) {
    var item = context.Database.SqlQuery<Item>(
        @"select @id as Id, @name as Name;",
        new SqlParameter("@id", 3), new SqlParameter("@name", "Ccc")).First();
    Console.WriteLine("{0}: {1}", item.Id, item.Name);   // 3: Ccc
}

おまけ（Dapperを使う）

Database.ConnectionプロパティでDbConnectionがとれるので、Dapperを使った方がすっきり書けますね。

using (var context = new AppDbContext()) {
    var item = context.Database.Connection.Query<Item>(
        @"select @id as Id, @name as Name;",
        new { id = 4, name = "Ddd" }).First();
    Console.WriteLine("{0}: {1}", item.Id, item.Name);   // 4: Ddd
}