LINQ to SQL 備忘録2 ストアドプロシージャ、ファンクション、SQLの直接発行の使用と遅延ローディングのメモ

概要:LINQ to SQL 備忘録1に続いて、今回はストアドプロシージャと、ファンクションとSQLを直接記述して発行する方法をLINQ to SQL で使用する方法を掲載します。遅延ローディングに関するメモも載せています。
投稿者：handcraft 公開日：11/9/2008 閲覧数:15312 評価者数：1

前回のLINQ to SQL 備忘録1に続いて、引き続き備忘録です。LINQ to SQL を使用するときには実際には、多くの場合でストアドを使用することになると思うので、ストアドプロシージャとファンクションを使用するためのサンプルとテストコードを掲載します。さらに、SQLを直接指定してEntity を作成する方法と、遅延ローディングについてのメモも掲載します。

今回もAdventureWorksデータベースを使用します。AdventureWorksの設定方法はこちらを参照して下さい。

動作確認環境は次の通りです。

実行OS: Windows Vista Enterprise
開発環境 : Visual Studio 2008 Professional
.NET 3.5

1. DataContext の拡張

ストアドやファンクション用のメソッドを定義するために、DataContextを継承して、AdventureWorksDataContextを定義します。DataContextを継承することでTable<TEntity> クラスをプロパティとして保持できるので、プログラムの可読性が増します。AdventureWorksではフィールドにTable<SalesOrder>型の SalesOrders フィールドとTable<Customer>型のCustomersフィールドを持っています。これらのフィールドはAdventureWorksDataContextを初期化するときにDataContextのコンストラクタ内でリフレクションにより自動的に初期化されるため、フィールドにインスタンスを設定する必要はありません。

/// <summary>
/// AdventureWorks用のDataContextクラスの作成
/// </summary>
public class AdventureWorksDataContext : DataContext
{
    public AdventureWorksDataContext(string fileOrServerOrConnection)
        : base(fileOrServerOrConnection) { }
    public AdventureWorksDataContext(IDbConnection connection)
        : base(connection) { }
    public AdventureWorksDataContext(string fileOrServerOrConnection, MappingSource mapping)
        : base(fileOrServerOrConnection, mapping) { }
    public AdventureWorksDataContext(IDbConnection connection, MappingSource mapping)
        : base(connection, mapping) { }

    /// <summary>
    /// エンティティの定義
    /// 初期時にベースクラス内でリフレクションを介して
    /// 初期化される。
    /// </summary>
    public Table<SalesOrder> SalesOrders;
    public Table<Customer> Customers;

    [Function(Name = "dbo.usp_GetOrderBySubTotal", IsComposable = false)]
    public ISingleResult<SalesOrder> SelectSalesOrdersBySubTotal(decimal subtotalFrom, decimal subtotalTo)
    {
        IExecuteResult executeResult = this.ExecuteMethodCall(this, MethodInfo.GetCurrentMethod() as MethodInfo, subtotalFrom, subtotalTo);

        // 複数のテーブル(ResultSet)を返す場合はIMultipleResultsを使用する
        ISingleResult<SalesOrder> salesOrders = (ISingleResult<SalesOrder>)executeResult.ReturnValue;
       return salesOrders;
    }
    [Function(Name = "dbo.usp_GetOrderAndCustomerBySubTotal", IsComposable = false)]
    [ResultType(typeof(SalesOrder))]
    [ResultType(typeof(Customer))]
    public IMultipleResults SelectSalesOrdersAndCustomersBySubTotal(decimal subtotalFrom, decimal subtotalTo)
    {
        IExecuteResult executeResult = this.ExecuteMethodCall(this, MethodInfo.GetCurrentMethod() as MethodInfo, subtotalFrom, subtotalTo);

        IMultipleResults salesOrdersAndCustomers = (IMultipleResults)executeResult.ReturnValue;
        return salesOrdersAndCustomers;
    }
    /// <summary>
    /// スカラー関数定義.NULLの可能性がある場合は、値やパラメータをNULL許容型にする(decimal?)。
    /// </summary>
    [Function(Name = "dbo.ufn_ToYen", IsComposable = true)]
    public decimal ToYen(decimal subtotal)
    {
        IExecuteResult executeResult = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), subtotal);
        return (decimal)executeResult.ReturnValue;
    }
    [Function(Name = "dbo.ufn_GetCustomerByFirstName", IsComposable = true)]
    public IQueryable<Customer> GetCustomerByName(string firstName)
    {
        return this.CreateMethodCallQuery<Customer>(this, (MethodInfo)MethodInfo.GetCurrentMethod(), firstName);
    }
}

2. ストアドプロシージャの定義(FunctionAttributeの使用)

