きなこもち.net

.NET Framework × UiPath,Orchestrator × Azure × AWS × SIer

EF Core 3.1 × Postgres × 検索条件のON/OFF

この記事の目的

この記事は、
EF Coreが出力する実際のSQLの検証を行うこと
を目的としています。
今回は、Selectの中のWhere句に絞って、いろいろな条件でクエリを投げた結果をまとめていきます。
実行環境などは、前回のブログと同じものを利用しています。

前回からの変更点

とくになし

本題

文字列比較(メソッド構文)

シンプルに比較した場合

シンプルに、=で比較した場合の結果。
想定内のクエリが発行されていました。

context.Account.Where(row => row.Username == conditions.Username).ToList();


発行されたSQLがこちら。

 SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE a.username = $1
-- parameters: $1 = 'Sample_User'
シンプルに比較した場合2

StringクラスのEquealsメソッドで比較した場合。
=と同じ結果でした。

context.Account.Where(row => row.Username == conditions.Username).ToList();


発行されたSQLがこちら。

SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE a.username = $1
-- parameters: $1 = 'Sample_User'
Containで比較した場合

指定した文字列が含まれる文字列を検索する際の処理を想定しています。
SQLで書けと言われたら、like文を使って、username like '%Sample_User%' とすることを想定していましたが、STRPOS関数を使って検索をしていることがわかりました。STRPOS関数は、指定した部分文字列の位置を特定する関数です。知らなかった…
www.postgresql.jp
また、パラメータが、空文字列であった場合、Trueになるように設定されているようです。

var account = context.Account.Where(row => row.Username.Contains(conditions.Username)).ToList();


発行されたSQLがこちら。

 SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE ($1 = '') OR (STRPOS(a.username, $1) > 0)
-- parameters: $1 = 'Sample_User'
正規表現として比較した場合

Sample_Userを正規表現のパターン文字列として、正規表現で比較した場合の結果がこちらになります。
ARE埋め込みオプションとか存在を認識していなかった。。。これを使うことで、部分的な改行を区別するマッチを使ったマッチが実行されます。部分的に改行を区別するマッチが指定されると、「.とブラケット式は改行を区別するマッチを行うようになりますが、^と$は変更されません。」とのことでした。

  • ~について

www.postgresql.jp

  • p(ARE埋め込みオプション文字)について

www.postgresql.jp

var account = context.Account.Where(row => Regex.IsMatch(row.Username, conditions.Username)).ToList();


発行されたSQLがこちら。

SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE a.username ~ ('(?p)' || $1)
-- parameters: $1 = 'Sample_User'

検索条件の状態によって、WHERE句をON/OFF

文字列比較と同じ題材を使って、検索条件の状態によって、動的にWHERE句を切り替える操作の検証をしました。
使っているのは、VarChar型のUserName列なので、検索条件(UserName)がNullの場合、String.Emptyの場合、何かしらの文字列が入っている場合の3パターンで検証してみます。

WHERE句の中で比較した場合

NullやString.Emptyの場合、Falseを指定しているので、WHERE FALSE = TRUEという条件が出てきてしまいます。ケースバイケースですが、指定した条件がNullやStrimg.Emptyの場合、検索結果を0件にしたいということであれば、これで問題ないですね。※その場合、DBにクエリを出さずに無条件で0件の結果を返したほうがいいはずですが・・・

account = context.Account.Where(row => 
     string.IsNullOrWhiteSpace(conditions.Username) ==false? row.Username.Contains(conditions.Username) : false).ToList();


発行されたSQLがこちら。

--conditions.Username=Nullの場合
 SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE FALSE = TRUE

--conditions.Username=String.Emptyの場合
 SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE FALSE = TRUE

--conditions.Username=”Sample_User”の場合
SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE ($1 = '') OR (STRPOS(a.username, $1) > 0)
-- parameters: $1 = 'Sample_User'
WHERE句の外で比較

Usernameに文字列が入っている時だけ、検索条件を追加して、それ以外の場合(Null、String.Empty)の場合は、全権検索したいということであれば、こんな感じで実装すればいいってことだと思います。

var account = context.Account;
if (string.IsNullOrWhiteSpace(conditions.Username))
{
       account.Where(row => row.Username.Contains(conditions.Username));
}
account.ToList();

番外編:検索条件の状態によって、WHERE句をON/OFF(複数条件の場合)

検索条件を、以下のようにしたときのクエリです。

  • User Name:Null、String.Empty、Sample_User
  • Password:Sample

また、条件については、string.IsNullOrWhiteSpace(conditions.Username) == false ? row.Username.Contains(conditions.Username) : falseとしてしまうと、TRUE=FALSEという条件が発行されてしまうので、string.IsNullOrWhiteSpace(conditions.Username) == false ? row.Username.Contains(conditions.Username) : trueとしています。


メソッド構文だと・・・

var account = context.Account.Where(row => row.Password.Contains(conditions.Password))
                    .Where(row => string.IsNullOrWhiteSpace(conditions.Username) == false ? row.Username.Contains(conditions.Username) : true)
                    .Select(row => row)
                    .ToList();

発行されたSQLがこちら。

- User Name:Null/String.Empty
- Password:Sample
SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE ($1 = '') OR (STRPOS(a.password, $1) > 0)
-- parameters: $1 = 'Sample'

- User Name:Sample_User
- Password:Sample
SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE (($1 = '') OR (STRPOS(a.password, $1) > 0)) AND (($2 = '') OR (STRPOS(a.username, $2) > 0))
-- parameters: $1 = 'Sample', $2 = 'Sample_User'


クエリ式だと・・・

var account = (from accountData in context.Account
                            where (accountData.Password.Contains(conditions.Password)) &&
                                  string.IsNullOrWhiteSpace(conditions.Username) == false ? accountData.Username.Contains(conditions.Username) : true
                            select accountData).ToList();

発行されたSQLがこちら。

- User Name:Null/String.Empty
- Password:Sample
SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a


- User Name:Sample_User
- Password:Sample
SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE CASE
            WHEN ($1 = '') OR (STRPOS(a.password, $1) > 0) THEN ($2 = '') OR (STRPOS(a.username, $2) > 0)
            ELSE TRUE
        END
-- parameters: $1 = 'Sample', $2 = 'Sample_User'

え・・・なんで、クエリ式だと、WHERE句がなくなるの・・・( ゚Д゚)
と思ったら、二個目の条件を括弧でくくったら、想定通りのクエリが発行されました。どこまでで1ブロックの条件かを明示的に書かないといけないということか。

var account = (from accountData in context.Account
                            where (accountData.Password.Contains(conditions.Password)) &&
                                  (string.IsNullOrWhiteSpace(conditions.Username) == false ? accountData.Username.Contains(conditions.Username) : true)
                            select accountData).ToList();

発行されたSQLがこちら。

SELECT a.user_id, a.created_on, a.email, a.last_login, a.note, a.password, a.sex_id, a.username
        FROM account AS a
        WHERE ($1 = '') OR (STRPOS(a.password, $1) > 0)
2020-06-14 04:26:41.971 UTC [616] DETAIL:  parameters: $1 = 'Sample'

まとめと課題

  • 条件はブロックごとに括弧でくくる。
  • WHEREのなかで、Nullチェックすると、読みにくいので、分けて書いたほうがよさげ
  • 課題:特に意識していなかったけど、メソッド構文でWHERE条件をOR条件にするのってどうやるんだろう・・・クエリ式だと、||でつなげればできるけど・・・これは次回調べる!


つづく!