WebSurfer's Home

トップ > Blog 1   |   ログイン
APMLフィルター

EF Core で SQL Server に渡される SQL 文の確認

by WebSurfer 2021年2月19日 16:27

開発時に Entity Framework から SQL Server に渡される SQL 文を確認したいことがあると思います。EF6 と違って EF Core ではそれは少々面倒だったのですが、EF Core 5.0 で導入された「シンプルなログ」という機能を使えば EF6 並みに簡単にできるようになったとのことなので試してみました。

EF Core の SQL 文

その「シンプルなログ」機能を使って EF Core が SQL Server に発行した SQL 文を Visual Studio の出力ウィンドウに表示したのが上の画像です。

基本的には DbContextOptionsBuilder.LogTo メソッドを使ってコンテキストクラスにアクセスして EF Core ログを取得し、指定の場所にログを書き出すという操作になるようです。

NuGet パッケージの追加、構成の変更、コードの大幅な書き換えなどは必要なく、Microsoft のドキュメント「シンプルなログ」に書かれているように、コンテキストクラスの OnConfiguring メソッドに一行追加するだけで EF Core ログの取得が可能になります。

具体的には、例えば先の記事「スキャフォールディング機能 (CORE)」でリバースエンジニアリングで作成したコンテキストクラスを例に取ると、以下のように自動生成された OnConfiguring メソッドの中で LogTo メソッドを使って EF Core ログを取得し、それを Debug.WriteLine メソッドで書き出すという一行を追加すれば OK です。

using Microsoft.EntityFrameworkCore;
using MvcCore5App.Models;

namespace MvcCore5App.DAL
{
    public partial class NorthwindContext : DbContext
    {
        public NorthwindContext(DbContextOptions<NorthwindContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Category> Categories { get; set; }
        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<Supplier> Suppliers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // 出力ウィンドウに EF Core ログを表示
            optionsBuilder.LogTo(msg => System.Diagnostics.Debug.WriteLine(msg));

            // ・・・中略・・・
        }

        // ・・・中略・・・
    }
}

上の LogTo メソッドにより、Visual Studio でデバッグ実行した際に EF Core ログが出力ウィンドウに表示され、その中に EF Core から SQL Server に渡される SQL 文が含まれるようになります。

例えば、以下のような Linq to Entities 文をコントローラーのアクションメソッドに書いて Visual Studio でデバッグ実行し、そのアクションメソッドをブラウザから呼び出してから出力ウィンドウを見ると、この記事の一番上の画像の SQL 文が発行されているのが確認できます。

namespace MvcCore5App.Controllers
{
    public class ProductsController : Controller
    {
        private readonly NorthwindContext _context;

        public ProductsController(NorthwindContext context)
        {
            _context = context;
        }

        public IActionResult Index()
        {
            // どのような SQL 文が投げられるかの検証用
            var list = _context.Products.OrderBy(p => p.ProductId).
                                         Skip(20).Take(10).ToList();

            // ・・・中略・・・
        }

        // ・・・中略・・・
    }
}

上に紹介した「シンプルなログ」は開発時限定でログを取ることを目的としているようで、運用時などでのログの取得には別の機能があるそうです。詳しくは Microsoft のドキュメント「ログとインターセプトの概要」を見てください。


最後に、EF6 で SQL Server に渡される SQL 文を確認する方法もついでに書いておきます。下の画像が EF6 のログを出力ウィンドウに書き出して、その中に含まれる SQL 文の部分を示したものです。

EF6 の SQL 文

基本的には EF Core と同様にコンテキストクラスからログを得て Visual Studio の出力ウィンドウに書き出すのですが、それには DbContext.Database.Log プロパティ を以下のように使います。

namespace Mvc5App.Controllers
{
    public class ProductsController : Controller
    {
        private NORTHWINDEntities db = new NORTHWINDEntities();

        public ActionResult Index()
        {
            // 出力ウィンドウに EF6 ログを表示
            db.Database.Log = msg => System.Diagnostics.Debug.Write(msg);

            var list = db.Products.OrderBy(p => p.ProductID).
                                   Skip(20).Take(10).ToList();

            // ・・・中略・・・
        }
    }
}

元々はログを取るための機能で、SQL 文を確認するだけでなく他にもいろいろ情報が取得できるようです。詳しくは Microsoft のドキュメント「データベース操作のログ記録と受信」を見てください。

Tags: , , ,

ADO.NET

Linq の GroupBy

by WebSurfer 2020年12月4日 12:33

