WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

SelectMany メソッド (その 2)

by WebSurfer 27. July 2023 10:11

先の記事「SelectMany メソッド」の続きです。

Microsoft のドキュメント「Enumerable.SelectMany メソッド」によるとこのメソッドには 4 つのオーバーロードがあります。それらの使い方を調べましたので、備忘録として書いておきます。

SQL Server サンプルデータベース Northwind の Orders, Order_Details テーブルから、リバースエンジニアリングで生成したコンテキスクラストとエンティティクラスをベースに使います。下の画像は Visual Studio 2022 の拡張機能 EF Core Power Tools を使って DbContext Diagram を表示したものです。

DbContext Diagram

Order には複数の顧客の過去の注文データ全てが含まれており、各注文に紐づく詳細は OrderDetails ナビゲーションプロパティをたどって OrderDetail にアクセスして取得できます。

Order から CustomerID が "ALFKI" の顧客の注文(Order の中に複数あります)を抽出し、それに紐づく OrderDetail を SelectMany メソッドで取得してみます。

以下に 4 つのオーバーロードを使った例を書きます。いずれも IEnumerable<T> を拡張する拡張メソッドであることに注意してください。

(1) その 1

// SelectMany<TSource,TResult>(
//     IEnumerable<TSource>,
//     Func<TSource,IEnumerable<TResult>>)
// シーケンスの各要素を IEnumerable<T> に射影し、結果のシー
// ケンスを 1 つのシーケンスに平坦化します。
var selectMany1 = await _context.Orders
    .Where(o => o.CustomerId == "ALFKI")
    .SelectMany(o => o.OrderDetails)
    .ToListAsync();

先の記事「SelectMany メソッド」に書いたのがこのメソッドです。

コードの上に書いたコメントは Microsoft のドキュメントの説明です。コメントの下のコードで具体的にどういうことをしているかと言うと:

Where メソッドの結果は IQueryable<Order> オブジェクト (IEnumerable<T> を継承) になります。これが上のコメント「シーケンスの各要素を・・・」の最初に出てくるシーケンスに該当します。各要素は Order オブジェクトです。

SelectMany メソッドは、その第 2 引数に指定された selector 関数 o => o.OrderDetails に従って、Order オブジェクトを必要なプロパティだけで構成された別の形式に変換し (これを「投射」という。この例では OrderDetails ナビゲーションプロパティで ICollection<OrderDetail> を取得)、それを 1 つのシーケンスに平坦化して返します (この例では IQueryable<OrderDetail> を返します)。

・・・ということで、上のコードの実行結果は以下の通りとなります。

その 1 の結果

(2) その 2

// SelectMany<TSource,TCollection,TResult>(
//     IEnumerable<TSource>,
//     Func<TSource,IEnumerable<TCollection>>,
//     Func<TSource,TCollection,TResult>)
// シーケンスの各要素を IEnumerable<T> に射影し、結果のシー
// ケンスを 1 つのシーケンスに平坦化して、その各要素に対し
// て結果のセレクター関数を呼び出します。
var selectMany2= await _context.Orders
    .Where(o => o.CustomerId == "ALFKI")
    .SelectMany(o => o.OrderDetails,
      (o, od) => new { o.OrderId, od.ProductId, od.UnitPrice })
    .ToListAsync();

上の「その 1」との違いは、SelectMany メソッドの引数に collectionSelector, resultSelector という 2 つの関数を取ることです。collectionSelector 関数を使って平坦化された中間シーケンスを生成し、次に resultSelector 関数を使って中間シーケンスの中の Order オブジェクトと OrderDetail オブジェクトの両方にアクセスして値を取得し、さらに別のシーケンス (上の例では IQueryable<匿名型>) を生成して戻り値として返しています。

上のコードの実行結果は以下の通りとなります。

その 2 の結果

上のコード例では Order オブジェクトの OrderId を取得しているところに注目してください。「その 1」のオーバーロードではそれはできません。

(3) その 3

// SelectMany<TSource,TResult>(
//     IEnumerable<TSource>,
//     Func<TSource,Int32,IEnumerable<TResult>>)
// 上の「その 1」と同様。加えてオーダー毎の index を付与。
// Linq to Entities では使えないので注意。
// index はオーダー毎に振られることに注意。
// 一つのオーダーは複数の OrderDetails を持つので下の例
// では index が同じになるものがある
var selectMany3 = _context.Orders.ToList()
    .Where(o => o.CustomerId == "ALFKI")
    .SelectMany((o, index) => o.OrderDetails
      .Select(od => new { index, od.ProductId, od.UnitPrice }))
    .ToList();

