CodeZine(コードジン)

特集ページ一覧

LINQにも色々 ~SQLに変換されるモノと変換されないモノ

Visual StudioでDB連携も簡単プログラミング-知っておきたいLINQメソッド式&ラムダ式 第4回

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2015/03/06 14:00
目次

SQLに変換されるモノ

 「すべてがSQLに変換可能でなければならない」というのは結構厳しい制限にも思えますが、どんなものであればSQLに変換されるのか、LINQと実際に変換されるSQLを確認していきましょう。

数値型

 まず、数値型について見ていきましょう(リスト3)。なお、以下ではLINQを書いた後すぐにSQLを発行するため、LINQの結果をList型に変換するToListメソッドを呼んでいます(=List型への変換の際に、データを取得するためにSQLが発行されます)。

リスト3 数値型に関するLINQとSQL
//比較演算子
products = context.Products.Where(x => x.Price == 300);
→ WHERE 300 = [Extent1].[Price]
products = context.Products.Where(x => x.Price != 300);
→ WHERE 300 <> [Extent1].[Price]
context.Products.Where(x => x.Price >= 300).ToList();
→ WHERE [Extent1].[Price] >= 300

//MathクラスのAbsメソッド(絶対値)
context.Products.Where(x => Math.Abs(x.Price) > 0).ToList();
→ WHERE (ABS([Extent1].[Price])) > 0

//MathクラスのCeilingメソッド(小数→整数変換)
context.Products.Where(x => x.Price >= Math.Ceiling(299.4)).ToList();
→ WHERE CAST([Extent1].[Price] AS float)>=(CEILING(cast(299.4 as float(53))))

//MathクラスのPowメソッド(累乗)
context.Products.Where(x => x.Price > Math.Pow(10.0, 2.0)).ToList();
→ WHERE CAST( [Extent1].[Price] AS float) >
   (POWER(cast(10 as float(53)), cast(2 as float(53))))

 ==, !=, <, >, <=, >=などの比較演算子は、それぞれ対応するSQLに変換されます。また、MathクラスのCeiling, Floor, Round, Truncateなどの小数点丸め関数や、Abs(絶対値関数)、Pow(累乗関数)などは、SQLの対応する関数に変換されます。

文字列型

 続いて文字列型です(リスト4)。

リスト4 文字列型に関するLINQとSQL
//比較演算子
context.Products.Where(x => x.Name == "ショートケーキ").ToList();
→ WHERE N'ショートケーキ' = [Extent1].[Name]

context.Products.Where(x => x.Name != "ショートケーキ").ToList();
→ WHERE NOT ((N'ショートケーキ' = [Extent1].[Name])
    AND ([Extent1].[Name] IS NOT NULL))

//文字列長
context.Products.Where(x => x.Name.Length == 6).ToList();
→ WHERE 6 = ( CAST(LEN([Extent1].[Name]) AS int))

//文字列のNULL&空白チェック
context.Products.Where(x => string.IsNullOrEmpty(x.Name)).ToList();
→ WHERE ([Extent1].[Name] IS NULL) OR ((CAST(LEN([Extent1].[Name]) AS int)) = 0)

//文字列のトリム(LTrim, RTrimも同様に変換対応)
context.Products.Where(x => x.Name.Trim() == "ケーキ"))).ToList();
→ WHERE N'ケーキ' = (LTRIM(RTRIM([Extent1].[Name])))

//文字列前方一致
context.Products.Where(x => x.Name.StartsWith("ショート")).ToList();
→ WHERE [Extent1].[Name] LIKE N'ショート%'

//文字列後方一致
context.Products.Where(x => x.Name.EndsWith("ケーキ")).ToList();
→ WHERE [Extent1].[Name] LIKE N'%ケーキ'

//文字列部分一致
context.Products.Where(x => x.Name.Contains("ケーキ")).ToList();
→ WHERE [Extent1].[Name] LIKE N'%ケーキ%'

//文字列検索(部分文字列が出現する位置)
context.Products.Where(x => x.Name.IndexOf("ケーキ") > 2).ToList();
→ WHERE (( CAST(CHARINDEX(N'ケーキ', [Extent1].[Name]) AS int)) - 1) > 2

 上記の通り、==, !=の比較演算子や、string型の各種プロパティ、メソッドがSQL変換に対応しています。中でも文字列検索に関連したStartsWidth、EndsWith、Contains、IndexOfメソッドは、実アプリケーションでも多用される機能と思われますが、きちんとSQLのLIKE演算子やCHARINDEX関数に変換されることが確認できます。

日時型

 続いて日時型です(リスト5)。

リスト5 日時型に関するLINQとSQL
//昨日の日付
var yesterday = DateTime.Now - TimeSpan.FromDays(1);
//昨日の日付と比較
context.Products.Where(x => x.Employee.Birthday > yesterday).ToList();
→ WHERE [Extent2].[Birthday] > @p__linq__0
→「@p__linq__0」の値は'2014/10/30 23:24:17'

//日時型の月
context.Products.Where(x => x.Employee.Birthday.Month <= 3).ToList();
→ WHERE (DATEPART (month, [Extent2].[Birthday])) <= 3

