読者です 読者をやめる 読者になる 読者になる

ぴよぴよエンジニアの日記

ぴよぴよエンジニアの日記です 技術系のことや日常のことをつぶやきます

LINQを使ってSQLの基本的なことをやってみる

C# LINQ

LINQ とは、 Language Integrated Query の略称で、 C#VB などの .NET Framework 対応言語に、 リレーショナルデータや XML に対するデータ操作構文を組み込む (+ データベースや XML 操作用のライブラリ) というものです。
LINQ - C# によるプログラミング入門 | ++C++; // 未確認飛行 C

LINQの詳しい説明は上記リンクをご参照ください.

今回は、SQLの基本的なことをLINQを使ってやってみたいと思います.
ちなみにLINQ to SQLではなく、LINQ to Objectです.


データセット

namespace LINQSample
{
    public enum Sex { Male, Female }
    public enum BloodType { A, B, O, AB }
    public enum Cup { AA, A, B, C, D, E, F}

    class Person
    {
        public String Name { get; set; }
        public Sex Sex { get; set; }
        public int? Age { get; set; }
        public BloodType BloodType { get; set; }
        public float Height { get; set; }
        public float Weight { get; set; }
        public float BMI { get; set; }
        public float? Bust { get; set; }
        public float? Waist { get; set; }
        public float? Hip { get; set; }
        public Cup Cup { get; set; }
        public DateTime BirthDay { get; set; }
        public String Hometown { get; set; }
        public String CharacterVoice { get; set; }
        public int? VoiceActorAge { get; set; }
        public String Agency { get; set; }
    }
}

データセット用にPersonクラスを定義しました.

public static List<Person> CreateIdolList()
{
    var idlos = new List<Person>();

    idlos.Add(new Person
    {
        Name = "天海春香",
        Age = 17,
        Sex = Sex.Female,
        BloodType = BloodType.O,
        Height = 158,
        Weight = 46,
        BMI = 18.43f,
        Bust = 83,
        Waist = 56,
        Hip = 82,
        Cup = Cup.D,
        BirthDay = new DateTime(2016, 4, 3),
        Hometown = "神奈川県",
        CharacterVoice = "中村繪里子",
        VoiceActorAge = 34,
        Agency = "765"
   });

   …
}

データセット生成メソッドです.
今回は765プロのみなさんと346プロのみなさんにご協力頂きます.

public static void WriteList(IEnumerable<Object> list)
{
    foreach (Object element in list)
    {
        Debug.WriteLine(element);
    }
}

表示用メソッドです.


SELECT

var idols = Person.CreateIdolList();
var names = idols.Select(v => v.Name);
WriteList(names);
天海春香
如月千早
…
三村かな子
諸星きらり

最も基本的なSELECT句です.Nameカラムのみを抽出します.

var idols = Person.CreateIdolList();
var nameAndAgeList = idols.Select(v => new {v.Name, v.Age});
WriteList(nameAndAgeList);
{ Name = 天海春香, Age = 17 }
{ Name = 如月千早, Age = 16 }
…
{ Name = 三村かな子, Age = 17 }
{ Name = 諸星きらり, Age = 17 }

複数のカラムを出力するSELECT句です.


WHERE

var idols = Person.CreateIdolList();
var tokyos = idols
    .Where(v => v.Hometown == "東京都")
    .Select(v => v.Name);
WriteList(tokyos);
高槻やよい
音無小鳥
島村卯月
渋谷凛
赤城みりあ
多田李衣菜

出身地が東京都のアイドルを抽出するWHERE句です.
(765プロ所属のアイドルの出身地が分からなかったので、中の人の出身地にしています)

var idols = Person.CreateIdolList();
var notTeenagers = idols
    .Where(v => v.Age < 10 || v.Age >= 20)
    .Select(v => v.Name);
WriteList(notTeenagers);
三浦あずさ

10代"でない"(10歳未満または20歳以上)アイドルを抽出するWHERE句です.複数のWHERE条件は &&, || で結ぶことができます.


SUM

var idols = Person.CreateIdolList();
var sumWeight = idols.Sum(v => v.Weight);
Debug.WriteLine(sumWeight);
1215

アイドルの体重の合計を集計するSUMです.返り値はfloatになります.


AVG

var idols = Person.CreateIdolList();
var avgAge = idols.Average(v => v.Age);
Debug.WriteLine(avgAge);
15.8

アイドルの平均年齢を集計するAVGです.返り値はfloatになります.
高校1年生が一番多いようです.


COUNT

var idols = Person.CreateIdolList();
var count765Idos = idols.Count(v => v.Agency == "765");
Debug.WriteLine(count765Idos);
14

765プロ所属のアイドルの総数を集計するCOUNTです.返り値はintになります.
引数にWHERE句のように条件式を書くことができます.
(結果には律っちゃんと音無さんが含まれています)


MAX

var idols = Person.CreateIdolList();
var heighest = idols.Max(v => v.Height);
Debug.WriteLine(heighest);
182

最も背が高いアイドルの身長を集計するMAXです.返り値はfloatになります.
(一番背が高いのは当然きららですね)


MIN

var idols = Person.CreateIdolList();
var shortest = idols.Min(v => v.Height);
Debug.WriteLine(shortest);
139

最も背が低いアイドルの身長を集計するMINです.返り値はfloatになります.
(一番背が低いのは杏ですね)


DISTINCT

var idols = Person.CreateIdolList();
var hometowns = idols
    .Select(v => v.Hometown)
    .Distinct();
WriteList(hometowns);
神奈川県
山口県
新潟県
広島県
東京都
大阪府
鳥取県
千葉県
北海道
三重県
熊本県
埼玉県

出身地の一覧を重複なしで抽出するDISTINCT修飾子です.


ORDER BY

var idols = Person.CreateIdolList();

var idolsWeightOrder = idols.OrderByDescending(v => v.Weight);

foreach (Person idol in idolsWeightOrder)
{
    Debug.WriteLine("Name: {0}, Weight: {1}", idol.Name, idol.Weight);
}
Name: 諸星きらり, Weight: 60
Name: 三村かな子, Weight: 52
Name: 四条貴音, Weight: 49
Name: 音無小鳥, Weight: 49

体重の大きい順に降順でソートするORDER BY句です.
昇順ソートが#OrederBy で、降順ソートが#OrderByDescendingとなります.


GROUP BY

var idols = Person.CreateIdolList();

var agencyMembers = idols
    .GroupBy(v => v.Agency);
            
foreach (IGrouping<String, Person> group in agencyMembers)
{
    Debug.WriteLine("Agency: {0}, AvgVoiceActorAge: {1:f1}", 
        group.Key, 
        group.Average(v => v.VoiceActorAge));
}
Agency: 765, AvgVoiceActorAge: 33.6
Agency: 346, AvgVoiceActorAge: 25.6

所属事務所ごとの声優の平均年齢を集計するGROUP BY句です.
#GroupByの結果はIGroupingになります.この場合、集計に用いたのは String のため、IGrouping<String, Person>となります.
(やはり765と346の平均年齢には開きがありますね...)

var idols = Person.CreateIdolList();

var agencyMembers = idols
    .GroupBy(v => v.Age);
            
foreach (IGrouping<int?, Person> group in agencyMembers)
{
    Debug.WriteLine("Age: {0}, Number: {1}", group.Key, group.Count());
}
Age: 17, Number: 8
Age: 16, Number: 3
Age: 15, Number: 6
Age: 14, Number: 2
Age: 21, Number: 1
Age: 13, Number: 2
Age: 18, Number: 1
Age: 19, Number: 2
Age: , Number: 1
Age: 11, Number: 1
Age: 12, Number: 1

別の例です.年齢ごとのアイドル数を集計するGROUP BY句です.
この場合、集計に用いたのは int? のため、IGrouping<int?, Person>となります.
(空欄のところはnullです.音無さんは年齢が分かりませんでした.)


複雑なクエリ


サブクエリを用いたクエリ

SELECT *
  FROM idols
 WHERE height = (
	SELECT MIN(height)
	FROM idols
)

サブクエリを用いたクエリです.
最小値に該当するレコードを抽出しようとするとサブクエリを書かないといけません.
これをLINQで書くと以下のようになります.

