SQL クエリ

[アーティクル]
08/24/2023

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

ヒント

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

基本的な SQL クエリ

FromSql を使用して、SQL クエリに基づいた LINQ クエリを開始できます。

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

Note

FromSql は、EF Core 7.0 で導入されました。古いバージョンを使用する場合は、代わりに FromSqlInterpolated を使用してください。

SQL クエリを使用すれば、エンティティデータを返すストアドプロシージャを実行できます。

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

Note

FromSql は、DbSet 上でのみ直接使用できます。任意の LINQ クエリで構成することはできません。

パラメーターを渡す

警告

SQL クエリを使用する場合は、パラメーター化に細心の注意を払う

SQL クエリにユーザー指定の値を採用するときは、SQL インジェクション攻撃を防ぐための注意を払う必要があります。ユーザー指定の文字列値がプログラムによって SQL クエリに統合され、そのユーザー指定の値が、文字列を終了させ、別の悪意のある SQL 操作を実行するように細工されている場合に、SQL インジェクションが発生します。 SQL インジェクションの詳細については、こちらのページを参照してください。

FromSql および FromSqlInterpolated メソッドは、SQL インジェクションに対して安全であり、パラメーターデータを常に個別の SQL パラメーターとして統合します。ただし、使い方が適切でない場合、FromSqlRaw は SQL インジェクション攻撃に対して脆弱になる可能性があります。詳細については、以下を参照してください。

次の例では、パラメーターのプレースホルダーを SQL クエリ文字列に含め、追加の引数を指定することによって、ストアドプロシージャに 1 つのパラメーターを渡しています。

var user = "johndoe";

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

この構文は、正規の C# 文字列補間のように見えるかもしれませんが、提供された値は DbParameter にラップされ、生成されたパラメーター名は、{0} プレースホルダーが指定された場所に挿入されます。これにより FromSql は SQL インジェクション攻撃から保護され、値を効率的かつ正しくデータベースに送信します。

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

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

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

送信するデータベースパラメーターをより細かく制御する必要がある場合は、DbParameter を構築し、それをパラメーター値として指定することもできます。これにより、パラメーターの正確なデータベース型、またはそのサイズ、有効桁数、長さなどのファセットを設定できます。

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

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

Note

渡すパラメーターは、ストアドプロシージャの定義と完全に一致している必要があります。パラメーターの順序付けには特別な注意を払い、パラメーターを指定し忘れたり場所を間違えたりしないように気を付けてください。また、名前付きパラメーター表記の使用を検討してください。さらに、パラメーターの型が対応していること、それらのファセット (サイズ、精度、スケール) が必要に応じて設定されていることも確認してください。

動的 SQL とパラメーター

可能な限り、FromSql と、そのパラメーター化を使用する必要があります。ただし、SQL を動的に組み合わせる必要があるために、データベースパラメーターを使用できないというシナリオもあります。たとえば、フィルター処理されるプロパティの名前が C# 変数に保持されているとしましょう。次のような SQL クエリを使いたくなるかもしれません。

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

データベースでは列名 (またはスキーマの他の部分) をパラメーター化できないため、このコードは機能しません。

まず、SQL を介して、またはその他の方法でクエリを動的に構築した場合に、どのような結果になるかを考慮することが重要です。ユーザーによる列名を受け入れると、彼らはインデックスが作成されていない列を選択することができ、結果、クエリの実行速度が非常に遅くなり、データベースがオーバーロードされる可能性があります。また、あなたが公開したくないデータを含む列を、彼らが選択できる場合もあります。本当の意味で動的なシナリオの場合を除き、通常は、パラメーター化を使用して 2 つの列名を 1 つのクエリに折りたたむのではなく、2 つの列名に対して 2 つのクエリを使用することをお勧めします。

SQL を動的に作成することに決めた場合は、FromSqlRaw を使用する必要があります。これにより、データベースパラメーターを使用するのでなく、変数データを直接 SQL 文字列に補間できます。

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

