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

はじめに

DBから情報取得や登録以外にもレコードを数える関数や取得レコードの数を指定するSQLがあります。そのようなSQLや若干特殊な操作をEntityFramworkで実行して、DBのログに表示されるSQLをまとめました。前回と前々回の続きです。

環境

• 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を実行してしまう可能性もあり、どこまで理解すれば良いのか悩んでしまいました。