WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

Linq to Entities / Objects

by WebSurfer 13. February 2019 15:17

Linq to Entities は、Linq to Objects とは違って、そのクエリ式が SQL Server などの DB で使われる SQL に変換できる必要があるという話を書きます。CodeZine の記事「LINQにも色々 ~SQLに変換されるモノと変換されないモノ」を見てください。図だけ借用して以下にも貼っておきます。

Linq to Objects / Enitities

その図を見れば一目瞭然だと思いますし、詳しいことは CodeZine の記事を読めば分かるのですが、それで終わってしまってはブログの記事としては面白くないので、どういう事例があったか(要するに失敗談)を書いておきます。(笑)

まず以下の例。これを実行すると ToList() のところで "System.NotSupportedException: LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression." というエラーが出ます。

public class Filter
{
    public int Id { get; set; }
    public DateTime? Date { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        // ADO.NET Entity Data Model
        TestDatabaseEntities ctx = new TestDatabaseEntities();
            
        var list = (from c in ctx.TestTable
                    select new Filter
                    {
                        Id = c.ID,
                        Date = DateTime.Parse(c.Date)
                    }).ToList();
    }
}

上の図の右側に「SQ に変換してデータベース上で実行」とありますが、DateTime.Parse メソッドが SQL に変換できないということでエラーになったということです。

上のコードで Filter クラスを初期化して Id, Date に代入するところからは C# に戻ってきて行うのかと期待してましたが、そうではなくて ToList の前まで全部 SQL に変換して SQL Server で実行しようとするようです。

ちなみに、Filter クラスの Date プロパティを string 型に変更して、DateTime.Parse なしで直接 c.Data を代入するようにすれば SQL に変換出来るようで、エラーなく期待した結果が得られます。

もう一つは以下の例。これはコードのコメントにも書いてある通り delivery も test も Linq to Entities のクエリ式で、両方を合体して SQL に変換でき、foreach で DB に SQL を投げることができるので問題なく期待した結果が得られます。(Northwind の Order Details, Products テーブルから ADO.NET Entity Data Model を作成して使っています)

// これは Linq to Entities
var delivery = 
    from d in context.Order_Details
    group d by d.ProductID into g
    orderby g.Key
    select new
    {
       ItemCode = g.Key,
        Count = g.Sum(x => x.Quantity),
        SumAmount = g.Sum(x => x.UnitPrice * x.Quantity)
    };

// これも Linq to Entities
var test = from p in context.Products
           join d in delivery
           on p.ProductID equals d.ItemCode into dGroup
           from item in dGroup.DefaultIfEmpty()
           select new
           {
               ItemCode = p.ProductID,
               Name = p.ProductName,
               Count = item.Count,
               SumAmount = item.SumAmount
           };

// delivery を含めた test のコード全体を Linq to Entities と
// して SQL に変換することができ、foreach で DB に SQL を投げ
// ることができるので問題ない。
foreach (var x in test)
{
    Console.WriteLine(
      $"Name: {x.Name}, Count: {x.Count}, Sum: {x.SumAmount}");
}

上のような複雑なクエリ式が SQL に変換できるというのが驚きですが、それはちょっと置いといて、失敗事例はどういうことだったのかを書きます。

それは、DataTable から Linq to Objects のクエリ式を使って匿名型のオブジェクトのコレクションを取得し、それを Linq to Entities のクエリ式に組み合わせたことです。

具体的には、上のコードの delivery を以下のように DataTable(コードの table がそれ)から取得するようにしました。

// これは Linq to Object
var delivery = 
    from d in table.AsEnumerable()
    group d by d.Field<int>("ProductID") into g
    orderby g.Key
    select new
    {
        ItemCode = g.Key,
        Count = g.Sum(x => x.Field<Int16>("Quantity")),
        SumAmount = g.Sum(x => x.Field<decimal>("UnitPrice") * 
                               x.Field<Int16>("Quantity"))
    };

そうすると foreach のところで " System.NotSupportedException: Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context." というエラーになります。

エラーメッセージが前者の例とは違っていてため、最初、原因が分からなかったのですが、匿名型のオブジェクトのコレクションを Linq to Entities のクエリに組み込むと SQL に変換できないということが問題のようです。

なお、匿名型でなく、別にクラスとプロパティを定義し、それを初期化して各プロパティに代入するようにしても、上のエラーメッセージの 'Anonymous type' が定義したクラス名に変わるだけで同じエラーになります。

解決策は test のクエリ式も Linq to Objects にすることで、具体的には context.Products を context.Products.ToList() にすれば期待した結果が得られます。

Tags:

ADO.NET

Linq to Entities で型の初期化

by WebSurfer 30. April 2018 16:31

以下のコードを実行すると foreach (var item in list2) のところで System.NotSupportedException がスローされ、

1 つの LINQ to Entities クエリに含まれる構造的に互換性のない 2 つの初期化に、型 'ConsoleAppJoinByLinq2.JoinedList' が指定されています。1 つの型を同じクエリ内の 2 つの場所で初期化することはできますが、両方の場所で同じプロパティが同じ順序で設定されている必要があります。