上の「その 1」とほぼ同様な操作を行いますが、加えてオーダー毎の index を付与できるところが異なります。

このオーバーロードは SQL Server などの DB で使われる SQL に変換できないので、Linq to Entities では使えないことに注意してください。

上のコードの実行結果は以下の通りとなります。

その 3 の結果

SelectMany で index を振ってどういう使い道があるかは自分的には謎です。先の記事「Entity Framework で ROW_NUMBER」で書いたようなケースでは意味があると思いますが。

(4) その 4

// SelectMany<TSource,TCollection,TResult>(
//     Enumerable<TSource>,
//     Func<TSource,Int32,IEnumerable<TCollection>>,
//     Func<TSource,TCollection,TResult>)
// 上の「その 2」と同様。加えてオーダー毎の index を付与。
// Linq to Entities では使えないので注意。
// index はオーダー毎に振られることに注意。
// 一つのオーダーは複数の OrderDetails を持つので下の例
// では index が同じになるものがある
var selectMany4 = _context.Orders.ToList()
    .Where(o => o.CustomerId == "ALFKI")
    .SelectMany((o, index) => o.OrderDetails
      .Select(od => new { index, od.ProductId, od.UnitPrice }),
        (o, a) => new { o.OrderId, a.index, a.ProductId, a.UnitPrice })
    .ToList();

上の「その 2」とほぼ同様な操作を行いますが、それに加えてオーダー毎の index を付与できるところが異なります。

このオーバーロードは SQL Server などの DB で使われる SQL に変換できないので、Linq to Entities では使えないことに注意してください。

上のコードの実行結果は以下の通りとなります。

その 4 の結果

Tags: , ,

ADO.NET

DbProviderFactory の利用

by WebSurfer 4. July 2023 17:33

ADO.NET の DbProviderFactory クラスDbConnection クラス, DbCommand クラスなどの抽象クラスを使ってデータベースにアクセスして操作するアプリの��を備忘録として書いておきます。

Windows Forms アプリ

上の画像は、SQLite のテーブルのレコード一覧を DataGridView に表示し、ユーザーがそれを見て編集した後、編集結果をデータベースに書き戻す .NET Framework 4.8 の Windows Forms アプリです。

対象としたデータベースは以下の内容の SQLite の Movie テーブルです。

SQLite の Movie テーブル

普通に作ると、SQLite 専用の SQLiteConnection, SQLiteCommand などを使うと思いますが、それらに代えて DbConnection, DbCommand などの抽象クラスと、DbProviderFactory の CreateConnection、CreateCommand メソッドなど使うようにします。

そうすると何のメリットがあるのかと言うと、例えば SQLite を SQL Server に変更する場合、ハードコーディングした SQLiteConnection, SQLiteCommand などを SqlConnection, SqlCommand などに書き換える必要はなく、app.config の接続文字列だけを SQL Server 用に書き換えれば移行できます。

まず DbProviderFactory の登録を行う必要がありますが、SQLite の場合は NuGet から System.Data.SQLite をインストールすると app.config に以下の DbProviderFactories 要素が追加されるのでこれを利用します。

<DbProviderFactories>
  <remove invariant="System.Data.SQLite.EF6" />
  <add name="SQLite Data Provider (Entity Framework 6)" 
    invariant="System.Data.SQLite.EF6" 
    description=".NET Framework Data Provider for SQLite (Entity Framework 6)" 
    type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
  <remove invariant="System.Data.SQLite" />
  <add name="SQLite Data Provider" 
    invariant="System.Data.SQLite" 
    description=".NET Framework Data Provider for SQLite" 
    type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>

接続文字列とプロバイダ名も app.config から取得できるように、以下のように connectionStrings 要素を設定しておきます。

<connectionStrings>
  <add name="ConnectionInfo"
    connectionString="SQLite 用の接続文字列"
    providerName="System.Data.SQLite"/>
</connectionStrings>

