MTG Blog: ◆LINQでの1対多対多の集計

NorthWindにて以下のような関係になっているテーブル。

この３つのテーブルを使って、顧客ごとの注文合計金額を計算してみる。
テーブルの関係は見てわかるとおり、CustomersとOrderが1対多、OrderとOrder_Detailsが1対多。

これをLINQで書く場合、Cutomersから始めるかOrderから始めるか迷うところだが、とりあえずCutomersから始めてみる。

こんな感じだろうか。

using (NorthWindDataContext nwnd = new NorthWindDataContext())
{
nwnd.Log = Console.Out;
var orderTotalByCustomer = nwnd.Customers
.Select(c => new
{
c.CustomerID,
c.CompanyName,
total = c.Order.Sum(o => o.Order_Details.Sum(od => od.Quantity * od.UnitPrice))
});
dataGridView1.DataSource = orderTotalByCustomer;
}

            using (NorthWindDataContext nwnd = new NorthWindDataContext())
            {
                nwnd.Log = Console.Out;
                var orderTotalByCustomer = nwnd.Customers
                    .Select(c => new
                    {
                        c.CustomerID,
                        c.CompanyName,
                        total = c.Order.Sum(o => o.Order_Details.Sum(od => od.Quantity * od.UnitPrice))
                    });

                dataGridView1.DataSource = orderTotalByCustomer;

            }

Order_Detailsは複数明細もっているのでそれを集計し、それをまた複数Order分集計するといった感じ。

なんとなく良さそうかなと思ったのだが実際に実行してみるとエラーが発生する。

