生 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. この方法は、実行するクエリが LINQ を使用して表現できない場合や、LINQ クエリを使用すると非効率的な SQL がデータベースに送信される場合に役立ちます。This can be useful if the query you want to perform can't be expressed using LINQ, or if using a LINQ query is resulting in inefficient SQL being sent to the database. 生 SQL クエリは、エンティティ型か、モデルの一部であるクエリ型 (EF Core 2.1 以降) を返すことができます。Raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model.

ヒント

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

制限事項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 or query type.

  • 結果セットの列名は、プロパティがマップされている列名と一致する必要があります。The column names in the result set must match the column names that properties are mapped to. これは EF6 と異なる点です。EF6 では、生 SQL クエリのプロパティ/列のマッピングは無視され、結果セットの列名はプロパティ名と一致する必要がありました。Note this is different from EF6 where property/column mapping was ignored for raw SQL queries and result set column names had to match the property names.

  • SQL クエリに関連データを含めることはできません。The SQL query cannot 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).

  • このメソッドに渡された SELECT ステートメントは、一般的にコンポーザブルである必要があります。EF Core がサーバー上で追加のクエリ演算子を評価する必要がある場合 (たとえば、FromSql の後に適用される LINQ 演算子を変換する場合)、指定された SQL はサブクエリとして扱われます。SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (for example, to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery. これは、渡される SQL に、次のようなサブクエリでは無効な文字またはオプションを含めてはならないことを意味します。This means that the SQL passed should not contain any characters or options that are not 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 句の TOP 100 PERCENT を伴わない ORDER BYOn SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
  • SELECT 以外の SQL ステートメントは、自動的に非コンポーザブルと認識されます。SQL statements other than SELECT are recognized automatically as non-composable. その結果、ストアド プロシージャのすべての結果が常にクライアントに返され、FromSql の後に適用されたすべての LINQ 演算子はメモリ内で評価されます。As a consequence, the full results of stored procedures are always returned to the client and any LINQ operators applied after FromSql are evaluated in-memory.

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

FromSql 拡張メソッドを使用して、生 SQL クエリに基づいた LINQ クエリを開始できます。You can use the FromSql extension method to begin a LINQ query based on a raw SQL query.

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

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

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

パラメーターを渡すPassing parameters

SQL を受け取る API の場合と同様に、SQL インジェクション攻撃から保護するには、ユーザー入力をパラメーター化することが重要です。As with any API that accepts SQL, it is important to parameterize any user input to protect against a SQL injection attack. SQL クエリ文字列にパラメーターのプレースホルダーを含めて、追加の引数としてパラメーター値を指定することができます。You can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. 指定したパラメーター値は自動的に DbParameter に変換されます。Any parameter values you supply will automatically be converted to a DbParameter.

次の例では、ストアド プロシージャに 1 つのパラメーターを渡しています。The following example passes a single parameter to a stored procedure. これは String.Format 構文のように見えますが、指定された値はパラメーターにラップされ、生成されたパラメーター名は {0} プレースホルダーが指定された場所に挿入されます。While this may look like String.Format syntax, the supplied value is wrapped in a parameter and the generated parameter name inserted where the {0} placeholder was specified.

var user = "johndoe";

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

これは同じクエリですが、EF Core 2.0 以降でサポートされている文字列補間構文を使用しています。This is the same query but using string interpolation syntax, which is supported in EF Core 2.0 and above:

var user = "johndoe";

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

また、DbParameter を構築し、それをパラメーター値として指定することもできます。You can also construct a DbParameter and supply it as a parameter value. こうすることで、SQL クエリ文字列に名前付きパラメーターを使用できますThis allows you to use named parameters in the SQL query string

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

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

LINQ による作成Composing with LINQ

データベース内で SQL クエリを作成できる場合は、LINQ 演算子を使用して初期の生 SQL クエリ上に作成することができます。If the SQL query can be composed on in the database, then you can compose on top of the initial raw SQL query using LINQ operators. SQL クエリは、SELECT キーワードを使用して作成できます。SQL queries that can be composed on being with the SELECT keyword.

次の例では、テーブル値関数 (TVF) から選択し、LINQ を使用してフィルター処理と並べ替えを実行する生 SQL クエリを使用します。The following example uses a raw SQL query that selects from a Table-Valued Function (TVF) and then composes on it using LINQ to perform filtering and sorting.

var searchTerm = ".NET";

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

LINQ 演算子による作成を使用して、関連データをクエリに含めることができます。Composing with LINQ operators can be used to include related data in the query.

var searchTerm = ".NET";

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

警告

生 SQL クエリには常にパラメーター化を使用する: FromSqlExecuteSqlCommand のような生 SQL 文字列を受け取る API では、値をパラメーターとして簡単に渡すことができます。Always use parameterization for raw SQL queries: APIs that accept a raw SQL string such as FromSql and ExecuteSqlCommand allow values to be easily passed as parameters. ユーザーの入力を検証するだけでなく、生 SQL クエリ/コマンドで使用される値には常にパラメーター化を使用してください。In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. 文字列の連結を使用してクエリ文字列の一部を動的に構築する場合は、SQL インジェクション攻撃から保護するために入力を検証する必要があります。If you are using string concatenation to dynamically build any part of the query string then you are responsible for validating any input to protect against SQL injection attacks.