Linq to Objects で GroupBy メソッドを使って IEnumerable<IGrouping<TKey, TElement>> オブジェクトを取得できるということを備忘録として書いておきます。

SELECT クエリ

SQL 文の GROUP BY 句はグループごとの集計を取るために使われるものと理解しています。例えば上の画像にあるように、Products テーブルから SupplierID 別に商品単価の最高と最低の値を取得するというように。(DB は Microsoft が提供するサンプルデータベース Northwind です)

なので、GROUP BY 句で指定したフィールド(上の例では SupplierID)以外や、最大値と最小値を返す MAX(), MIN()、合計を返す SUM() や平均を返す AVG() など以外を SELECT することはできません。

例えば、上の画像の SELECT クエリで ProductName を SELECT しようとすると "列 'NORTHWIND.dbo.Products.ProductName' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません" というエラーになります。

Linq でも同様で、下のような Select メソッドなしで使うものではないと思っていました。

var result = await _context.Products.GroupBy(p => p.SupplierId).
                   Select(g => new 
                   { 
                       Id = g.Key, 
                       Max = g.Max(g => g.UnitPrice), 
                       Min = g.Min(g => g.UnitPrice) 
                   }).
                   ToListAsync();

Select メソッドなしで使うと、例えば以下のようにすると "Client side GroupBy is not supported" というエラーになります。(注: これは Core 3.0 以降の話で、Core 2.x 以前はサポートされていたそうです。.NET Framework ��� EF でもサポートされています。詳しくは Breaking changes included in EF Core 3.x の最初のセクション LINQ queries are no longer evaluated on the client を見てください)

var result1 = await _context.Products.GroupBy(p => p.SupplierId).
                    ToListAsync();

// または

var result2 = await (from p in _context.Products
                     group p by p.SupplierId into g
                     select g).ToListAsync();

そのエラーの原因は Linq to Entities で上のような GroupBy を使った Linq 式は SQL 文に変換できないということのようです。(ただし、Core 2.x 以前と .NET Framework では EF 側でオブジェクトの生成をサポートしていて、上記のコードでも OK です)

でも、Linq to Objects の場合は話が違ってきます。以下のコードは ASP.NET Core MVC のアクションメソッドですが、IEnumerable<IGrouping<int?, Products>> オブジェクトを model に取得して View に渡すことができています。

public async Task<IActionResult> ListBySupplier2()
{
    // Linq to Objects で GroupBy を使うのは問題ない
    List<Products> productList = await _context.Products.ToListAsync();
    var model = productList.GroupBy(p => p.SupplierId);

    return View(model);
}

Visual Studio 2019 のデバッガで上のコードの model を展開すると以下の画像のようになっています。

デバッガで見た model

これが全くの予想外で驚いたので備忘録として残しておくことにした次第です。(自分が無知なだけということかもしれませんけど)

これが何の役に立つのかと言われると答えに窮しますが、例えば以下の View を使って SupplierID 別にテーブルを作ると言ったことができます。

@model IEnumerable<IGrouping<int?, Products>>

