C# で DbContext.Database.SqlQuery<TElement>(sql, params) をストアド プロシージャと共に使用する方法：代替アプローチ

C# で DbContext.Database.SqlQuery<TElement>(sql, params) をストアド プロシージャと共に使用する方法

このチュートリアルでは、Entity Framework (EF) Code First CTP5 で DbContext.Database.SqlQuery<TElement>(sql, params) メソッドを使用してストアド プロシージャを呼び出す方法を説明します。

手順

1. ストアド プロシージャの名前を指定する SQL クエリを作成します。クエリには、ストアド プロシージャに渡すパラメーターを含める必要があります。

EXEC MyStoredProcedure @parameter1, @parameter2

1. SqlQuery<TElement> メソッドを使用して、クエリを実行し、結果を TElement 型のリストに格納します。

var results = context.Database.SqlQuery<Customer>("EXEC MyStoredProcedure @parameter1, @parameter2", parameter1Value, parameter2Value);

1. 必要に応じて、結果を処理します。

例

次の例では、Customer クラスを表す Customer エンティティ タイプを使用して、GetCustomersByCity メソッドを作成します。このメソッドは、指定された都市に住む顧客のリストを返します。

public class Customer
{
    public int CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string City { get; set; }
}

public class MyDbContext : DbContext
{
    public MyDbContext() : base("MyContext")
    {
    }

    public DbSet<Customer> Customers { get; set; }
}

public class CustomerService
{
    private MyDbContext context;

    public CustomerService(MyDbContext context)
    {
        this.context = context;
    }

    public List<Customer> GetCustomersByCity(string city)
    {
        var results = context.Database.SqlQuery<Customer>("EXEC GetCustomersByCity @city", city);
        return results.ToList();
    }
}

この例では、GetCustomersByCity メソッドは、次の SQL クエリを実行します。

EXEC GetCustomersByCity @city

このクエリには、@city という名前のパラメーターが含まれています。このパラメーターには、メソッドに渡される都市名が割り当てられます。

クエリの結果は、Customer エンティティのリストに変換されます。このリストは、メソッドによって返されます。

• SqlQuery<TElement> メソッドは、生の SQL クエリを実行するために使用できます。これにより、EF の追跡機能を利用せずに、データベースからデータを直接取得できます。
• ストアド プロシージャを使用する場合は、クエリ文字列の先頭に EXEC キーワードを追加する必要があります。
• ストアド プロシージャにパラメーターを渡す場合は、クエリ文字列のパラメーター プレースホルダ (@parameterName) を実際の値に置き換える必要があります。

public class Customer
{
    public int CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string City { get; set; }
}

DbContext クラス

public class MyDbContext : DbContext
{
    public MyDbContext() : base("MyContext")
    {
    }

    public DbSet<Customer> Customers { get; set; }
}

サービス クラス

public class CustomerService
{
    private MyDbContext context;

    public CustomerService(MyDbContext context)
    {
        this.context = context;
    }

    public List<Customer> GetCustomersByCity(string city)
    {
        var parameters = new SqlParameter[]
        {
            new SqlParameter("@city", city)
        };

        var results = context.Database.SqlQuery<Customer>("EXEC GetCustomersByCity @city", parameters);
        return results.ToList();
    }
}

使用例

using (var context = new MyDbContext())
{
    var customerService = new CustomerService(context);

    var customers = customerService.GetCustomersByCity("Seattle");

    foreach (var customer in customers)
    {
        Console.WriteLine($"{customer.CustomerID} - {customer.FirstName} {customer.LastName} ({customer.City})");
    }
}

説明

• このコードは、Customer エンティティを表す Customer クラスを定義します。
• MyDbContext クラスは、Customer エンティティの DbSet を含む DbContext クラスを表します。
• CustomerService クラスは、Customer エンティティに対して CRUD 操作を実行するためのメソッドを提供します。
• GetCustomersByCity メソッドは、指定された都市に住む顧客のリストを返します。このメソッドは、DbContext.Database.SqlQuery<TElement> メソッドを使用して、GetCustomersByCity ストアド プロシージャを呼び出します。
• 使用例では、MyDbContext インスタンスを作成し、CustomerService インスタンスを初期化します。次に、GetCustomersByCity メソッドを使用して、"Seattle" に住む顧客のリストを取得します。最後に、リスト内の各顧客をコンソールに出力します。

追加のヒント

• ストアド プロシージャに複数の入出力パラメーターがある場合は、SqlParameter コレクションを使用してパラメーター値を指定できます。
• ストアド プロシージャが結果セットを返す場合は、SqlQuery<TElement> メソッドの代わりに SqlQuery メソッドを使用できます。このメソッドは、Object のリストを返します。
• ストアド プロシージャがリターン コードを返す場合は、ExecuteSqlCommand メソッドを使用できます。このメソッドは、int 値を返します。

FromSql メソッドは、生の SQL クエリを実行し、結果をエンティティまたは匿名型に変換するために使用できます。このメソッドは、ストアド プロシージャを呼び出す場合にも使用できます。

利点

• SqlQuery<TElement> メソッドよりも簡潔な構文
• ストアド プロシージャのパラメーターを自動的にマッピングできる

欠点

• ストアド プロシージャの結果セットが複雑な場合は、匿名型を使用する必要がある

var customers = context.Customers.FromSql("EXEC GetCustomersByCity @city", city: "Seattle");

ExecuteSqlCommand メソッド

ExecuteSqlCommand メソッドは、SQL コマンドを実行し、影響を受けた行数を返します。このメソッドは、ストアド プロシージャを呼び出す場合にも使用できます。

• ストアド プロシージャのリターン コードを取得できる
• DML 操作を実行する場合に役立つ

• 結果セットを返す場合は、手動で処理する必要がある
• FromSql メソッドほど簡潔ではない

var command = context.Database.CreateCommand();
command.CommandText = "EXEC GetCustomersByCity @city";
command.Parameters.AddWithValue("@city", "Seattle");

var result = command.ExecuteScalar();

if (result != null)
{
    Console.WriteLine($"影響を受けた行数: {result}");
}

LINQ クエリ

LINQ クエリを使用して、ストアド プロシージャを呼び出すこともできます。これを行うには、DbConnection 拡張メソッドを使用する必要があります。

• LINQ の構文を使用してストアド プロシージャを呼び出せる
• 結果セットをエンティティまたは匿名型に変換できる

• 複雑なストアド プロシージャ呼び出しには適していない

var customers = context.Database.Connection.Query<Customer>("EXEC GetCustomersByCity @city", new { city = "Seattle" });

ストアド プロシージャを呼び出す方法はいくつかあります。最良のアプローチは、特定のニーズによって異なります。

• シンプルで使いやすい方法が必要な場合は、FromSql メソッドがおすすめです。
• ストアド プロシージャのリターン コードを必要とする場合は、ExecuteSqlCommand メソッドがおすすめです。
• LINQ の構文を使用してストアド プロシージャを呼び出したい場合は、LINQ クエリを使用するのがおすすめです。