//現在日時と比較
context.Products.Where(x => x.Employee.Birthday > DateTime.Now).ToList();
→ WHERE [Extent2].[Birthday] > (SysDateTime())

//現在日時(Utc)と比較
context.Products.Where(x => x.Employee.Birthday > DateTime.UtcNow).ToList();
→ WHERE [Extent2].[Birthday] > (SysUtcDateTime())

 C#のDateTime型との比較はもちろん、SQLのDATEPART関数を使った日時型の特定のフィールドの取り出しにも対応しています。

 続いて、SQLのIN演算子に変換されるパターンです(リスト6)。

リスト6 IN演算子に変換されるパターン
//金額リスト
var list = new List<int>() { 100, 200, 300, 400, 500 };
context.Products.Where(x => list.Contains(x.Price)).ToList();
→ WHERE [Extent1].[Price] IN (100, 200, 300, 400, 500)

//名前リスト(string配列)
var nameList = new string[] { "ショートケーキ", "バームクーヘン" };
context.Products.Where(x => nameList.Contains(x.Employee.Name)).ToList();
→ WHERE ([Extent1].[Name] IN (N'ショートケーキ', N'バームクーヘン'))
     AND ([Extent1].[Name] IS NOT NULL)

 List型や配列のContainsメソッド(Listや配列に指定した値が含まれているかどうか)を使用すると、IN演算子を使用したSQLに変換されます。SQLのIN演算子は、対象の値を任意個数指定できるため、SQLを文字列として扱う場合にはやや面倒な演算子ですが、LINQから変換する場合には、スマートに書くことができます。

列挙型(Enum)

 さらに、Entity Frameworkは列挙型(Enum)にも対応していますので、列挙型を用いたLINQもSQLに変換できます(リスト7)。

リスト7 列挙型を使用するパターン
//商品ステータスを表す列挙型
public enum StatusEnum
{
  販売中,
  販売終了,
  検討中
}
//エンティティクラス
public class Product
{
・・・
  //商品ステータスを列挙型として定義
  public StatusEnum Status { get; set; }
・・・
}

//列挙型を使用して検索
context.Products.Where(x => x.Status == StatusEnum.販売終了).ToList();
→ WHERE 1 =  CAST( [Extent1].[Status] AS int)

//複数の列挙型で検索
var statusList = new List<StatusEnum>() { StatusEnum.販売終了, StatusEnum.検討中 };
context.Products.Where(x => statusList.Contains(x.Status)).ToList();
→ WHERE [Extent1].[Status] IN (1, 2)

 ここでは商品ステータスを表すStatusEnumという列挙型を定義し、ProductクラスのStatusフィールドで使用しています。Entity Frameworkでは、列挙型のフィールドをデータベース上のint型フィールドとして扱います。そのため、列挙型の値との比較や、複数の列挙型との比較も、SQL上では通常のint型の比較のように扱われます。

 何かのオブジェクトのステータスを、データベース上では整数型で扱うことは多いことでしょう。Entity Frameworkを使えば、ソースコード上では列挙型として扱うことができるため、可読性が高くなりますし、コーディングミスを減らすこともできます。

 ここまで、数値型、文字列型、日時型、列挙型などについて、SQLに変換可能な主な機能を解説しましたが、SQLに変換される機能をさらに詳細に知りたい場合は、MSDNの「CLR メソッドと正規関数とのマッピング」を参照してください。

まとめ

 本記事では、LINQ to ObjectsとLINQ to Entitiesの違いを見ながら、LINQ to Entitiesで正常に動作しないLINQがあることを解説しました。その一方でどの程度の機能がSQLに変換されるのか、も解説しました。幾らかの制限事項はあるものの、大抵のクエリはサポートされている範囲の機能で網羅できることでしょう。

 本連載では、Entity FrameworkとLINQメソッド式を使用した、快適なデータベースプログラミング手法について解説しました。Entity Frameworkは.NET Framework上で使用できる汎用的なデータアクセスフレームワークですので、LINQを使ったデータベースプログラミングに慣れておけば、ASP.NETやASP.NET MVC、Windowsデスクトップアプリ、はたまたクラウドサービスであるAzure上のWebサービスなど、様々な分野に応用可能です。

 LINQは単なる言語パズル的な機能では無く、データベースプログラミングにおいて生産性を向上させる有用な道具です。Entity Framework&LINQの活用において、本連載がお役に立てますように。

参照



  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:Visual StudioでDB連携も簡単プログラミング ~知っておきたいLINQメソッド式&ラムダ式

著者プロフィール

  • WINGSプロジェクト 土井 毅(ドイ ツヨシ)

    <WINGSプロジェクトについて> 有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛)。主にWeb開発分野の書籍/記事執筆、翻訳、講演等を幅広く手がける。2018年11月時点での登録メンバは55名で、現在も執筆メンバを募集中。興味のある方は、どしどし応募頂...

  • 山田 祥寛(ヤマダ ヨシヒロ)

    静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for ASP/ASP.NET。執筆コミュニティ「WINGSプロジェクト」代表。 主な著書に「入門シリーズ(サーバサイドAjax/XM...

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5