C#のentityframworkで特殊なSQLをためす

# はじめに
DBから情報取得や登録以外にもレコードを数える関数や取得レコードの数を指定するSQLがあります。そのようなSQLや若干特殊な操作をEntityFramworkで実行して、DBのログに表示されるSQLをまとめました。[前回](https://qiita.com/mink0212/items/fcf4cb16991e11cd2a98)と[前々回](https://qiita.com/mink0212/items/d3274e070d79071480ab)の続きです。


# 環境
 - Windows:10
 - dotnet:3.0.100

# レコード数を数えるCount
Selectで使用するCountはレコードを数える関数で、SQLとEntityFramwork両方にあります。大抵は、Where句を使用してある条件のレコード数を調べるときに使います。

## シンプルなCount
SQLで使用するように、Where句で条件を指定してCount関数でレコード数を取得してみます。

### EntityFramworkでの表現
絞り込み関数(Where)でageが4のレコードを絞り込み、Count関数で絞り込み後のレコード数を数えています。

``` LinqEF.cs
using System;
using System.Linq;
using LinqStandard.EF;

namespace DBSample
{
    class LinqEF
    {
        public void WhereCountSample()
        {
            PetsContext contexts = new PetsContext();
            var PetCount = contexts.PetModels.Where(x => x.Age == 4).Count();

            Console.WriteLine("pet count:{0}", PetCount);
        }
    }
}
```

### SQLでの表現(DB側のログ)
Count文としては、一部を除いて特殊なものはなくWhereで条件を絞り込んでCountで数えています。
ただし、Countの対象カラムは特に指定せず全カラムを指定しており、Int型で返ってくるように指定しているようです。
Int型の指定をしたくないときはLongCount関数を使用します。

```
2019-11-16 08:16:56.625 UTC [33] LOG:  execute <unnamed>: SELECT COUNT(*)::INT
        FROM pets AS p
        WHERE p.age = 4
```

## 絞り込みありのCount
EntityFramworkのCountには、引数に与えることでWhere関数のように絞り込み条件を指定することができます。
その時のSQLは変わるのかを見てみます。

### EntityFramworkでの表現
Count関数でageが4のレコードを絞り込みと絞り込み後のレコード数を数えています。
関数部のみ記載しています。usingは一番上の例を見てください。

``` LinqEF.cs
        public void　CountTargetSample()
        {
            PetsContext contexts = new PetsContext();

            var PetCount = contexts.PetModels.Count(x => x.Age == 4);

            Console.WriteLine("pet count:{0}", PetCount);
        }

``` 

### SQLでの表現(DB側のログ)
SQLとしては、絞り込みにWhere関数を使用した場合もCount関数を使用した場合も同じSQLが発行されていました。

```
2019-11-16 08:22:36.082 UTC [46] LOG:  execute <unnamed>: SELECT COUNT(*)::INT
        FROM pets AS p
        WHERE p.age = 4
```

# selectによる取得値の違い
Selectは取得したいカラムを指定することができる関数で、SQLとEntityFramwork両方にあります。大抵は、取得したいカラムを指定して取得する値を制限します。

## 取得カラム指定のselect
Select関数を指定して取得するカラムを制限しています。これによって、シンプルなリストが返却されるので処理が簡単になります。

### EntityFramworkでの表現
Select関数でageカラムを指定してAgeの値を格納したリストを取得しています。その後、ループで全部表示しています。

``` LinqEF.cs
        public void SelectSample()
        {
            PetsContext contexts = new PetsContext();

            var AgeList = contexts.PetModels.Select(x=>x.Age);

            foreach(var item in AgeList)
            {
                Console.WriteLine("item:{0}", item);
            }
        }
``` 

### SQLでの表現(DB側のログ)
SQLもageのみSelect文で取得するようになっていました。

```
2019-11-16 08:38:10.814 UTC [76] LOG:  execute <unnamed>: SELECT p.age
        FROM pets AS p
```


## 全カラム指定のselect
特に意味はないかもしれませんが、全カラムを取得するときは何も指定せずに使用します。

### EntityFramworkでの表現
Select関数でPetModelを指定して値を格納したリストを取得しています。その後、ループで全部表示しています。

``` LinqEF.cs
        public void SelectAllSample()
        {
            PetsContext contexts = new PetsContext();

            var PetList = contexts.PetModels.Select(x=>x);

            foreach(var item in PetList)
            {
                Console.WriteLine("item:{0}", item);
            }
        }
``` 

### SQLでの表現(DB側のログ)
SQLは*での全指定ではなく、全カラムの指定をして取得するようになっていました。

```
2019-11-16 09:45:37.735 UTC [149] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
```


## レコード条件指定のselect
Select関数内に式を入れることにより、その式に一致しているレコードかのリストを取得できます。

### EntityFramworkでの表現
Select関数でageが3より大きいという式を指定してTrue/Falseを格納したリストを取得しています。ageが3より大きいレコードに対してはTrue、以下のレコードに対してはFalseが返却されます。

``` LinqEF.cs
        public void SelectBool()
        {
            PetsContext contexts = new PetsContext();

            var PetList = contexts.PetModels.Select(x=>x.Age>3);

            foreach(var item in PetList)
            {
                Console.WriteLine("item:{0}", item);
            }
        }
``` 

### SQLでの表現(DB側のログ)
SQLでも同じようにage>3という式をselectで指定をして取得するようになっていました。

```
2019-11-16 09:48:21.145 UTC [153] LOG:  execute <unnamed>: SELECT p.age > 3
        FROM pets AS p
```


# レコード条件確認
AllやAnyなどレコードが条件に一致するかを判断する関数がEntityFramworkにあります。大抵は、Whereで条件を絞って関数でそのレコードがあるかをチェックします。

## Allによるレコード条件
Allは全レコードが全て条件に一致するかを判断する関数です。

### EntityFramworkでの表現
All関数でageカラムが2より大きいレコードを指定して、成否の値を取得しています。すべてのレコードのageが2より大きい場合True、一つでも小さいレコードがある場合Falseが返却されます。

``` LinqEF.cs
        public void AllSample()
        {
            PetsContext contexts = new PetsContext();

            var result = contexts.PetModels.All(x=>x.Age > 2);
            Console.WriteLine("result:{0}", result);
        }
``` 

### SQLでの表現(DB側のログ)
SQLは大きくことなります。SQLとしては、まずWhere句を指定したSelect文でレコードを1つ取得するSQLとEXITST関数で存在有無を確認するSQLの2つを使用しています。内容としては条件を指定して1つでも取得できるレコードがあればFalseが返却されます。取得できなければTrueが返却されます。EntityFramworkで逆にして返却されるようです。

```
2019-11-16 09:56:35.026 UTC [176] LOG:  execute <unnamed>: SELECT NOT EXISTS (
            SELECT 1
            FROM pets AS p
            WHERE p.age <= 2)
```

## Anyによるレコード条件
Anyは一部のレコードが条件に一致するかを判断する関数です。

### EntityFramworkでの表現
Any関数でageカラムが2より大きいレコードを指定して、成否の値を取得しています。一部のレコードのageが2より大きい場合True、全て小さいレコードの場合Falseが返却されます。

``` LinqEF.cs
        public void AnySample()
        {
            PetsContext contexts = new PetsContext();

            var result = contexts.PetModels.Any(x=>x.Age > 2);
            Console.WriteLine("result any:{0}", result);
        }
``` 

### SQLでの表現(DB側のログ)
SQLは大きくことなります。SQLとしては、Allと似通っていますが、NOT EXISTSがEXISTSに変わっているだけになります。内容としては条件を指定して1つでも取得できるレコードがあればTrueが返却されます。取得できなければFalseが返却されます。Allと異なりそのまま返却されるようです。

```
2019-11-16 10:01:50.327 UTC [205] LOG:  execute <unnamed>: SELECT EXISTS (
            SELECT 1
            FROM pets AS p
            WHERE p.age > 2)
```

# 取得レコードの指定
SingleやFirst、Takeなど取得するレコード数を指定する関数がEntityFramworkにあります。

## Singleによる1レコード取得
Singleは1レコードだけ取得する関数です。Countと同じく関数内に絞り込み条件を指定することができSQL的には変わりませんでした。

### EntityFramworkでの表現
Single関数は1レコードの情報しか指定できないため、Where句で一意になるように絞り込み取得しています。返却される値はデータクラスになります。

``` LinqEF.cs
        public void WhereSingle()
        {
            PetsContext contexts = new PetsContext();

            // var result = contexts.PetModels.Single(y=>y.Id==1);
            var result = contexts.PetModels.Where(x=>x.Id==1).Single();

            Console.WriteLine("result name:{0}", result.Name);
        }
``` 

### SQLでの表現(DB側のログ)
SQLは大きくことなります。ちょっと分かり難いですが、SingleとしてはLIMIT句でレコード数を指定して全カラムを取得しています。なぜLIMITが2なのか分かりません。

```
2019-11-16 10:11:57.525 UTC [267] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.id = 1
        LIMIT 2
```

## Firstによる1レコード取得
Firstは最初の1レコードだけ取得する関数です。Countと同じく関数内に絞り込み条件を指定することができSQL的には変わりませんでした。

### EntityFramworkでの表現
First関数もSingle関数と同じで1レコードの情報しか指定できないため、Where句で一意になるように絞り込み取得しています。返却される値はデータクラスになります。

``` LinqEF.cs
        public void WhereFirst()
        {
            PetsContext contexts = new PetsContext();

            // var result = contexts.PetModels.First(y=>y.Id==1);
            var result = contexts.PetModels.Where(x=>x.Id==1).First();

            Console.WriteLine("result name:{0}", result.Name);
        }
``` 

### SQLでの表現(DB側のログ)
SQLは大きくことなります。Singleと同じでLIMIT句でレコード数を指定して全カラムを取得しています。

```
2019-11-16 10:24:35.315 UTC [280] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.id = 1
        LIMIT 1
```

## takeによる複数レコード取得
takeは指定したレコードだけ取得する関数です。Countと同じく関数内に絞り込み条件を指定することができSQL的には変わりませんでした。

### EntityFramworkでの表現
take関数は複数レコードの情報を取得できるので5レコード分取得しています。また、Where句でageが3より大きくなるように絞り込み取得しています。返却される値はデータクラスのリストになります。


``` LinqEF.cs
        public void WhereTake()
        {
            PetsContext contexts = new PetsContext();

            var resultList = contexts.PetModels.Where(x=>x.Age > 3).Take(5);
            
            foreach(var item in resultList)
            {
                Console.WriteLine("item name:{0}", item.Name);
            }
        }
``` 

### SQLでの表現(DB側のログ)
SQLは大きくことなります。Singleと同じでLIMIT句でレコード数を指定して全カラムを取得しています。

```
2019-11-16 10:42:01.769 UTC [390] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.age > 3
        LIMIT $1
2019-11-16 10:42:01.769 UTC [390] DETAIL:  parameters: $1 = '5'
```


# レコード順操作
レコード順操作にはOrderByやSkipなどがあります。OrderByではSQLにもあるので特にまとめは必要ないかもしれませんがSkipはSQLにないのでまとめておきます。

## skipによるレコード操作
Skipはレコードを指定したレコード数を省略して取得します。

### EntityFramworkでの表現
Skip関数を指定して1レコード分スキップして値を取得しています。返却される値はデータクラスのリストになります。

``` LinqEF.cs
        public void WhereSkip()
        {
            PetsContext contexts = new PetsContext();

            var resultList = contexts.PetModels.Where(x=>x.Age > 3).Skip(1);
            
            foreach(var item in resultList)
            {
                Console.WriteLine("item:{0}", item);
            }            
        }
``` 

### SQLでの表現(DB側のログ)
SQLは大きくことなります。OFFSET句でスキップレコードを指定しています。

```
2019-11-16 10:45:36.619 UTC [420] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.age > 3
        ORDER BY (SELECT 1)
        OFFSET $1
2019-11-16 10:45:36.619 UTC [420] DETAIL:  parameters: $1 = '1'
```

# おわりに
EntityFramworkで色々SQLを見てきましたが、如何に面倒な作業を肩代わりしてくれているかがわかりました。
一方で使い方を正しく理解できないと思わぬSQLを実行してしまう可能性もあり、どこまで理解すれば良いのか悩んでしまいました。