・・・というエラーメッセージが表示されます。その理由と解決策を書きます。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleAppJoinByLinq2
{
  public class JoinedList
  {
    public int ProductID { set; get; }
    public string ProductName { get; set; }
    public int CategoryID { get; set; }
    public string CategoryName { set; get; }
  }

  class Program
  {
    static void Main(string[] args)
    {
      NORTHWINDEntities db = new NORTHWINDEntities();

      var list1 = from c in db.Categories
                  where c.CategoryID == 1
                  select new JoinedList
                  {
                    CategoryID = c.CategoryID,
                    CategoryName = c.CategoryName
                  };

      var list2 = from c in list1
                  join p in db.Products
                  on c.CategoryID equals p.CategoryID
                  select new JoinedList
                  {
                    ProductID = p.ProductID,
                    ProductName = p.ProductName,
                    CategoryName = c.CategoryName
                  };

      foreach (var item in list2)
      {
        Console.WriteLine("{0}, {1}, {2}",
          item.ProductID, item.ProductName, item.CategoryName);
      }
    }
  }
}

上のコードでは、SQL Server のサンプルデータベース Northwind をベースに Visual Studio Communirt 2015 のウィザードを使って作った Entity Data Model (EDM) を使っています。以下の画像を見てください。

Entity Data Model

NORTHWINDEntities は EDM を作ると一緒に自動生成される DbContext クラスを継承したコンテキストクラスです。

Categories と Products は NORTHWINDEntities コンテキストクラスのプロパティで、データベースの当該テーブルを表すエンティティのコレクションを取得・設定するものです。

そして、肝心の話の何故エラーになるかの理由ですが、エラーメッセージの「両方の場所で同じプロパティが同じ順序で設定」という条件が満たされてない、即ち、list1 と list2 のクエリで JoinedList を初期化する際のプロパティの設定が異なるからです。

その前のエラーメッセージの条件「1 つの型を同じクエリ内の 2 つの場所で初期化」には該当しないように見えますが、list1 と list2 のクエリは両方 foreach のところで遅延評価されて、結局「同じクエリ内」ということになるようです。

解決策は、

  1. list1 のクエリに ToList() を適用する(遅延評価されないように)、または、
  2. JoinedList を初期化する際のプロパティの設定を、並び順序を含めて list1 / list2 のクエリで同じになるようにする

・・・です。そうすれば、以下の通り期待した結果が得られます。

実行結果

Tags:

ADO.NET

異なるデータソースの結合と表示

by WebSurfer 26. November 2017 13:34

異なるデータソース(例えば SQL Server と CSV ファイル)のレコードを内部結合または左外部結合して GridView などに一覧表示する例を書きます。

GridView に表示

上の画像はこの記事で紹介するサンプルの実行結果で、SQL Server と CSV ファイルをデータソースに使い、左側が内部結合、右側が左外部結合した結果一覧を ASP.NET Web Forms アプリの GridView に表示したものです。

この記事で使用したデータソースは、Microsoft が提供しているサンプルデータベース Northwind の Orders テーブルと、Customers テーブルから一部のフィールド / レコードを抜き出して作った以下の画像の CSV ファイルです。

CSV ファイル内容

データソースが両方とも SQL Server のサンプルデータベース Northwind にあれば、SELECT クエリで JOIN 句を使って結合し、その結果を DataTable などに取得するのが簡単ですが、一方が CSV ファイルではそうはいきません。

ではどうするかと言うと、SQL Server のテーブルと CSV ファイルそれぞれから List<T> 型のオブジェクトを作り、それを Linq で結合した結果を GridView のデータソースとしてバインドしてやるのがよさそうです。

