生 SQL クエリRaw SQL Queries

Entity Framework Core を使用すると、リレーショナル データベースを操作するときに生 SQL クエリにドロップ ダウンすることができます。Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. 生 SQL クエリは、必要なクエリが LINQ を使用して表現できない場合に便利です。Raw SQL queries are useful if the query you want can't be expressed using LINQ. また、生 SQL クエリは、LINQ クエリを使うと、効率の悪い SQL クエリになる場合にも使用されます。Raw SQL queries are also used if using a LINQ query is resulting in an inefficient SQL query. 生 SQL クエリは、通常のエンティティ型か、モデルの一部であるキーレス エンティティ型を返すことができます。Raw SQL queries can return regular entity types or keyless entity types that are part of your model.

ヒント

この記事のサンプルは GitHub で確認できます。You can view this article's sample on GitHub.

基本的な生 SQL クエリBasic raw SQL queries

FromSqlRaw 拡張メソッドを使用して、生 SQL クエリに基づいた LINQ クエリを開始できます。You can use the FromSqlRaw extension method to begin a LINQ query based on a raw SQL query. FromSqlRaw は、DbSet<> に直接配置されている、クエリ ルートでのみ使用できます。FromSqlRaw can only be used on query roots, that is directly on the DbSet<>.

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

生 SQL クエリを使用してストアド プロシージャを実行できます。Raw SQL queries can be used to execute a stored procedure.

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

パラメーターを渡すPassing parameters

警告

生 SQL クエリには常にパラメーター化を使用するAlways use parameterization for raw SQL queries

生 SQL クエリにユーザー指定の値を採用するときは、SQL インジェクション攻撃を防ぐための注意を払う必要があります。When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. そのような値に無効な文字が含まれていないことを検証するだけでなく、SQL テキストとは別に値を送信するパラメーター化を常に使用してください。In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text.

特に、検証されていないユーザー指定の値を含む連結された文字列や補間された文字列 ($"") を、FromSqlRawExecuteSqlRaw に渡さないでください。In particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. FromSqlInterpolatedExecuteSqlInterpolated のメソッドでは、SQL インジェクション攻撃から保護されるように文字列補間構文を使用できます。The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

次の例では、パラメーターのプレースホルダーを SQL クエリ文字列に含め、追加の引数を指定することによって、ストアド プロシージャに 1 つのパラメーターを渡しています。The following example passes a single parameter to a stored procedure by including a parameter placeholder in the SQL query string and providing an additional argument. この構文は String.Format 構文のように見えるかもしれませんが、提供された値は DbParameter にラップされ、生成されたパラメーター名は、{0} プレースホルダーが指定された場所に挿入されます。While this syntax may look like String.Format syntax, the supplied value is wrapped in a DbParameter and the generated parameter name inserted where the {0} placeholder was specified.

var user = "johndoe";

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

FromSqlInterpolatedFromSqlRaw に似ていますが、文字列補間構文を使用できます。FromSqlInterpolated is similar to FromSqlRaw but allows you to use string interpolation syntax. FromSqlRaw と同じように、FromSqlInterpolated はクエリ ルートでのみ使用できます。Just like FromSqlRaw, FromSqlInterpolated can only be used on query roots. 前の例と同様に、値は DbParameter に変換され、SQL インジェクションに対して脆弱ではありません。As with the previous example, the value is converted to a DbParameter and isn't vulnerable to SQL injection.

注意

バージョン 3.0 より前では、FromSqlRawFromSqlInterpolated は、FromSql という名前の 2 つのオーバーロードでした。Prior to version 3.0, FromSqlRaw and FromSqlInterpolated were two overloads named FromSql. 詳細については、以前のバージョンに関するセクションを参照してください。For more information, see the previous versions section.

var user = "johndoe";

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

また、DbParameter を構築し、それをパラメーター値として指定することもできます。You can also construct a DbParameter and supply it as a parameter value. 文字列のプレースホルダーではなく、通常の SQL パラメーターのプレースホルダーが使用されるため、FromSqlRaw を安全に使用できます。Since a regular SQL parameter placeholder is used, rather than a string placeholder, FromSqlRaw can be safely used:

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

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

FromSqlRaw では、SQL クエリ文字列で名前付きパラメーターを使用できます。これはストアド プロシージャに省略可能なパラメーターがある場合に便利です。FromSqlRaw allows you to use named parameters in the SQL query string, which is useful when a stored procedure has optional parameters:

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

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