1で掲載したAdventureWorks.SelectSalesOrdersBySubTotalメソッドと、SelectSalesOrdersAndCustomersBySubTotalメソッドがストアドプロシージャを現しています。ストアドプロシージャであることを定義するためには、 FunctionAttribute を使用します。 FunctionAttribute の Name プロパティにプロシージャの名前を設定し、 IComposable プロパティをfalse にします。後述しますが、IComposable を true にするとファンクションの定義になります。各メソッドの返り値ですが、シングル ResultSet がストアドプロシージャの返り値の場合は ISingleResult<TEntity> を使用します。マルチ ResultSet の場合は IMultiResults型を返り値とします。ストアドプロシージャの処理を呼び出すためには、サンプルのように、ExecutionMethodを使用します。

サンプルで使用されているは次のように定義されています。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'[dbo].[usp_GetOrderBySubTotal]') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_GetOrderBySubTotal]
GO
-- =============================================
-- Subtotalの範囲検索を行う
-- =============================================
CREATE PROCEDURE dbo.usp_GetOrderBySubTotal
	 @subtotalFrom money
	,@subtotalTo money
AS
BEGIN
	SET NOCOUNT ON;

	SELECT T1.*
      FROM Sales.SalesOrderHeader T1
     WHERE T1.SubTotal < @subtotalTo and T1.SubTotal > @subtotalFrom
END
GO

IF OBJECT_ID(N'[dbo].[usp_GetOrderAndCustomerBySubTotal]') IS NOT NULL
    DROP PROCEDURE [dbo].[usp_GetOrderAndCustomerBySubTotal]
GO
-- =============================================
-- Subtotalの範囲検索を行い、該当するCustomerも検索する
-- =============================================
CREATE PROCEDURE dbo.usp_GetOrderAndCustomerBySubTotal
	 @subtotalFrom money
	,@subtotalTo money
AS
BEGIN
	SET NOCOUNT ON;

	SELECT T1.*
      FROM Sales.SalesOrderHeader T1
     WHERE T1.SubTotal < @subtotalTo and T1.SubTotal > @subtotalFrom;

    SELECT T2.*
      FROM Person.Contact T2
     WHERE EXISTS(
            SELECT T1.*
              FROM Sales.SalesOrderHeader T1
             WHERE T1.SubTotal < @subtotalTo and T1.SubTotal > @subtotalFrom
               AND T1.CustomerID = T2.ContactID
           );
END
GO

サンプルプログラムを次のように2つ用意します。

/// <summary>
/// シングルのResultSetを返すストアドプロシージャ
/// を使用するパターン
/// </summary>
public static void StoredSalesOrder()
{
    AdventureWorksDataContext dc = new AdventureWorksDataContext(ConnectionString);

    foreach (var item in dc.SelectSalesOrdersBySubTotal(10.5m, 12.5m))
    {
        Console.WriteLine(item.SubTotal);
    }
}
/// <summary>
/// 複数のResultSetを返すストアドプロシージャを
/// 使用するパターン.選択されるResultSetの順番で
/// GetResult<TEntity>を呼び出すこと
/// </summary>
public static void StoredSalesOrderMultiple()
{
    AdventureWorksDataContext dc = new AdventureWorksDataContext(ConnectionString);

    IMultipleResults salesAndCustomers = dc.SelectSalesOrdersAndCustomersBySubTotal(10.5m, 12.5m);
    foreach (var item in salesAndCustomers.GetResult<SalesOrder>())
    {
        Console.WriteLine(item.SubTotal);
    }
    foreach (var item in salesAndCustomers.GetResult<Customer>())
    {
        Console.WriteLine(item.FirstName + item.LastName);
    }
}

StoredSalesOrderの実行結果は次のようになります。

StoredSalesOrderMultipleの実行結果は次のようになります。

3. ユーザ定義ファンクションの定義( FunctionAttribute の使用)

ユーザ定義のファンクションをLINQ to SQL で使用する場合も、メソッドに FunctionAttribute を使用することで実現します。ただし、ファンクションの場合は FunctionAttribute の IsComporsable を false にします。これにより、クエリ式内で、AdventureWorksDataContextで定義したファンクション用のメソッドを使用できるようになります。ファンクションのサンプルは1で掲載した AdventureWorksDataContext の ToYen と GetCustomerByName メソッドです。ToYen はスカラー関数のユーザ定義ファンクションを表し、 GetCustomerByName はテーブル関数を表します。2つの違いは、SQLを作成するときに使用するメソッドの違いです。スカラー関数の場合は、ExecuteMethodCall を使用し、テーブル関数の場合は、 CreateMethodCallQuery<TEntity>()を使用します。

サンプルで使用するファンクションは次のように定義されています。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'[dbo].[ufn_ToYen]') IS NOT NULL
    DROP FUNCTION [dbo].[ufn_ToYen]
GO

-- 円に直して比較
CREATE FUNCTION dbo.ufn_ToYen
(
	@subtotal money
)
RETURNS money
AS
BEGIN
	RETURN @subtotal * 98.0;
END
GO

IF OBJECT_ID(N'[dbo].[ufn_GetCustomerByFirstName]') IS NOT NULL
    DROP FUNCTION [dbo].[ufn_GetCustomerByFirstName]