var idols = Person.CreateIdolList();

var heighest = idols
    .Where(v => v.Height == idols.Max(max => max.Height))
    .ElementAt(0);

Debug.WriteLine("Name: {0}, Height: {1}", heighest.Name, heighest.Height);
Name: 諸星きらり, Height: 182

WHERE句でサブクエリ的なものを書いています.
#Whereの結果はIEnumerable<Person>なので先頭の要素をElementAt(0)で取ってきています.



GROUP BYを用いたサブクエリのクエリ

WITH avgBmiEachAgency AS(

    SELECT agency, AVG(bmi) AS bmi
      FROM idols
    GROUP BY agency
)

SELECT idols.name,
       idols.agency,
       idols.bmi,
       avgBmiEachAgency.bmi

  FROM idols INNER JOIN avgBmiEachAgency
  ON idlos.agency = avgBmiEachAgency.agency

WHERE idols.bmi < avgBmiEachAgency.bmi

所属事務所ごとの平均BMIよりも小さいBMIのアイドルを抽出するクエリです.
これをLINQで書くと以下のようになります.

var idols = Person.CreateIdolList();

var avgBMI = idols.GroupBy(member => member.Agency)
    .Select(group => new
    {
        AvgBMI = group.Average(member => member.BMI),
        Agency = group.Key
    });

var idolsUnderAvgBMI = idols.Join(avgBMI, members => members.Agency, group => group.Agency, (members, group) => new
{
    Name = members.Name,
    Agency = members.Agency,
    BMI = members.BMI,
    AvgBMI = group.AvgBMI
}).Where(v => v.BMI < v.AvgBMI);

foreach (var idol in idolsUnderAvgBMI)
{
    Debug.WriteLine("Name: {0}, Agency: {1}, BMI: {2:f2}, AvgBMI: {3:f2}",
        idol.Name.PadRight(6, ' '),
        idol.Agency,
        idol.BMI,
        idol.AvgBMI);
}
Name: 如月千早  , Agency: 765, BMI: 15.62, AvgBMI: 17.27
Name: 三浦あずさ , Agency: 765, BMI: 17.01, AvgBMI: 17.27
Name: 水瀬伊織  , Agency: 765, BMI: 17.09, AvgBMI: 17.27
Name: 双海亜美  , Agency: 765, BMI: 16.82, AvgBMI: 17.27
Name: 双海真美  , Agency: 765, BMI: 16.82, AvgBMI: 17.27
Name: 四条貴音  , Agency: 765, BMI: 17.16, AvgBMI: 17.27
Name: 渋谷凛   , Agency: 346, BMI: 16.20, AvgBMI: 17.68
Name: アナスタシア, Agency: 346, BMI: 15.79, AvgBMI: 17.68
Name: 神崎蘭子  , Agency: 346, BMI: 16.80, AvgBMI: 17.68
Name: 城ヶ崎莉嘉 , Agency: 346, BMI: 16.20, AvgBMI: 17.68
Name: 双葉杏   , Agency: 346, BMI: 15.50, AvgBMI: 17.68

SQLのWITH句がavgBMIに対応し、SQLの本体がidolsUnderAvgBMIに対応しています.
LINQのJoinメソッドSQLのFROM句とSELECT句を統合した意味になっていますね.
結果を見ると346よりも765のほうが平均BMIは低いようです.特に痩せているのは杏なのですね.
(346の平均BMIを押し上げているのは誰かは明言しないでおこう...)


まとめ

今回はLINQを使ってSQLの基本的なことをやってみました.
LINQはデータ集計をSQL的な書き方で行えるので大変便利だと思います.
この他にもLINQには様々なメソッドが用意されていますので、もっと色々なことが可能です.(勉強中)
しかしながら、あまり複雑なことをしてしまうとコードの可読性が著しく下がりますし、件数が多くなると処理速度も問題となります.
なので、ある程度の集約は当然のことですがデータベースサイドで行うべきだと思います.

感想

フィールドたくさん用意してもあまり使っていないことに気付きましたorz
今後、例を増やすかもしれません.

以上です.