LINQ による作成Composing with LINQ

LINQ 演算子を使用して、最初の生 SQL クエリに基づいて構成することができます。You can compose on top of the initial raw SQL query using LINQ operators. EF Core では、これをサブクエリとして扱い、データベースで構成します。EF Core will treat it as subquery and compose over it in the database. 次の例では、テーブル値関数 (TVF) から選択する生 SQL クエリを使用します。The following example uses a raw SQL query that selects from a Table-Valued Function (TVF). その後、LINQ を使用してそれを構成し、フィルター処理と並べ替えを行います。And then composes on it using LINQ to do filtering and sorting.

var searchTerm = ".NET";

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

上記のクエリでは、次の SQL が生成されます。Above query generates following 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 クエリと同様に、関連データを含めることができます。The Include method can be used to include related data, just like with any other LINQ query:

var searchTerm = ".NET";

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

EF Core では提供された SQL がサブクエリとして扱われるため、LINQ を使用して構成するには、生 SQL クエリがコンポーザブルである必要があります。Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. SELECT キーワードで始まる SQL クエリを作成できます。SQL queries that can be composed on begin with the SELECT keyword. さらに、次のような、サブクエリでは無効な文字やオプションを、渡される SQL に含めることはできません。Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

  • 末尾のセミコロンA trailing semicolon
  • SQL Server では、末尾のクエリ レベル ヒント (例: OPTION (HASH JOIN))On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
  • SQL Server では、SELECT 句の OFFSET 0 または TOP 100 PERCENT と共に使用されない ORDER BYOn SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

SQL Server ではストアド プロシージャ呼び出しを構成することができないため、そのような呼び出しに追加のクエリ演算子を適用しようとすると、無効な SQL が発生します。SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. EF Core でストアド プロシージャの構成が試行されないようにするには、AsEnumerable または AsAsyncEnumerable メソッドの直後に FromSqlRaw または FromSqlInterpolated メソッドを使用します。Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.

変更追跡Change Tracking

FromSqlRaw または FromSqlInterpolated メソッドを使用するクエリでは、EF Core 内の他の LINQ クエリとまったく同じ変更追跡ルールに従います。Queries that use the FromSqlRaw or FromSqlInterpolated methods follow the exact same change tracking rules as any other LINQ query in EF Core. たとえば、クエリでエンティティ型を予測する場合、既定で結果は追跡されます。For example, if the query projects entity types, the results will be tracked by default.

次の例では、テーブル値関数 (TVF) から選択し、AsNoTracking の呼び出しを使用して変更追跡を無効にする生 SQL クエリを使用しています。The following example uses a raw SQL query that selects from a Table-Valued Function (TVF), then disables change tracking with the call to AsNoTracking:

var searchTerm = ".NET";

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

制限事項Limitations

生 SQL クエリを使用する場合、注意が必要な制限事項がいくつかあります。There are a few limitations to be aware of when using raw SQL queries:

  • SQL クエリは、エンティティ型のすべてのプロパティのデータを返す必要があります。The SQL query must return data for all properties of the entity type.
  • 結果セットの列名は、プロパティがマップされている列名と一致する必要があります。The column names in the result set must match the column names that properties are mapped to. この動作は EF6 とは異なることに注意してください。Note this behavior is different from EF6. EF6 では、生 SQL クエリのプロパティの列へのマッピングは無視され、結果セットの列名はプロパティ名と一致する必要がありました。EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.
  • SQL クエリに関連データを含めることはできません。The SQL query can't contain related data. ただし、多くの場合、Include 演算子を使用して関連データを返すクエリを作成することができます (「関連データを含める」を参照してください)。However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

以前のバージョンPrevious versions

EF Core バージョン 2.2 以前では、FromSql というメソッドの 2 つのオーバーロードがありました。これは新しい FromSqlRawFromSqlInterpolated と同じように動作しました。EF Core version 2.2 and earlier had two overloads of method named FromSql, which behaved in the same way as the newer FromSqlRaw and FromSqlInterpolated. 挿入文字列メソッドを呼び出すつもりが、誤って生文字列メソッドを簡単に呼び出せてしまいました。その逆も同様です。It was easy to accidentally call the raw string method when the intent was to call the interpolated string method, and the other way around. 誤って間違ったオーバーロードを呼び出すと、クエリがパラメーター化される必要があるときにそうならない可能性があります。Calling wrong overload accidentally could result in queries not being parameterized when they should have been.