#この記事、何?
Linq書いてみたくなった。
というのも、マスタ系の静的なデータだったら、都度DBにアクセスするよりも保持しておいてもいいよね。
あと、更新したかどうかを照合するために持っておくとか。
で、それをC#のDataTable型で保持しておいて、それ同士にjoinかけないかな、と。
で、欲張って複数項でjoinが必要な場合まで想定してみて・・・と思ったらはまったという話。
反省は文末にするとして、具体的な話に続く。。。
※追記ありです※
コメントいただいたおかげで、泣き言言ってたり妥協してた個所がだいぶ改善している・・・はず。
※も1個追記いただきました※
文末に追記です。
#環境
Windows 8 Pro x64
PostgreSQL 9.6.11
Visual Studio 2017
.netframework 4.6.1
Npgsql 4.0.5
#やりたいこと
項目1つでJoinできる間柄と、項目2つでJoinできる事柄を想定して以下のようなダミーデータを作った。
まずは商品マスタ(m_item)で名称と値段を持つ。
販売の実績をレシートデータ(t_receipt)に持ち、販売数量を持つ。
併せて、性別{1, 2}と年代{1,2,3}を持つ。
で、性別年代別にアットランダムに割引するような感じで、割引対象(target)を
割引対象選択(t_coupon)にもつ。
だから、商品マスタとレシートデータが商品IDでJoinする。
また、レシートデータと割引対象が性別と年代でJoinする。
まずは、1項目のjoinだけを対象。
tbl.AsEnumerable()
と、
tbl.Rows
の二通りの書き方があるみたいだからそれぞれ書いてみた。
それがquery1とquery2。ここまではまあよし。
で、そのままだと2項目でのjoinの書き方が分からなかったので別な書き方で書いた。
それがquery3とquery4。ぐだぐだ。
出力はまあ・・・適当にMessageBox.Showしてみたくらい。
##商品マスタ
create table m_item
(
id integer
, name text
, price integer
)
;
insert into m_item (id, name, price) values (1, 'candy', 30);
insert into m_item (id, name, price) values (2, 'coffee', 150);
insert into m_item (id, name, price) values (3, 'juice', 100);
##レシートデータ
create table t_receipt
(
id integer
, itemid integer
, amount integer
, sex integer
, agerank integer
)
;
insert into t_receipt (id, itemid, amount, sex, agerank) values (1, 2, 1, 1, 1);
insert into t_receipt (id, itemid, amount, sex, agerank) values (2, 1, 3, 2, 2);
insert into t_receipt (id, itemid, amount, sex, agerank) values (3, 2, 5, 2, 1);
insert into t_receipt (id, itemid, amount, sex, agerank) values (4, 1, 2, 1, 1);
insert into t_receipt (id, itemid, amount, sex, agerank) values (5, 3, 2, 1, 3);
##割引対象選択
create table t_coupon
(
sex integer
, agerank integer
, target integer
)
;
insert into t_coupon (sex, agerank, target) values (1, 1, 1);
insert into t_coupon (sex, agerank, target) values (1, 2, 0);
insert into t_coupon (sex, agerank, target) values (1, 3, 0);
insert into t_coupon (sex, agerank, target) values (2, 1, 1);
insert into t_coupon (sex, agerank, target) values (2, 2, 1);
insert into t_coupon (sex, agerank, target) values (2, 3, 0);
#できたもの
private void Test04()
{
var tItem = GetTable("m_item");
var tReceipt = GetTable("t_receipt");
var tCoupon = GetTable("t_coupon");
var query1 =
from i in tItem.AsEnumerable()
join r in tReceipt.AsEnumerable()
on i.Field<int>("id") equals r.Field<int>("itemid")
where r.Field<int>("amount") > 2
select new
{
ItemID = i.Field<int>("id"),
ItemName = i.Field<string>("name"),
Price = i.Field<int>("price"),
Amount = r.Field<int>("amount"),
TotalPrice = i.Field<int>("price") * r.Field<int>("amount")
};
var query2 =
from DataRow i in tItem.Rows
join DataRow r in tReceipt.Rows
on i["id"] equals r["itemid"]
where (int)r["amount"] > 2
select new
{
ItemID = i["id"],
ItemName = i["name"],
Price = i["price"],
Amound = r["amount"],
TotalPrice = (int)i["price"] * (int)r["amount"]
};
//query3とquery4は迷走時の黒歴史。文末の追記のほうが良いです。
var query3 =
tItem.AsEnumerable()
.Join(tReceipt.AsEnumerable(),
i => new { f1 = i.Field<int>("id") },
r => new { f1 = r.Field<int>("itemid") },
(i, r) =>
new
{
ItemID = i.Field<int>("id"),
ItemName = i.Field<string>("name"),
Price = i.Field<int>("price"),
Amount = r.Field<int>("amount"),
TotalPrice = i.Field<int>("price") * r.Field<int>("amount"),
Sex = r.Field<int>("sex"),
AgeRank = r.Field<int>("agerank")
}
)
.Join(tCoupon.AsEnumerable(),
r => new { r.Sex, r.AgeRank },
c => new { Sex = c.Field<int>("sex"), AgeRank = c.Field<int>("agerank") },
(r, c) =>
new
{
r.ItemID,
r.ItemName,
r.Price,
r.Amount,
r.TotalPrice,
r.Sex,
r.AgeRank,
Target = c.Field<int>("target")
}
)
.Where(x => x.Amount > 2)
//.Sum(x => x.TotalPrice)
;
var query4 =
(from DataRow i in tItem.Rows select i)
.Join((from DataRow r in tReceipt.Rows select r),
i => new { f1 = i["id"] },
r => new { f1 = r["itemid"] },
(i, r) =>
new
{
ItemID = i["id"],
ItemName = i["name"],
Price = i["price"],
Amount = r["amount"],
TotalPrice = (int)i["price"] * (int)r["amount"],
Sex = r["sex"],
AgeRank = r["agerank"]
}
)
.Join((from DataRow c in tCoupon.Rows select c),
r => new { r.Sex, r.AgeRank },
c => new { Sex = c["sex"], AgeRank = c["agerank"] },
(r, c) =>
new
{
r.ItemID,
r.ItemName,
r.Price,
r.Amount,
r.TotalPrice,
r.Sex,
r.AgeRank,
Target = c["target"]
}
)
.Where(x => (int)x.Amount > 2)
//.Sum(x => (int)x.TotalPrice)
;
string tmp = "";
tmp = "";
foreach (var item in query3)
{
tmp += item.ToString();
tmp += "\n";
}
//tmp = query3.ToString();
MessageBox.Show(tmp);
}
private DataTable GetTable(string tableName)
{
string connectionString = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=manager;Database=postgres;";
var conn = new Npgsql.NpgsqlConnection(connectionString);
conn.Open();
string sql = "select * from " + tableName;
var da = new Npgsql.NpgsqlDataAdapter(sql, conn);
var ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
#感想
うーん・・・query1とquery2に関して、目的が「簡単に書きたい」なので、そういう意味ではquery2が優勢。
ただ、途中でcase書かないといけなかったのでちょっと微妙。
長くてもIntellisenceが助けてくれるんだから、そういう意味ではquery1がいい気がするわ。
で、query3とquery4は・・・ないな。
やむを得なかったら使うかもだけど、ここまで煩雑に書かないといけないのなら直接SQL書きたい。。。
改めて、query1を見てみると・・・うーん・・・そんなに冗長でもないよね?
「AsEnumerable」の表現にちょっと面喰っちゃったけど、改めて考えてみると妥当な気がしてきた。
・・・いや、「妥当」というか、「自分の理解の範囲内に落ちる」という感じ。
だって、DataTable形でtblを持っているとして、
(書き方A) foreach row in tbl
(書き方B=query2) foreach row in tbl.Rows
(書き方C=query1) foreach obj in tbl.AsEnumerable
本音は書き方Aで書きたいところだけど、tblは繰り返し構造を持つけど繰り返し構造そのものではないからNG。
だから、テーブルの繰り返し構造を示すと、書き方Bになる。だから、r["amount"]とかはDataRow型で、int型じゃないので直接掛け算はできない。
じゃあ、となると、Asである意味型変換した感じで書き方Cになる。
まあ・・・castを嫌わなければ文字数減らせるから書き方Bがいいかも?
あと、query3,query4はお手上げ。
そもそもあんな冗長な書き方をしている時点で理解できてない気がする。
まあ・・・当面、query1かquery2の書き方で書いてみて、見えてきたら改めてこの記事書き直そうかな。
その時のタイトルは、「C#手遊び(Linqもっとまじめに書いてみた)」とかで。。。
#追記１：「もっとまじめに書いてみた(当日版)」
コメントいただきました。ありがとうございます。
■Join by using composite keys
https://docs.microsoft.com/ja-jp/dotnet/csharp/linq/join-by-using-composite-keys
正直言うと、このページ、query3とかquery4を書くとき一度見ました。
でもその時は・・・よくわからなかった!
というか、「into deatils」という表現自体が全然別の目的の話だと思って違う件だと理解(誤解)してた。
で、せっかくいただいたコメントみて、「あっ、違ってないってことね?」と。
で、写経していじってみて・・・なるほど、と。
で、できたのがこれ。
private void Test05()
{
//https://docs.microsoft.com/ja-jp/dotnet/csharp/linq/join-by-using-composite-keys
var tReceipt = GetTable("t_receipt");
var tCoupon = GetTable("t_coupon");
var query = from DataRow r in tReceipt.Rows
join DataRow c in tCoupon.Rows
on (r["sex"], r["agerank"]) equals (c["sex"], c["agerank"])
select (r, c);
string tmp = "";
foreach (var item in query)
{
tmp += item.r["itemid"] + "/" + item.r["sex"] + "/" + item.r["agerank"] + "/" + item.c["target"];
tmp += "\n";
}
MessageBox.Show(tmp);
}
タプルで行けましたね。あと、これいいですね。書いてて思った。
C#のメリットはかなり強い型付けだと思っていて、直感的には「多少ミスってもコンパイラが見つけてくれる」って感じ。
ただ、(int)とかってcastしてしまうとコンパイラに対して、「君、チェックしなくていいから!」って言っている感じ。
なのでそのメリットが失われてしまう。正直ヤだ、そんなのできれば書きたくない。
ホントいうと、row["項目名"]もヤなんだけど、よく考えたら一度通過テストをするとミスがあっても絶対拾えるから・・・じゃ、いいか、と。
だったら、query3,query4のような中途半端な書き方ではなく、これがいいね。
教訓：ちゃんと公式読めるようになろう！
本音：munielさんありがとうございます！
#追記２：いくつかノウハウいただきました！
albireoさんありがとうございます。
一部抜粋して要点だけ。
##「newしないほうがいいよ！」
ですよね～迷走中に、にゅーにゅーいってて、なんかヤだな・・・って思ってました。
どう考えても、今どきの洗練された言語の書き方じゃない・・・
けど、その時点(この記事の初版書いた時点)で、あたしのベストだったので、「あ～これ、ぜったい書き方わかってないわ～」とおもってました。すみません。。。
##「where先に書いたほうがいいよ」
なるほど。そりゃそうですね。ここら辺はSQL書く時の考え方に通じるところありますね。
##「っていうか、クラス作ったら?」
ごもっとも。この点はこの投稿では端折ったところでした。
動的に任意のテーブルを相手にできることを目的にしたかったということもあります。
しかしこの記事を通してみると、そこに意固地になるより素直にクラスを作ったほうがいい気もしますね。
やっぱ、Listとかやって使うとVisual Studioがかなり助けてくれるのが心地いですよね!