Linq を使って結合する例は Microsoft の文書「join 句 (C# リファレンス)」やそれからリンクが張ってある記事が参考になりました。

上の画像を表示したサンプルコードは以下の通りです。説明はコメントとして書きましたので、それを見てください。(手抜きでスミマセン)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

// SQL Server の Northwind サンプルデータベース Orders
// テーブルのレコードを格納するクラス定義
public class Order
{
    public int OrderID { get; set; }
    public string CustomerID { get; set; }
    public int? EmployeeID { get; set; }
    public DateTime? OrderDate { get; set; }
    public DateTime? RequiredDate { get; set; }
    public DateTime? ShippedDate { get; set; }
    public int? ShipVia { get; set; }
    public decimal? Freight { get; set; }
    public string ShipName { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipRegion { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
}

// CSV ファイルのレコードを格納するためのクラス定義
public class Customer
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
}

// 結合後の結果を格納するためのクラス定義
public class Result
{
    public int OrderID { get; set; }
    public string CompanyName { get; set; }
    public DateTime? OrderDate { get; set; }
    public decimal? Freight { get; set; }
}

public partial class _0019_GridViewJoinedList : 
    System.Web.UI.Page
{
  // SQL Server のサンプルデータベース Northwind の
  // Orders テーブルからデータを取得して List<Order>
  // オブジェクトを生成。Entity Framework を使う方が簡単
  // だが、ここではプリミティブに ADO.NET の SqlDataReader 
  // を使用した。
  protected List<Order> CreateOrderList()
  {
    List<Order> orders = new List<Order>();

    string connString = WebConfigurationManager.
            ConnectionStrings["NORTHWINDConnectionString"].
            ConnectionString;

    string query = "SELECT [OrderID], [CustomerID]," +
            "[EmployeeID], [OrderDate], [RequiredDate]," +
            "[ShippedDate], [ShipVia], [Freight]," +
            "[ShipName], [ShipAddress], [ShipCity]," +
            "[ShipRegion], [ShipPostalCode], [shipCountry]" +
            "FROM [Orders]";

    using (SqlConnection conn = new SqlConnection(connString))
    {
      conn.Open();
      using (SqlCommand cmd = new SqlCommand(query, conn))
      {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
          if (reader != null)
          {
            while (reader.Read())
            {
              Order record = new Order();

              record.OrderID = reader.GetInt32(0);
              record.CustomerID = reader.IsDBNull(1) ?
                      null : reader.GetString(1);
              record.EmployeeID = reader.IsDBNull(2) ?
                      null : (int?)reader.GetInt32(2);
              record.OrderDate = reader.IsDBNull(3) ?
                      null : (DateTime?)reader.GetDateTime(3);
              record.RequiredDate = reader.IsDBNull(4) ?
                      null : (DateTime?)reader.GetDateTime(4);
              record.ShippedDate = reader.IsDBNull(5) ?
                      null : (DateTime?)reader.GetDateTime(5);
              record.ShipVia = reader.IsDBNull(6) ?
                      null : (int?)reader.GetInt32(6);
              record.Freight = reader.IsDBNull(7) ?
                      null : (decimal?)reader.GetDecimal(7);
              record.ShipName = reader.IsDBNull(8) ?
                      null : reader.GetString(8);
              record.ShipAddress = reader.IsDBNull(9) ?
                      null : reader.GetString(9);
              record.ShipCity = reader.IsDBNull(10) ?
                      null : reader.GetString(10);
              record.ShipRegion = reader.IsDBNull(11) ?
                      null : reader.GetString(11);
              record.ShipPostalCode = reader.IsDBNull(12) ?
                      null : reader.GetString(12);
              record.ShipCountry = reader.IsDBNull(13) ?
                      null : reader.GetString(13);

              orders.Add(record);
            }
          }
        }
      }
    }
    return orders;
  }

  // CSV ファイルからデータを取得して List<Customer> オブ
  // ジェクトを生成。
  protected List<Customer> CreateCustomerList()
  {
    List<Customer> customers = new List<Customer>();

    string csvFile = Server.MapPath("~/App_Data/TextFile.csv");

    using (Microsoft.VisualBasic.FileIO.TextFieldParser tfp =
      new Microsoft.VisualBasic.FileIO.TextFieldParser(
        csvFile,
        System.Text.Encoding.GetEncoding("Shift_JIS")))
    {
      //フィールドがデリミタで区切られている
      tfp.TextFieldType =
        Microsoft.VisualBasic.FileIO.FieldType.Delimited;
      // デリミタを , とする
      tfp.Delimiters = new string[] { "," };
      // フィールドを " で囲み、改行文字、デリミタを
      // 含めることができるか
      tfp.HasFieldsEnclosedInQuotes = true;
      // フィールドの前後からスペースを削除
      tfp.TrimWhiteSpace = true;

      while (!tfp.EndOfData)
      {
        string[] fields = tfp.ReadFields();

        Customer customer = new Customer()
        {
          CustomerID = fields[0],
          CompanyName = fields[1],
          ContactName = fields[2],
          ContactTitle = fields[3]
        };
        customers.Add(customer);
      }
    }
    return customers;
  }

  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      List<Order> orders = CreateOrderList();
      List<Customer> customers = CreateCustomerList();

      // 内部結合
      var innerJoin = from o in orders
                      join c in customers
                      on o.CustomerID equals c.CustomerID
                      select new Result
                      {
                        OrderID = o.OrderID,
                        CompanyName = c.CompanyName,
                        OrderDate = o.OrderDate,
                        Freight = o.Freight
                      };

      // シーケンスが空の場合に返すデフォルト値
      // 下の DefaultIfEmpty メソッドの引数に設定する
      Customer defaultValue = new Customer() {
                CustomerID = string.Empty,
                CompanyName = string.Empty,
                ContactName = string.Empty,
                ContactTitle = string.Empty };

      // 左外部結合
      var leftOuterJoin = 
          from o in orders
          join c in customers
          on o.CustomerID equals c.CustomerID into cGroup
          from item in cGroup.DefaultIfEmpty(defaultValue)
          select new Result
          {
              OrderID = o.OrderID,
              CompanyName = item.CompanyName,
              OrderDate = o.OrderDate,
              Freight = o.Freight
          };

      // 上の画像の左側の GridView(内部結合)
      GridView1.DataSource = innerJoin;
      GridView1.DataBind();

      // 上の画像の右側の GridView(左外部結合)
      GridView2.DataSource = leftOuterJoin;
      GridView2.DataBind();
    }
  }
}

Tags: , ,

ASP.NET

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  July 2019  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar