SQLの動的組み立て(LINQ to 〜)
LINQって便利なんだけど、
宣言型だから動的に検索条件変えるってのが難しい。
いくつか記事がでてるけど、正直わかんないのね。
有象無象チーム開発で使うの難しいんじゃないかなー?
LINQを活用した簡単XMLデータベース・アプリケーション − @IT
LINQ文で動的にWhere句を組み立てるには?[3.5、C#、VB] − @IT
だったら文字列連結で直接クエリ書いちゃう方が簡単な気がしなくもない。
(直接の方が速いし・・・)
でも、単純な条件のクエリにおいては良い方法があったのでメモしておく。
業務アプリだと画面で入力した場合は条件に加えて、未入力の場合は条件からはずすといった
単純なクエリが大部分を占めると思うので結構な範囲カバーできるんじゃないかな。
LINQ to SQLの場合
準備
NorthWindのCategoriesテーブルからDataContextを作成しておく
コード
int? categoryID = null; string categoryName = string.Empty; string description = string.Empty; // nullチェックとORでくっつけてやる from c in ctx.Categories where (categoryID == null || c.CategoryID == categoryID) && (string.IsNullOrEmpty(categoryName) || c.CategoryName == categoryName) && (string.IsNullOrEmpty(description) || c.Description == description) select c;
1.上記を実行した場合に発行されるSQL
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture] FROM [dbo].[Categories] AS [t0]
2.categoryIDにnull以外を入れて実行した場合に発行されるSQL
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture] FROM [dbo].[Categories] AS [t0] WHERE ([t0].[CategoryID]) = @p0
3.categoryIDとcategoryNameを指定した場合に発行されるSQL
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture] FROM [dbo].[Categories] AS [t0] WHERE (([t0].[CategoryID]) = @p0) AND ([t0].[CategoryName] = @p1)
うん。いい感じです。
変数がnullのやつはSQLの条件部に入ってないです。
実際のSQLで↓のように書いちゃうと、
NULLチェックの所為でインデックスが効かなくなっちゃうのでダメなのです。
DECLARE @categoryID int SET @categoryID = 3 SELECT * FROM Categories WHERE ( @categoryID IS NULL OR CategoryID = @categoryID )
ちなみに、LINQ to Entities(SQL SERVER)ではどーなる?
int? categoryID = null; string categoryName = string.Empty; string description = string.Empty; from c in em.Categories where (categoryID == null || c.CategoryID == categoryID) && (string.IsNullOrEmpty(categoryName) || c.CategoryName == categoryName) //&& (string.IsNullOrEmpty(description) || c.Description == description) select c;
結果
SELECT [Extent1].[CategoryID] AS [CategoryID], [Extent1].[CategoryName] AS [CategoryName], [Extent1].[Description] AS [Description], [Extent1].[Picture] AS [Picture] FROM [dbo].[Categories] AS [Extent1] WHERE ((@p__linq__1 IS NULL) OR ([Extent1].[CategoryID] = @p__linq__2)) AND ((@p__linq__3 IS NULL) OR )((CAST(LEN(@p__linq__4) AS int)) = 0) OR ([Extent1].[CategoryName] = @p__linq__5))(
あちゃー。
違うやり方があるのかな?
追記
id:bleis-tiftさんのコメント通り、Whereをつなげるやり方でできました。
var q = ctx.Categories.AsQueryable(); if (categoryID != null) { q = q.Where(c => c.CategoryID == categoryID); } if (!string.IsNullOrEmpty(categoryName)) { q = q.Where(c => c.CategoryName == categoryName); }
こんな感じで、対象の列だけ条件に含められます。
このやり方だとLINQ to Entitiesの場合でもOKでした。