GO

-- FirstNameに前方一致するContactのレコードを取得
CREATE FUNCTION dbo.ufn_GetCustomerByFirstName
(
	@firstName nvarchar(50)
)
RETURNS TABLE
AS
     RETURN 
	 SELECT T1.*
	  FROM Person.Contact T1
     WHERE T1.FirstName LIKE @firstName + '%';
GO

テスト用のメソッドを次のように定義し、実行しまうす。

/// <summary>
/// スカラー関数テスト
/// </summary>
public static void ScalarFunctionSalesOrder()
{
    AdventureWorksDataContext dc = new AdventureWorksDataContext(ConnectionString);
    dc.Log = Console.Out;
    var query = from o in dc.SalesOrders
                where 800 > dc.ToYen(o.SubTotal)
                  && 700 < dc.ToYen(o.SubTotal)
                select o;
    foreach (var item in query.Take(5))
    {
        Console.WriteLine("ID:" + item.SalesOrderID + "SubTotal:" + item.SubTotal);
    }
}
/// <summary>
/// テーブル関数のテスト
/// </summary>
public static void TableFunctionCustomer()
{
    AdventureWorksDataContext dc = new AdventureWorksDataContext(ConnectionString);
    dc.Log = Console.Out;

    var query = from c in dc.GetCustomerByName("Fr")
                join o in dc.SalesOrders
                    on c.CustomerID equals o.CustomerID
                    into co
                where c.LastName.Length > 8
                select new { c.FirstName, c.LastName, Count = co.Count(),  Total = (decimal?) co.Sum(order => order.SubTotal)};
    foreach (var item in query)
    {
        Console.WriteLine(item.FirstName + " " + item.LastName + " Total:" + item.Total + " Count:" + item.Count);
    }
}

ScalarFunctionSalesOrder()の実行結果は次のようになります。

TableFunctionCustomer()の実行結果は次のようになります。

4. SQL を直接指定してエンティティクラスを作成する

LINQ to SQL では　SQL を直接指定して Entity クラスのインスタンスを生成できます。SQL を直接指定する場合は ExecuteQuery<TEntity> を使用します。オーバーロード版を使用すると、パラメタ付のクエリを発行できます。パラメタは中括弧にインデックスを指定して記述します。パラメタは内部的に@p0,@p1などのパラメタ名に置換され、パラメタライズドクエリとしてSQLが発行されます。テストコードは次のようになります。

public static void DirectSelectSalesOrders()
{
    AdventureWorksDataContext dc = new AdventureWorksDataContext(ConnectionString);

    StringBuilder sql = new StringBuilder();
    sql.Append("	SELECT T1.* ");
    sql.Append("      FROM Sales.SalesOrderHeader T1 ");
    sql.Append("     WHERE T1.SubTotal < {0} and T1.SubTotal > {1} ");

    var query = dc.ExecuteQuery<SalesOrder>(sql.ToString(), 800, 700);

    foreach (var item in query.Take(5))
    {
        Console.WriteLine("ID:" + item.SalesOrderID + "SubTotal:" + item.SubTotal);
    }
}

上記のテストコード内の SQL は2で作成したシングル ResultSet を返すプロシージャと同じなので、 StoredSalesOrder メソッドの実行結果と同じになります。

5. 遅延ローディングについてのメモ

遅延ローディング既定で有効になっています。遅延ローディングの有効無効の指定は DataContext.DeferredLoadingEnabled プロパティで設定します。DeferredLoadingEnabled が false の場合でも、DataLoadOptions で同時ロードオプションが DataLoadOptions.LoadWith<TEntity>() によって設定していた場合は、同時ロードは有効になります。

既定で有効になっている DataContext の変更追跡サービス(Change Tracking Service)がObjectTrackingEnabled プロパティを false に指定することにより無効になっていた場合は、DeferredLoadingEnabled は常に false と判断されます。変更追跡サービスを無効にすると、DataContext は選択処理のみ(読み取り専用)となるので、パフォーマンスが向上する可能性があります。

個別のプロパティに対する遅延ロード
遅延ロードはプロパティに対して個別に設定できます。フィールドに対しては行えません。これは、記事テーブルなどで、記事の本文のみ必要に応じてロードするほうが効率がよい場合などに有効です。プロパティの遅延ロードを有効にするには、ストレージとして使用するフィールドをLink<T>型(Tは実際の列に対応する.NETの型)として定義し、次のようにプロパティを指定します。

private Link<string> _body;
[Column(Storage = "_body")]
public string Body{
get{ return _body.Value; }
set{ _body.Value = value; }
}

今回の説明は以上です。間違い、指摘点などがありましたら、ご連絡ください。

記事を評価する

名前
Eメール(option)
Webサイト(option)
コメント
キャプチャの答え

Netplanetes Memo

技術的な備忘録,プログラミングサンプル,ツールを公開しているサイトです