普通なら変数の値を追って行きとなるのだが、LINQなんてどうやってデバッグしたものか。
VisualStudioを弄ってあれこれ見てみたがさっぱりチンプンカンプンだ。(^^;

まぁ、こういう時は感で考えるしかない。（本当か？）
とにかく何かがNULLだって言っているんだからそれが何かを考える。しかもDecimalだって言っているのだからDecimalの項目を探してみる。
すると、VisualStudioでマウスカーソルを当てながら見ていくとどうやらOrder_DetailのUnitPriceがDecimalだ。
ん～、VisualStudioって便利になったものだ。（私がちょっと触ってみた2002の頃からはだいぶ進歩している）

あー、そうか、Customerから手繰っているからこいつはLeftOuterJoinイメージになっていて、1件も発注していないCutomerに対するOrder_Detailの金額を計算しようとしてエラーになっているのか。

試しにCustomerを1件だけWhereで抽出して流してみるとうまくいった。
やはりエラーの原因はそれっぽい。

ではどう直すのか。
とりあえず以下のようにしてみた。

view plain print ?

using (NorthWindDataContext nwnd = new NorthWindDataContext())
{
nwnd.Log = Console.Out;
var orderTotalByCustomer = nwnd.Customers.Where(c => c.Order.Count() != 0)
.Select(c => new
{
c.CustomerID,
c.CompanyName,
total = c.Order.Sum(o => o.Order_Details.Sum(od => od.Quantity * od.UnitPrice))
});
dataGridView1.DataSource = orderTotalByCustomer;
}

using (NorthWindDataContext nwnd = new NorthWindDataContext())
            {
                nwnd.Log = Console.Out;
                var orderTotalByCustomer = nwnd.Customers.Where(c => c.Order.Count() != 0)
                    .Select(c => new
                    {
                        c.CustomerID,
                        c.CompanyName,
                        total = c.Order.Sum(o => o.Order_Details.Sum(od => od.Quantity * od.UnitPrice))
                    });

                dataGridView1.DataSource = orderTotalByCustomer;

            }

Orderのカウントを取り０件以外を表示させるって感じ。
するとなんとかうまくいった。

めでたし、めでたし。

でも、ちょっとまてよ。
こんなことなら最初からOrderからスタートすれば良かったんだ。
そうすれば０件判定なんかしなくてもOrderのあるデータだけに絞り込まれる。

ん～、でもゼロはゼロで表示させたいことも業務ではしばしばある。
ここはやはり、Customerから手繰ってゼロはゼロで表示させたい。

そうか、GroupJoinの所で使ったDefaultIfEmptyを使ってDefaultのインスタンスを指定すれば良いんだ。

こうかな？

view plain print ?

using (NorthWindDataContext nwnd = new NorthWindDataContext())
{
nwnd.Log = Console.Out;
var orderTotalByCustomer = nwnd.Customers
.Select(c => new
{
c.CustomerID,
c.CompanyName,
total = c.Order.Sum(o => o.Order_Details
.DefaultIfEmpty(new Order_Details() { Quantity = 0, UnitPrice = 0 })
.Sum(od => od.Quantity * od.UnitPrice))
});
dataGridView1.DataSource = orderTotalByCustomer;
}

            using (NorthWindDataContext nwnd = new NorthWindDataContext())
            {
                nwnd.Log = Console.Out;
                var orderTotalByCustomer = nwnd.Customers
                    .Select(c => new
                    {
                        c.CustomerID,
                        c.CompanyName,
                        total = c.Order.Sum(o => o.Order_Details
                            .DefaultIfEmpty(new Order_Details() { Quantity = 0, UnitPrice = 0 })
                            .Sum(od => od.Quantity * od.UnitPrice))
                    });

                dataGridView1.DataSource = orderTotalByCustomer;

            }

うぎゃ。

サポートされないオーバーロードって何？
そんなもん、普通コンパイル時に判るもんとちゃうのん？

ZZZ

ん、どちらにしてもOrderがないのにOrder_DetailのDefaultを指定しても仕方がないな。
OrderのDefaultを指定するのか？
でも何を指定すりゃいいの？
こうなりゃ、適当に。

view plain print ?

using (NorthWindDataContext nwnd = new NorthWindDataContext())
{
nwnd.Log = Console.Out;
var orderTotalByCustomer = nwnd.Customers
.Select(c => new
{
c.CustomerID,
c.CompanyName,
total = c.Order.DefaultIfEmpty(new Order() { OrderID = 1 }).Sum(o => o.Order_Details
.DefaultIfEmpty(new Order_Details() { OrderID = 1, Quantity = 0, UnitPrice = 0 })
.Sum(od => od.Quantity * od.UnitPrice))
});
dataGridView1.DataSource = orderTotalByCustomer;
}

            using (NorthWindDataContext nwnd = new NorthWindDataContext())
            {
                nwnd.Log = Console.Out;
                var orderTotalByCustomer = nwnd.Customers
                    .Select(c => new
                    {
                        c.CustomerID,
                        c.CompanyName,
                        total = c.Order.DefaultIfEmpty(new Order() { OrderID = 1 }).Sum(o => o.Order_Details
                            .DefaultIfEmpty(new Order_Details() { OrderID = 1, Quantity = 0, UnitPrice = 0 })
                            .Sum(od => od.Quantity * od.UnitPrice))
                    });

                dataGridView1.DataSource = orderTotalByCustomer;

            }

結果は変わらず。(^^;

そうか、OrderとかOrder_Detailとか考えているからダメなんだ。
要は、totalにゼロを入れれば良いのだ。

これでどうだ。

view plain print ?

using (NorthWindDataContext nwnd = new NorthWindDataContext())
{
nwnd.Log = Console.Out;
var orderTotalByCustomer = nwnd.Customers
.Select(c => new
{
c.CustomerID,
c.CompanyName,
total = c.Order.DefaultIfEmpty().Sum(o => (o == null) ? 0 : o.Order_Details
.Sum(od => od.Quantity * od.UnitPrice))
});
dataGridView1.DataSource = orderTotalByCustomer;
}

            using (NorthWindDataContext nwnd = new NorthWindDataContext())
            {
                nwnd.Log = Console.Out;
                var orderTotalByCustomer = nwnd.Customers
                    .Select(c => new
                    {
                        c.CustomerID,
                        c.CompanyName,
                        total = c.Order.DefaultIfEmpty().Sum(o => (o == null) ? 0 : o.Order_Details
                            .Sum(od => od.Quantity * od.UnitPrice))
                    });

                dataGridView1.DataSource = orderTotalByCustomer;

            }

おー、出た出た。

ちゃんと金額がゼロのやつもある。

でも、合計金額とか本当に正しいのだろうか。
もう疲れたので検証せず（笑）

最後に、これで出力されたSQLはこんな複雑な奴。

自分じゃ絶対書けないな・・・。
LINQって凄い？

MTG Blog

ページ

2011年4月16日土曜日

◆LINQでの1対多対多の集計

0 件のコメント:

コメントを投稿

私が最近チェックした記事