お久しぶりです。

今年はWindows Embedded Compact 7によるハンディターミナルの開発ばかりやってたのですが、今月からうってかわってAzure Functions V2によるWebアプリ開発になりました。

振れ幅の大きさに自分でも笑ってしまいます。

それはさておきDBへのクエリ結果から配列がネストされた構造のJSONに変換するのに簡単な方法はないものか色々と悩みましたが、あまり簡単な方法はなかったようでエクセレントな解法ではないものの、まあ許容できる範囲かと思ったので検証結果を公開します。

検証には次のような条件を前提としています。

• JOINするのは3テーブル
• それぞれのテーブルには単一カラムの主キーがある
• クエリ解析にはDapperのマルチマッピング機能を利用する

そして以上の条件を踏まえ、Dapper公式サイトのマルチマッピング機能のソースを改変した検証コードが次です。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using Dapper;

namespace ConsoleApp1 {

    class Program {

        public class MtProducts {
            public int ProductID { get; set; }
            public string ProductName { get; set; }
        }
        public class OrderDetail {
            public int OrderDetailID { get; set; }
            public List ProductList { get; set; }
            public int Quantity { get; set; }
        }

        public class Order {
            public int OrderID { get; set; }
            public int CustomerID { get; set; }
            public int EmployeeID { get; set; }
            public DateTime OrderDate { get; set; }
            public int ShipperID { get; set; }
            public List OrderDetails { get; set; }
        }

        /// 

        /// Dapperマルチマッピングの検証
        /// original: https://dapper-tutorial.net/result-multi-mapping
        /// 
        /// 
        static void Main(string[] args) {
            string sql = @"
                    SELECT TOP 10 
	                A.OrderID,
	                A.CustomerID,
	                A.EmployeeID,
	                A.OrderDate,
	                A.ShipperID,
	                B.OrderDetailID,
	                B.Quantity,
	                MP.ProductID,
	                MP.ProductName
                FROM dbo.Orders AS A 
                INNER JOIN OrderDetails AS B 
                ON A.OrderID = B.OrderID
                INNER JOIN MtProducts AS MP
                ON B.ProductID = MP.ProductID
                          ;";

            SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder() {
                DataSource = @"myserver\SQLSERVER,1433",
                InitialCatalog = "MyDbName",
                UserID = "myuser",
                Password = "mypassword",
                IntegratedSecurity = true

            };

            // 出力用リスト
            var orders = new List();

            // ルックアップテーブル代わりの連想配列
            var ordDict = new Dictionary, List>();
            using (var connection = new SqlConnection(cb.ToString())) {

                // Query(sql,func map,param)
                var list = connection.Query(
                    sql: sql,
                    map: (ord, detail, products) => {
                        // 現在のOrderIDとOrderDetailIDが読込済でなければ初期化する
                        var key = new Tuple(ord.OrderID, detail.OrderDetailID);
                        var pBuf = new List();
                        if (!ordDict.TryGetValue(key, out pBuf)) {
                            detail.ProductList = new List();
                            ord.OrderDetails = new List();
                            ord.OrderDetails.Add(detail);
                            ordDict.Add(key, detail.ProductList);
                            if (orders.Find(x => x.OrderID == ord.OrderID) == null)
                                orders.Add(ord);
                        }

                        // 現在のOrderIDとOrderDetailIDにproductsをリスト追加
                        if (ordDict.TryGetValue(key, out pBuf)) {
                            ordDict[key].Add(products);
                        }
                                
                        // 出力用リストを検索し設定がなければ追加、設定済であれば上書
                        var od1 = orders.First(x => x.OrderID == ord.OrderID);
                        if (od1 != null) {
                            var od2 = orders.Find(x => x.OrderID == ord.OrderID).OrderDetails.Find(y => y.OrderDetailID == detail.OrderDetailID);
                            if (od2 == null) {
                                // 追加
                                detail.ProductList = ordDict[key];
                                orders.Find(x => x.OrderID == ord.OrderID).OrderDetails.Add(detail);
                            } else {
                                // 上書
                                detail.ProductList = ordDict[key];
                                orders.Find(x => x.OrderID == ord.OrderID).OrderDetails.Find(y => y.OrderDetailID == detail.OrderDetailID).ProductList = detail.ProductList;
                            }
                        }

                        // この戻り値はダミー
                        return ord;
                    },
                    commandType: null,
                    splitOn: "OrderDetailID,ProductID"
                );

                // 出力用リストを返す
                var s = JsonConvert.SerializeObject(orders);
                Console.Write(s);
            }

        }
    }
}

-- DDLなど
CREATE TABLE [dbo].[Orders](
	[OrderID] [int] NOT NULL,
	[CustomerID] [int] NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[OrderDate] [datetime] NOT NULL,
	[ShipperID] [int] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderDetails](
	[OrderDetailID] [int] NOT NULL,
	[OrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
(
	[OrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MtProducts](
	[ProductID] [int] NOT NULL,
	[ProductName] [ntext] NULL,
 CONSTRAINT [PK_MtProducts] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO [dbo].[MtProducts]([ProductID],[ProductName]) VALUES (1,'肉');
INSERT INTO [dbo].[MtProducts]([ProductID],[ProductName]) VALUES (2,'魚');
INSERT INTO [dbo].[MtProducts]([ProductID],[ProductName]) VALUES (3,'米');
GO
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10248,90,5,'2018/07/04 12:00:00',3);
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10249,81,6,'2018/07/05 12:00:00',1);
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10250,34,4,'2018/07/06 12:00:00',2);
INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipperID]) VALUES (10251,84,3,'2018/07/07 12:00:00',1);
GO
INSERT INTO [dbo].[OrderDetails]([OrderDetailID],[OrderID],[ProductID],[Quantity]) VALUES (1,10248,1,12);
INSERT INTO [dbo].[OrderDetails]([OrderDetailID],[OrderID],[ProductID],[Quantity]) VALUES (2,10248,2,10);
INSERT INTO [dbo].[OrderDetails]([OrderDetailID],[OrderID],[ProductID],[Quantity]) VALUES (3,10249,3,5);

上記のコードのポイントは次の通りです。

• レコードの読込判定にディクショナリを用いる
• ディクショナリに格納するのは最下層の配列
• 最終的に出力するリストにひとまず追加し、追加済みであれば上書する

じゃあ3階層を超えたネストはどうすればいいんだ、という疑問については

「3階層目からメソッド分割して別途Query呼び出せばいいんじゃね？」

と思ったりしますが、DapperのQueryがネストして呼び出せるかどうか試していないので断言は出来ません。

それにしてもWordpressを更新したら、前のような記述が出来なくなって、やたら平坦て簡素な書き方を強いられてしまいます。

しかもシンタックスハイライターが動かんのでソースコード見づらいし・・・。

勝手に変えるなよ・・・と思っても、何も考えずよく知らないWordpressを選んだ私の自業自得なのでしょう。