上記のコードでは、C# 文字列補間を使用して、列名が SQL に直接挿入されます。この文字列値が安全であることを確認し、もし安全でない origin からのものである場合は自分の責任でサニタイズする必要があります。つまり、セミコロン、コメント、その他の SQL 構成要素などの特殊文字を検出し、それらを適切にエスケープするか、そのような入力を拒否するということです。

一方、列の値は DbParameter を介して送信されるため、SQL インジェクションが発生しても安全です。

警告

FromSqlRaw を使用する際は細心の注意を払い、値が安全な origin からのものであること、または適切にサニタイズされていることを常に確認してください。 SQL インジェクション攻撃は、ご利用のアプリケーションに多大な損害をもたらす可能性があります。

LINQ による作成

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

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"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
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

EF Core では提供された SQL をサブクエリとして扱うため、LINQ を使用して構成するには、SQL クエリが構成可能である必要があります。構成可能な SQL クエリは、通常、SELECT キーワードで始めます。そして次のようなサブクエリ内で無効である SQL 機能を含めることはできません。

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

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

変更の追跡

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

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

var searchTerm = "Lorem ipsum";

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

スカラー (非エンティティ) 型のクエリを実行する

Note

この機能は EF Core 7.0 で導入されました。

FromSql は、ご利用のモデルで定義されているエンティティに対してクエリを実行する場合に便利です。しかし、SqlQuery を使用すれば、下位レベルのデータアクセス API にドロップダウンすることなく、SQL を介してスカラー型の非エンティティ型に対して簡単にクエリを実行できます。たとえば、次のクエリでは、Blogs テーブルからすべての ID をフェッチします。

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

var ids = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToList();

var ids = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToList();

SQL クエリを使用して LINQ 演算子を作成することもできます。ただし、SQL はサブクエリとなり、その出力列は、追加する SQL EF によって参照される必要があるため、出力列に Value という名前を付ける必要があります。たとえば、次のクエリからは ID 平均を超える ID が返されます。

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

var overAverageIds = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

var overAverageIds = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql は、ご利用のデータベースプロバイダーでサポートされている任意のスカラー型で使用できます。データベースプロバイダーでサポートされていない型を使用する場合は、それに対して、規則の前の構成を使用して値変換を定義できます。

SqlQueryRaw を使用すると、FromSqlRaw がエンティティ型に対して行うのと同様に、SQL クエリを動的に構築できます。

非クエリ SQL の実行

一部のシナリオでは、通常、データベース内のデータを変更したり、結果セットを返さないストアドプロシージャを呼び出したりするために、データを返さない SQL を実行する必要があります。これは、ExecuteSql を使用して行います。

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

これにより、指定された SQL が実行され、変更された行数が返されます。 ExecuteSql では、FromSql と同様に安全なパラメーター化を使用することで、SQL インジェクションから保護します。ExecuteSqlRaw では、クエリのために FromSqlRaw で行うのと同様に、SQL クエリの動的な構築が可能です。

Note

EF Core 7.0 より前のバージョンでは、上記のように、ExecuteSql API を使用してデータベースに対して "一括更新" を実行することが必要な場合がありました。これは、一致するすべての行に対してクエリを実行してから、SaveChanges を使用してそれらを変更するよりもかなり効率的です。 EF Core 7.0 では ExecuteUpdate と ExecuteDelete を導入しました。これらを使用すれば、LINQ を介して効率的な一括更新操作を表現できるようになります。可能な限り、ExecuteSql ではなく、前述の API を使用することをお勧めします。

制限事項

SQL クエリからエンティティ型を返す場合、注意が必要な制限事項がいくつかあります。

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

次の方法で共有

SQL クエリ

基本的な SQL クエリ

パラメーターを渡す

動的 SQL とパラメーター

LINQ による作成

変更の追跡

スカラー (非エンティティ) 型のクエリを実行する

非クエリ SQL の実行

制限事項

その他のリソース

次の方法で共有

SQL クエリ

基本的な SQL クエリ

パラメーターを渡す

動的 SQL とパラメーター

LINQ による作成

関連データを含める

変更の追跡

スカラー (非エンティティ) 型のクエリを実行する

非クエリ SQL の実行

制限事項

その他のリソース