Sqlを動的に作成する場合には、「空なら消えてくれればいいのに」があります。
LambdicSqlでは以下の場合には空を渡すと要素や句が消えます。
- Select句のメンバ
- Join
- Where
- Having
- Order By
Select句のメンバ
//Typeを表示するときのみCase式を挿入する var type = new Sql<string>(); if (isSelectType) { type = Db<DB>.Sql(db => Case(). When(db.tbl_remuneration.money < 2000).Then("Cheap"). When(db.tbl_remuneration.money < 3000).Then("Middle"). Else("High"). End()); } var sql = Db<DB>.Sql(db => Select(new SelectData { Name = db.tbl_staff.name, PaymentDate = db.tbl_remuneration.payment_date, Money = db.tbl_remuneration.money, //タイプ Type = type }). From(db.tbl_remuneration). Join(db.tbl_staff, db.tbl_staff.id == db.tbl_remuneration.staff_id) );
isSelectTypeが有効のときは
SELECT tbl_staff.name AS Name, tbl_remuneration.payment_date AS PaymentDate, tbl_remuneration.money AS Money, CASE WHEN (tbl_remuneration.money) < (@p_0) THEN @p_1 WHEN (tbl_remuneration.money) < (@p_2) THEN @p_3 ELSE @p_4 END AS Type FROM tbl_remuneration JOIN tbl_staff ON (tbl_staff.id) = (tbl_remuneration.staff_id)
無効のとき、つまりtypeが空の時は消えます。
SELECT tbl_staff.name AS Name, tbl_remuneration.payment_date AS PaymentDate, tbl_remuneration.money AS Money FROM tbl_remuneration JOIN tbl_staff ON (tbl_staff.id) = (tbl_remuneration.staff_id)
Where、Having
これらの句は条件を動的に組み立てるユーティリティもサポートしています。
Conditionクラスは第一引数がnullなら消えます。
両方消えると句自体がなくなります。
var minCondition = false; var maxCondition = false; var exp = Db<DB>.Sql(db => new Condition(minCondition, 3000 < db.tbl_remuneration.money) && new Condition(maxCondition, db.tbl_remuneration.money < 4000)); var query = Db<DB>.Sql(db => Select(Asterisk()). From(db.tbl_remuneration). Where(exp) );
SELECT * FROM tbl_remuneration
Join、Order By
あまりないかもしれませんが、JoinとOrder Byも消えます。
var tbl_staff = new Sql<Staff>(); var asc = new Sql<OrderByElement>(); var desc = new Sql<OrderByElement>(); var sql = Db<DB>.Sql(db => Select(new SelectedData2 { Id = db.tbl_remuneration.staff_id }). From(db.tbl_remuneration). Join(tbl_staff, db.tbl_remuneration.staff_id == tbl_staff.Body.id). OrderBy(asc, desc));
SELECT tbl_remuneration.staff_id AS Id FROM tbl_remuneration
こんな感じで楽にSQL構築ができます。