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

固定長パラメータの LIKE 比較

by WebSurfer 2010年9月3日 22:35

Transact-SQL の LIKE 句を使用して文字列の比較を行うときは、パターン文字列と比較対象文字列の中の空白の取り扱いに注意が必要です。

詳しくは MSDN ライブラリの LIKE (Transact-SQL) を見てもらうとして、ここでは ADO.NET や Visual Studio のウィザードで作る TableAdapter を利用する場合の注意事項を、例をあげて書いておきます。

例として、SQL Server DB に TestTable という名前のテーブルがあり、以下のフィールドを持っているとします。

FieldA  nchar(5)
FieldB  nchar(20)

TeatTable は以下の内容になっています。(下の画像では TestTable2 になっていますが、気にしないでください。(笑))

TeatTable の内容

この Table を、以下の SELECT クエリを用いて、FieldA の 前 2 文字を指定して検索を行うとします。

SELECT FieldA, FieldB
FROM   TestTable
WHERE  (FieldA LIKE @FieldA + N'%')

まず、Visual Studio のウィザードを利用して、上記の SELECT クエリをベースに型付 DataSet + TableAdapter を作ります。デザイン画面で見ると、以下のようになります。

型付 DataSet + TableAdapter

TableAdapter 作成された GetData メソッドを使って、その引数を "01" としてデータを抽出してみます。

結果は "01" しか抽出されません。FieldA = "01" で、"01", "01111", "012", "01234", "01457" がマッチするように思えますが、"01" しか抽出されないのは何故でしょう?

理由は以下のとおりです。

自動生成される TableAdapter のコードは、nchar(5) の場合、パラメータの追加部分は以下のようになります。

this._commandCollection[0].Parameters.Add(
  new global::System.Data.SqlClient.SqlParameter(
    "@FieldA", global::System.Data.SqlDbType.NChar, 5, 
    global::System.Data.ParameterDirection.Input, 0, 0, 
    "FieldA", global::System.Data.DataRowVersion.Current, 
    false, null, "", "", ""
  )
);

この ... SqlDbType.NChar, 5, ... というところが問題のようです。SqlParameter.Size に 5 が設定されるので、FieldA = "01" の場合、クエリは LIKE '01   %'(01 と % の間に 3 文字空白)となって、"01"(実際は 01 の後に空白 3 文字あり) 以外はマッチしないということのようです。

MSDN ライブラリの SqlParameter.Size プロパティ の解説には "固定長データ型では、Size の値は無視されます。" と書いてあったんですが・・・

解決策は以下のとおりです。

  1. FieldA の型を変更できるのであれば、nchar(5) を nvarchar(5) に変更して、型付 DataSet + TableAdapter を作り直す。
  2. FieldA の型を変更できなければ、ウィザードベースで GetData メソッドを作るのは諦めて、自力でコードを書いて TableAdapter を拡張する。

上記 2 ですが、パラメータの追加の部分を以下のようにすると期待通りの結果になります。自力で書くのは面倒だからといって、自動生成されたコードを書き直すと、思わぬところで不具合を生じる可能性がありますので止めた方がいいです。

command1.Parameters.Add(
  new SqlParameter("@FieldA", SqlDbType.NChar));
command1.Parameters["@FieldA"].Value = "01";

または、

command1.Parameters.AddWithValue("@FieldA", "01");

なお、先の記事 SqlParameter の Size 指定 で、長さ(SqlParameter.Size)の指定は 0 が良いと書きましたが、それは今回のケースでも当てはまっていました。以下のようにしても OK です。

command1.Parameters.Add(
  new SqlParameter("@FieldA", SqlDbType.NChar, 0, "FieldA"));
command1.Parameters["@FieldA"].Value = "01";

------------ 2010/9/4 追記 ------------

パターン文字列の生成に使う型(パラメータ設定のデータ型)と比較対象文字列の型(テーブル定義のデータ型)を合わせる必要はないということに気がつきました。

パラメータ設定で SqlDbType.NChar を SqlDbType.NVarChar に変更しても OK です。すなわち以下のようにすれば、nvarchar(5) で文字列を組み立てるので、パターン文字列は '01%' となるはずです。

command1.Parameters.Add(
  new SqlParameter("@FieldA", SqlDbType.NVarChar, 5, "FieldA"));
command1.Parameters["@FieldA"].Value = "01";

要するに、パラメータ設定で、SqlDbType.NChar とすると MSDN ライブラリの LIKE (Transact-SQL) の解説のセクションにある 2 つの SQL の前者、SqlDbType.NVarChar とすると後者のようになるということのようです。

Tags: , ,

ADO.NET

SQL Server の IN 演算子の利用

by WebSurfer 2010年8月5日 12:31

DB のテーブルから一つの項目を選び、その項目の詳細を表示するというケースはよくあると思います。そのようなサンプルは Web から容易に見つかると思います。

では、一度に複数の項目を選んで、複数の項目の詳細を同時に表示するにはどうすればいいでしょうか?

SQL Server には IN 演算子というものがあって、それを利用するのがよさそうです。以下は SQL Server 2005 Online Book のサンプルです。

SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c 
    ON e.ContactID = c.ContactID
WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');

ASP.NET のアプリでは、CheckBoxList で複数の項目を選んで、Button クリックで GridView に選んだ項目の詳細を表示することを考えます。

ユーザーの選択結果によって動的にクエリ(上記で言うと、IN (...) の ... の部分)を変える必要があります。

他にもっとスマートなやり方があるかもしれませんが、プログラムで SELECT クエリを組み立てて処置するサンプルを書いておきます。

DB は Microsoft が無償提供している Northwind サンプルデータベースの Orders テーブルを使用しました。

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

  protected void Button1_Click(object sender, EventArgs e)
  {
    string selectedItems = "(";
    int count = 0;

    for (int i = 0; i < CheckBoxList1.Items.Count; i++)
    {
      if (CheckBoxList1.Items[i].Selected)
      {
        selectedItems += CheckBoxList1.Items[i].Value + ", ";
        count++;
      }
    }

    if (count > 0)
    {
      selectedItems = 
        selectedItems.TrimEnd(new char[] { ',', ' ' });
      selectedItems += ")";
      SqlDataSource2.SelectParameters.Clear();
      SqlDataSource2.SelectCommand = 
        "SELECT * FROM [orders] WHERE [EmployeeID] IN "
        + selectedItems + " ORDER BY [EmployeeID]";
    }
    else
    {
      SqlDataSource2.SelectParameters.Clear();
    }       
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>無題のページ</title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
    <asp:SqlDataSource ID="SqlDataSource1" 
      runat="server"
      ConnectionString="<%$ ConnectionStrings:Northwind %>"
      SelectCommand="SELECT DISTINCT [EmployeeID] FROM [Orders] ORDER BY [EmployeeID]">
    </asp:SqlDataSource>
    <asp:CheckBoxList ID="CheckBoxList1" 
      runat="server"
      DataSourceID="SqlDataSource1" 
      DataTextField="EmployeeID"
      DataValueField="EmployeeID">
    </asp:CheckBoxList>
   
    <asp:Button ID="Button1" 
      runat="server" 
      Text="Show Selected Data"
      onclick="Button1_Click" />
       
    <asp:SqlDataSource ID="SqlDataSource2" 
      runat="server"
      ConnectionString="<%$ ConnectionStrings:Northwind %>" >
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" 
      runat="server" 
      DataSourceID="SqlDataSource2">
    </asp:GridView>   
  </div>
  </form>
</body>
</html>

Tags: ,

ASP.NET

About this blog

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

Calendar

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

View posts in large calendar