@{
    ViewData["Title"] = "ListBySupplier2";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h1>ListBySupplier2</h1>

@foreach (var item in Model)
{
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(m => item.First().ProductId)
                </th>
                <th>
                    @Html.DisplayNameFor(m => item.First().ProductName)
                </th>
                <th>
                    @Html.DisplayNameFor(m => item.First().SupplierId)
                </th>
                <th>
                    @Html.DisplayNameFor(m => item.First().UnitPrice)
                </th>
            </tr>
        </thead>
        <tbody>
            @foreach (var product in item)
            {
                <tr>
                    <td>
                        @Html.DisplayFor(m => product.ProductId)
                    </td>
                    <td>
                        @Html.DisplayFor(m => product.ProductName)
                    </td>
                    <td>
                        @Html.DisplayFor(m => product.SupplierId)
                    </td>
                    <td>
                        @Html.DisplayFor(m => product.UnitPrice)
                    </td>
                </tr>
            }
        </tbody>
    </table>
}

上のコードで foreach (var item in model) の item は IGrouping<int?, Products> に、foreach (var product in item) の product は Products になります。

結果、ブラウザには以下のように SupplierID 別のテーブルが表示されます。

結果

ORDER BY [SupplierID] で並べて一つのテーブルに表示した方がスマートかつ見やすいと思いますが、それはとりあえず置いときます。(笑)

Tags: , ,

ADO.NET

try - catch を書かないでロールバック

by WebSurfer 2020年7月29日 13:45

ADO.NET + SqlClient を使ってトランザクション処理を行う場合、明示的に Rollback メソッドを書かなくてもロールバックできるという話を書きます。

ロールバックを行うコードで自分がよく目にするのは、try - catch 文を使って try 句の中に処理をまとめ、catch 句に Rollback メソッドを書いて、処理の途中で例外が発生したらロールバックできるようにするというものです。

具体例は Microsoft のドキュメント「SqlTransaction.Rollback メソッド」にありますので見てください。

そのドキュメントにある catch 句で Rollback するというコードは、一見して例外発生でロールバックされるということが分かって良いと思うのですが、Exception を catch してそのままにしてしまっている所が気になります。

そこは、catch するのを SqlException のみにするとか Rollback した後で再 throw することで対応できるのですが、意地でも try - catch 文は書きたくないという場合もあるかもしれません。ないかもしれませんが。(笑)

Microsoft の Blog に書いてあったことですが(今はリンク切れで読めません)、未コミットのトランザクションは SqlConnection を Dispose(Close と同じ)する際ロールバックされるそうです。

ということは、catch 句に Rollback メソッドを書くというようなことはしなくても、try - finally または using 句を使って確実に SqlConnection が Dispose / Close されるようにしておけば例外発生でロールバックされるはずです。

実際「Microsoft Visual Studio 2005 による Web アプリケーション構築技法」という本にも Rollback メソッドを使わないコード例は紹介されていました。

具体例を以下の画像の SQL Server のテーブルと ADO.NET + SqlClient のコードを使って説明します。

SQL Server のテーブル

テーブルは「エラー発生時のトランザクションのロールバック - SET XACT_ABORT ON と TRY...CATCH」という記事から借用しました。フィールド StudentID には FK 制約が設定してあり 1 ~ 6 または NULL 以外を入力しようとするとエラーになります。

コード例は下の方にアップします。using 句を使って確実に SqlConnection が Dispose されるようにしています。Rollback メソッドは書いていません。例外が発生するとアプリは終了しますが、その前に using 句により SqlConnection が Dispose され、Commit メソッドは実行されませんのでロールバックされるはずです。

コード例では、上に紹介した記事にならって、3 つの UPDATE 文を実行しようとしています。最後の UPDATE 文で StudentID を 7 に設定し(1 ~ 6 または NULL 以外は FK 制約違反)そこで FK 制約違反となるようにして試してみました。エラーメッセージは以下の通りです。

ハンドルされていない例外: System.Data.SqlClient.SqlException: UPDATE ステートメントは FOREIGN KEY 制約 "FK__TestResul__Stude__22AA2996" と競合しています。競合が発生したのは、データベース "TestDatabase"、テーブル "dbo.Student", column 'StudentID' です。

static void Main(string[] args)
{
    string connString = @"接続文字列";
    string query = "UPDATE [TestDatabase].[dbo].[TestResult] " +
                   "SET [StudentID]=@StudentID " +
                   "WHERE [TestResultID]=@TestResultID";

    using (var connection = new SqlConnection(connString))
    {
        using (var command = new SqlCommand(query, connection))
        {
            command.Parameters.Add(
                new SqlParameter("@StudentID", SqlDbType.Int));
            command.Parameters.Add(
                new SqlParameter("@TestResultID", SqlDbType.Int));
            connection.Open();
            var sqltx = connection.BeginTransaction();
            command.Transaction = sqltx;

            command.Parameters["@StudentID"].Value = 5;
            command.Parameters["@TestResultID"].Value = 1;
            command.ExecuteNonQuery();

            command.Parameters["@StudentID"].Value = 6;
            command.Parameters["@TestResultID"].Value = 2;
            command.ExecuteNonQuery();

            // FK 制約違反
            command.Parameters["@StudentID"].Value = 7;
            command.Parameters["@TestResultID"].Value = 3;
            command.ExecuteNonQuery();

            sqltx.Commit();
        }
    }
}

結果、期待通りロールバックされて TestResult テーブルには 3 つの UPDATE 文のいずれも反映されないことを確認しました。

上に紹介した記事の Transact-SQL を使った場合の例では、SET XACT_ABORT ON を設定しない場合、TRY...CATCH を使って CATCH で明示的に ROLLBACK する必要があるそうです。しかし、ADO.NET + SqlClient では上のコード例のように SqlConnection を Dispose / Close すればロールバックされるようです。

Tags: , , ,

ADO.NET

About this blog

2010年5月にこのブログを立ち上げました。主に ASP.NET Web アプリ関係の記事です。

Calendar

<<  2024年3月  >>
252627282912
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar