Pandora Pocket

IT系と日常系の備忘録。三日坊主。

ListからSQL検索条件を生成するLINQ

.NET Core 3.1対応をしているのですが、それに伴ってEntity Framework Coreもv3にあげたところ、LINQのクライアントサイド評価が行われなくなったことでいくつかのクエリが使えなくなったため、いろいろ調べながら対応しています。

対応が必要だったうちの一件が下記のようなもの。

class TBL {
string Data1;
string Data2;
}

このようなクラスを保持するリスト(List)に対して、

.Where(x => list.Any(l => l.Data1 == x.Data1 && l.Data2 = x.Data2))

のように、含まれている値と一致するレコードだけを取得するというものです。
SQLにしてみれば

WHERE (TBL.Data1 == [listの値1-1] && TBL.Data2 == [listの値1-2])
OR (TBL.Data1 == [listの値2-1] && TBL.Data2 == [listの値2-2])
・・・

みたいな感じ。
LINQ to SQLだとこのクエリ、SQLに変換してサーバーサイドで実行してくれないんですね。
式木をうまいことこねこねして動的に.Where内の値を生成するようにしようと思ったのですが、今の力量だとちょっと手間取りそうで、それに時間をかけてられないこともあり、とりあえずLINQを使わずFromSqlRawで直接SQLを実行することにしました。
SQLで実行するにしてもWHERE句に指定する条件はリストから動的に生成する必要があります。

そんな時に便利なのが下記のLINQ

var param = list
.Aggregate(
string.Empty,
(current, tbl) => current + $" OR ([Data1] = '{tbl.Data1}' AND [Data2] = {tbl.Data2}) ");

これならlistに格納されている値分のOR条件式が一つの文字列に吐き出されるので、あとは

var sql = $@"SELECT * FROM TBL
WHERE (1=0)
{param}";

こんな感じでSQLにぶち込んで、EFなりDapperなりに渡してやればOK。

e-Taxを利用した確定申告では控除証明書を提出する必要はない

今回初めて青色申告をすることになり、その中で知ったこと。

基本的に確定申告では社会保険料控除や寄附金控除(ふるさと納税とか)などの証明書を提出する必要があります。

e-Taxはオンライン上で手続きが終了するので、控除証明書をどうやって送付するのかと思い調べたところ、下記のページがヒット。

平成20年1月4日以後に、平成19年分以後の所得税の確定申告書の提出をe-Taxを利用して行う場合、次に掲げる第三者作成書類については、その記載内容を入力して送信することにより、これらの書類の税務署への提出又は提示を省略することができます。

ということで、下記の証明書はe-Taxでの確定申告を行う場合提出が不要です。

  •  給与所得者の特定支出の控除の特例に係る支出の証明書
  •  個人の外国税額控除に係る証明書
  •  雑損控除の証明書
  •  医療費の領収書、セルフメディケーション税制の医薬品購入の領収書、一定の取組を明らかにする書類
  •  医療費に係る使用証明書等(おむつ証明書など)
  •  社会保険料控除の証明書
  •  小規模企業共済等掛金控除の証明書
  •  生命保険料控除の証明書
  •  地震保険料控除の証明書
  •  寄附金控除の証明書
  •  勤労学生控除の証明書
  •  住宅借入金等特別控除に係る借入金年末残高証明書(適用2年目以降のもの)
  •  特定増改築等住宅借入金等特別控除(バリアフリー改修工事)に係る借入金年末残高証明書(適用2年目以降のもの)
  •  特定増改築等住宅借入金等特別控除(省エネ改修工事等)に係る借入金年末残高証明書(適用2年目以降のもの)
  •  特定増改築等住宅借入金等特別控除(多世帯同居改修工事)に係る借入金年末残高証明書(適用2年目以降のもの)
  •  政党等寄附金特別控除の証明書
  •  認定NPO法人寄附金特別控除の証明書
  •  公益社団法人等寄附金特別控除の証明書
  •  特定震災指定寄附金特別控除の証明書

当然ながら税務調査で提出できなければ無効となるため、5年間は大切に保存する必要があります。

必要があるときは、原則として法定申告期限から5年間、税務署等からこれらの書類の提示又は提出を求められることがあります。

個人事業主なら領収書を7年間保管する義務があるので同じように保存しておきましょう。 e-Tax、使い勝手悪いけどこういうところは良いですね。

SQLのWHERE 1=1と WHERE 1=0について

たまに使うのにどう使うのかを度忘れすることがあるので備忘録。

前提

テーブルから値を取得する際の条件としてWHERE句を設定するが、検索対象によってWHERE句に指定する検索条件が増減しうる。

例えば生徒の情報(氏名、性別等)を保持するテーブル STUDENT があったとして、全件取得したければ条件を付ける必要がないので

SELECT * FROM STUDENT

で済む。
これに対し氏名の姓だけを検索条件に加えるなら

SELECT * FROM STUDENT WHERE 氏名 LIKE '(姓)%'

となる。
プログラムでデータを取得する場合、条件の組み合わせごとにSQLをそれぞれ用意するような非効率なことはしたくないので、WHERE 以降の検索条件を動的につなげたい。
SQLは検索条件がない場合は WHERE があるとエラーになるため、検索条件がある場合と検索条件がない場合で条件分岐が必要になる。
この時に便利なのが(1=1)、(1=0)となる。

WHERE (1=1)

1=1は当然正しいのでtrue。

SELECT * FROM STUDENT WHERE (1=1)

この場合、(1=1)が検索条件となり、必ずtrueであるため、STUDENTテーブル上のすべてのレコードを取得することになる。

検索条件が存在する場合、

SELECT * FROM STUDENT WHERE (1=1)
AND 氏名 LIKE '(姓)%'

のように(1=1)以降にANDで条件を追加していけば良い。

なおMySQLではWHERE 1 でも同じことになる模様。

WHERE (1=0)

(1=1)の場合、以降に検索条件がなければ全件取得されるが、条件がない場合は一件も値を取得してほしくない場合もある。
その場合は(1=0)を用いる。

SELECT * FROM STUDENT WHERE (1=0)

この場合、(1=0)はfalseであるため、STUDENTテーブル上のレコードを1件も取得しない。

SELECT * FROM STUDENT WHERE (1=0)
OR 氏名 LIKE '(姓)%'

のように、OR (検索条件) として条件を付与することで、検索条件に適合するレコードがあればそれだけを取得する。

なお、bool値としてfalseになればいいわけなので、別に(2=0)でもなんでもよい。MySQLなら0でもOK。が、ややこしいので(1=0)にするのが無難。

Postmanを使い始めて躓いた点の備忘録

以前使っていたAPIをたたくツールが公開終了してしまい、最近はPowerShellで下記のように叩いていたんですが、

$body = @{hoge="fuga"; foo="bart"} | ConvertTo-Json -Compress
Invoke-RestMethod -Uri "https://localhost:5001/api/RESTfulApi" -Method POST -Body $body -ContentType application/json

やっぱり専用のクライアントを使ったほうが楽だなと思い直してPostmanを使い始めました。

乗り換えた際に躓いた点の備忘録。

続きを読む