Visual Studio のデザイン画面で、ツールボックスから Form に Button 2 つ、DataGridView、BindingSource をドラッグ&ドロップした後、以下のコードを記述します。コメントアウトしたコードが SQLiteConnection, SQLiteCommand, SQLiteDataAdapter, SQLiteCommandBuilder などを使ったもので、その下が DbProviderFactory を使用したものです。下のコードを実行した結果がこの記事の一番上の画像です。

using System;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
using System.Configuration;
using System.Data.Common;
using System.Configuration.Provider;

namespace WindowsFormsSQLite
{
    public partial class Form1 : Form
    {
        //private SQLiteDataAdapter adapter;
        // ↓↓↓
        private DbDataAdapter adapter;

        private DataTable table;

        public Form1()
        {
            InitializeComponent();

            this.dataGridView1.DataSource = this.bindingSource1;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            var connString = ConfigurationManager
                             .ConnectionStrings["ConnectionInfo"]
                             .ConnectionString;            
            var selectQuery = 
                "SELECT Id, Title, ReleaseDate, Genre, Price FROM Movie";

            //var connection = new SQLiteConnection(connString);
            //var command = new SQLiteCommand(selectQuery, connection);
            //this.adapter = new SQLiteDataAdapter();
            //this.adapter.SelectCommand = command;
            //_ = new SQLiteCommandBuilder(this.adapter);
            // ↓↓↓
            var providerName = ConfigurationManager
                               .ConnectionStrings["ConnectionInfo"]
                               .ProviderName;
            var factory = DbProviderFactories.GetFactory(providerName);
            var connection = factory.CreateConnection();
            connection.ConnectionString = connString;
            var command = connection.CreateCommand();
            command.CommandText = selectQuery;
            command.Connection = connection;
            this.adapter = factory.CreateDataAdapter();
            this.adapter.SelectCommand = command;
            var builder = factory.CreateCommandBuilder();
            builder.DataAdapter = this.adapter;

            this.table = new DataTable();
            this.adapter.Fill(this.table);
            this.bindingSource1.DataSource = this.table;

            this.components.Add(connection);
            this.components.Add(command);
        }
               
        private void Update_Click(object sender, EventArgs e)
        {            
            this.adapter.Update(this.table);
        }

        private void Remove_Click(object sender, EventArgs e)
        {
            this.bindingSource1.RemoveCurrent();
        }
    }
}

次に、SQLite のテーブルを下の画像の SQL Server のテーブルに変更する場合、どのようにするかを書きます。

SQL Server の Movie テーブル

System.Data.SqlClient を使う場合、そのプロパイダ情報は machine.config に登録済みのはずです。もし、登録されてなければ app.config の DbProviderFactories 要素に以下のように SQL Server 用のプロパイダ情報を追加してください。

<remove invariant="System.Data.SqlClient" />
<add name="SqlClient Data Provider"
  invariant="System.Data.SqlClient"
  description=".Net Framework Data Provider for SqlServer"
  type="System.Data.SqlClient.SqlClientFactory, System.Data,
    Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

あとは、以下のように接続文字列とプロバイダ名の設定を SQL Server 用に差し替えるだけで済みます。connectionString のみでなく、providerName も SQL Server 用に変更しているところに注意してください。

<connectionStrings>
  <add name="ConnectionInfo"
    connectionString="SQL Server 用の接続文字列"
    providerName="System.Data.SqlClient"/>
</connectionStrings>

その上でアプリを実行すれば、SQL Server に接続されて以下の画像の通り Movie テーブルのレコード一覧が表示され、編集操作も同様に可能になります。

Windows Forms アプリ

SQLite と SQL Server のテーブルにはデータの型の違いがありますが、DbDataAdapter と DataTable を使う非接続型のアプリの場合は、その違いはプロバイダと DataTable が吸収してくれるようです。

ただ、上のようなことを考えなくても、SQL Server の場合は Visual Studio のデータソース構成ウィザードが利用できますので、それを使って作り直した方が簡単かつ確実かもしれません。ドラッグ&ドロップ操作だけで自力では一行もコードを書かずにアプリを作成できますので。


【2023/7/5 追記】

プロバイダを、System.Data.SqlClient に代えて Microsoft.Data.SqlClient とする場合について以下に追記します。

Microsoft.Data.SqlClient 用のプロバイダは machine.config には登録されてないので、Microsoft のドキュメント「SqlClientFactory の取得」に書いてあるように、app.config に DbProviderFactory の登録を行う必要があります。以下の通りです。

