生 SQL クエリ

Entity Framework Core を使用すると、リレーショナル データベースを操作するときに生 SQL クエリにドロップ ダウンすることができます。 生 SQL クエリは、必要なクエリが LINQ を使用して表現できない場合に便利です。 また、生 SQL クエリは、LINQ クエリを使うと、効率の悪い SQL クエリになる場合にも使用されます。 生 SQL クエリは、通常のエンティティ型か、モデルの一部であるキーレス エンティティ型を返すことができます。

ヒント

この記事のサンプルは GitHub で確認できます。

基本的な生 SQL クエリ

FromSqlRaw 拡張メソッドを使用して、生 SQL クエリに基づいた LINQ クエリを開始できます。 FromSqlRaw は、DbSet<> に直接配置されている、クエリ ルートでのみ使用できます。

var blogs = context.Blogs
    .FromSqlRaw("SELECT * FROM dbo.Blogs")
    .ToList();

生 SQL クエリを使用してストアド プロシージャを実行できます。

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

パラメーターを渡す

警告

生 SQL クエリには常にパラメーター化を使用する

生 SQL クエリにユーザー指定の値を採用するときは、SQL インジェクション攻撃を防ぐための注意を払う必要があります。 そのような値に無効な文字が含まれていないことを検証するだけでなく、SQL テキストとは別に値を送信するパラメーター化を常に使用してください。

特に、検証されていないユーザー指定の値を含む連結された文字列や補間された文字列 ($"") を、FromSqlRawExecuteSqlRaw に渡さないでください。 FromSqlInterpolatedExecuteSqlInterpolated のメソッドでは、SQL インジェクション攻撃から保護されるように文字列補間構文を使用できます。

次の例では、パラメーターのプレースホルダーを SQL クエリ文字列に含め、追加の引数を指定することによって、ストアド プロシージャに 1 つのパラメーターを渡しています。 この構文は String.Format 構文のように見えるかもしれませんが、提供された値は DbParameter にラップされ、生成されたパラメーター名は、{0} プレースホルダーが指定された場所に挿入されます。

var user = "johndoe";

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

FromSqlInterpolatedFromSqlRaw に似ていますが、文字列補間構文を使用できます。 FromSqlRaw と同じように、FromSqlInterpolated はクエリ ルートでのみ使用できます。 前の例と同様に、値は DbParameter に変換され、SQL インジェクションに対して脆弱ではありません。

var user = "johndoe";

var blogs = context.Blogs
    .FromSqlInterpolated($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

また、DbParameter を構築し、それをパラメーター値として指定することもできます。 文字列のプレースホルダーではなく、通常の SQL パラメーターのプレースホルダーが使用されるため、FromSqlRaw を安全に使用できます。

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

FromSqlRaw では、SQL クエリ文字列で名前付きパラメーターを使用できます。これはストアド プロシージャに省略可能なパラメーターがある場合に便利です。

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser=@user", user)
    .ToList();

注意

パラメーターの順序付けSqlParameter[] 配列の順序に基づいて、Entity Framework Core によってパラメーターが渡されます。 複数の SqlParameter を渡す場合の SQL 文字列内の順序付けは、ストアド プロシージャの定義内のパラメーターの順序と一致する必要があります。 この手順を行わないと、そのプロシージャが実行されるときに、型変換の例外や予期しない動作が発生する可能性があります。

LINQ による作成

LINQ 演算子を使用して、最初の生 SQL クエリに基づいて構成することができます。 EF Core では、これをサブクエリとして扱い、データベースで構成します。 次の例では、テーブル値関数 (TVF) から選択する生 SQL クエリを使用します。 その後、LINQ を使用してそれを構成し、フィルター処理と並べ替えを行います。

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

上記のクエリでは、次の SQL が生成されます。

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

Include メソッドを使用して、他の LINQ クエリと同様に、関連データを含めることができます。

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

EF Core では提供された SQL がサブクエリとして扱われるため、LINQ を使用して構成するには、生 SQL クエリがコンポーザブルである必要があります。 SELECT キーワードで始まる SQL クエリを作成できます。 さらに、次のような、サブクエリでは無効な文字やオプションを、渡される SQL に含めることはできません。

  • 末尾のセミコロン
  • SQL Server では、末尾のクエリ レベル ヒント (例: OPTION (HASH JOIN))
  • SQL Server では、SELECT 句の OFFSET 0 または TOP 100 PERCENT と共に使用されない ORDER BY

SQL Server ではストアド プロシージャ呼び出しを構成することができないため、そのような呼び出しに追加のクエリ演算子を適用しようとすると、無効な SQL が発生します。 EF Core でストアド プロシージャの構成が試行されないようにするには、AsEnumerable または AsAsyncEnumerable メソッドの直後に FromSqlRaw または FromSqlInterpolated メソッドを使用します。

変更の追跡

FromSqlRaw または FromSqlInterpolated メソッドを使用するクエリでは、EF Core 内の他の LINQ クエリとまったく同じ変更追跡ルールに従います。 たとえば、クエリでエンティティ型を予測する場合、既定で結果は追跡されます。

次の例では、テーブル値関数 (TVF) から選択し、AsNoTracking の呼び出しを使用して変更追跡を無効にする生 SQL クエリを使用しています。

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

制限事項

生 SQL クエリを使用する場合、注意が必要な制限事項がいくつかあります。

  • SQL クエリは、エンティティ型のすべてのプロパティのデータを返す必要があります。
  • 結果セットの列名は、プロパティがマップされている列名と一致する必要があります。 この動作は EF6 とは異なることに注意してください。 EF6 では、生 SQL クエリのプロパティの列へのマッピングは無視され、結果セットの列名はプロパティ名と一致する必要がありました。
  • SQL クエリに関連データを含めることはできません。 ただし、多くの場合、Include 演算子を使用して関連データを返すクエリを作成することができます (「関連データを含める」を参照してください)。