<add name="Microsoft SqlClient Data Provider" 
  invariant="Microsoft.Data.SqlClient" 
  description="Microsoft SqlClient Data Provider for SQL Server" 
  type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient, 
        Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5" />

上の Version は、使用する Microsoft.Data.Sqlclient のバージョンと合わせる必要があるので注意してください。Microsoft.Data.Sqlclient は NuGet からインストールしますが、インストール後 Visual Studio のソリューションエクスプローラーの「参照」に Microsoft.Data.Sqlclient が追加されるので、そのバージョンに合わせてください。

あとは、接続文字列の設定の内 providerName を Microsoft.Data.Sqlclient 用に変更すれば OK です。

<add name="ConnectionInfo" 
  connectionString="SQL Server 用の接続文字列" 
  providerName="Microsoft.Data.SqlClient" />

Tags: , , , ,

ADO.NET

SQL Server と .NET の decimal 型

by WebSurfer 3. July 2023 17:47

SQL Server の decimal 型のフィールドから、.NET アプリで ADO.NET を使って .NET の Decimal 型として値を取得する際、SQL Server と .NET では扱える値の大きさが異なる (SQL Server の方が大きい) ことにより、OverflowException が発生することがあるという話を書きます。

OverflowException 発生

.NET の Decimal 型は、Microsoft のドキュメント「Decimal 構造体」によると、128 ビットで構成され、その内 96 ビットが整数部で、残り 32 ビットが正負の符号と小数点の位置を指定するのに使われているそうです。

従い、.NET の Decimal 型の最大値 (MaxValue) は、

2^96 - 1 = 79,228,162,514,264,337,593,543,950,335

ということになります。

一方、SQL Server の decimal 型は、Microsoft のドキュメント「decimal 型と numeric 型 (Transact-SQL)」によると、38 桁まで表すことができるそうです。例えば、decimal(38,0) は小数部の無い 38 桁の整数を表すことができるということになります。

上の画像では、.NET の Decimal.MaxValue より 1 大きい SQL Server の decimal(38,0) 型の値を、.NET のコンソールアプリで ADO.NET + System.Data.SqlClient を使って取得しようとして OverflowException が発生しています。

上のようなケースではオバーフローするのが当たり前と思えるのですが、自分がハマったのは SQL Server の decimal 型のフィールドで小数点が指定してある場合でした。そのことを以下に書きます。

なお、以下の話は System.Data.SqlClient を使った場合ですので注意してください。(Microsoft.Data.SqlClient の場合は後述します)

例えば、decimal(38,26) と言うように小数部の桁数も指定して、上の画像のコード例の内 query2 を以下のように変更したとします。

// 上の画像の query2 で、
// 79228162514264337593543950336 ⇒ 10000000
// decimal(38,0) ⇒ decimal(38,26)
// に変更
var query2 = "select cast(10000000 as decimal(38,26)) val";

この場合も OverflowException がスローされます。上の 10000000 という値は、上の回答の画像の例 MaxValue + 1 よりはるかに小さいのになぜオーバーフローするのでしょうか?

そこが分からなくてハマったのですが、どうやら以下のようなことらしいです。

上のコードの例では、10000000 の後に 26 個の 0 を続けた整数値を表現できなければならず、.NET の Decimal の整数部 96 ビットでは表現できない (MaxValue を超える) のでオバーフローするということのようです。

試しに上のコードの 10000000 を 792 および 793 に代えて実行してみました。

79200000000000000000000000000 < MaxValue < 79300000000000000000000000000

ということで 792 は OK でしたが、793 はオバーフローするという結果になりました。


Microsoft.Data.SqlClient を使った場合は話が違ってきます

System.Data.SqlClient も Microsoft.Data.SqlClient も整数部が 96 ビットというのは同じなので、一番上の画像のコード例のように MaxValue を超える整数を設定した場合はオバーフローするのは同じです。

ただし、Microsoft.Data.SqlClient の場合は、上に書いた例と同様に SQL Server の decimal 型に小数部の桁数を指定してもオーバーフローとはなりませんでした。

System.Data.SqlClient とは実装が変わったようです。ただ、どのように変わったのか、どこまで大丈夫なのかは調べ切れてません。時間があったら調べて、���か分かったら追記します。

Tags: , , ,

ADO.NET

About this blog

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

Calendar

<<  May 2024  >>
MoTuWeThFrSaSu
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

View posts in large calendar