はじめに

EntityFramworkは、LINQで絞り込みや結合など色々なSQLを指定することができますが、明示的にSQLを発行するタイミングが分かり難いことやLINQでオブジェクトの操作もできるためSQLを操作しているのかSQL実行後のModelクラスを操作しているのか分かり難いです。そのため、ここではDB側のログを出力してSQLが変更されていることを確認します。環境作成は前回を見てください。

環境

Windows:10
dotnet:3.0.100

Whereによる単純な比較

Whereはシンプルな絞り込み条件になります。LINQでも同じくWhereにより絞り込みを行います。
contexts.PetModels.Where(x=>x.Age==3);のようにModelクラスに対してWhere関数で絞り込みを行っています。Whereの中身はWhere(変数名=>変数名を使用した操作)のようにして指定します。

LinqEF.cs

using System;
using System.Linq;
using DBSample.EF;

namespace DBSample
{
    class LinqEF
    {
        public void WhereSample()
        {
            PetsContext contexts = new PetsContext();

            var PetModelList = contexts.PetModels.Where(x=>x.Age==3);

            foreach(var PetModel in PetModelList)
            {
                Console.WriteLine("id:{0}, name:{1}, age:{2}, birthday:{3}", PetModel.Id, PetModel.Name, PetModel.Age, PetModel.Birthday);
            }

            Console.WriteLine("Hello World!");
        }
    }
}

DB側のログ

2019-11-03 03:35:17.878 UTC [131] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.age = 3

Whereで指定したAge==3の条件でSQLのWhereが指定されていることがわかりました。
ちなみにWhere内をAge<3にすると以下のようにSQLも変わります。

DB側ログ

2019-11-03 03:53:53.208 UTC [169] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.age < 3

Whereによるちょっと複雑な比較

LINQによるノットイコール

C#とSQLは言語が異なるので比較演算子もそれぞれで異なりますが、その差分もLINQが吸収してくれます。例えば、C#で否定を表す演算子は!=ですが、SQLでは<>になります。

LinqEF.cs

using System;
using System.Linq;
using DBSample.EF;

namespace DBSample
{
    class LinqEF
    {
        public void WhereSample()
        {
            PetsContext contexts = new PetsContext();

            var PetModelList = contexts.PetModels.Where(x=>x.Age!=3);

            foreach(var PetModel in PetModelList)
            {
                Console.WriteLine("id:{0}, name:{1}, age:{2}, birthday:{3}", PetModel.Id, PetModel.Name, PetModel.Age, PetModel.Birthday);
            }

            Console.WriteLine("Hello World!");
        }
    }
}

DB側ログ

2019-11-03 03:55:54.944 UTC [174] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.age <> 3

LINQによるIN句

POSTGRESにはリストに一致する値のみ絞り込めるIN句というものがあります。C#にはそのようなものはないですが、LISTのContains関数を使用することでLINQがIN句に変換してくれます。

LinqEF.cs

using System;
using System.Linq;
using System.Collections.Generic;
using DBSample.EF;

namespace DBSample
{
    class LinqEF
    {
        public void WhereSample()
        {
            PetsContext contexts = new PetsContext();

            List<int> ageList = new List<int>() {2,3};

            var PetList = contexts.PetModels.Where(x=>ageList.Contains(x.Age));

            foreach(var PetModel in PetList)
            {
                Console.WriteLine("id:{0}, name:{1}, age:{2}, birthday:{3}", PetModel.Id, PetModel.Name, PetModel.Age, PetModel.Birthday);
            }

            Console.WriteLine("Hello World!");
        }
    }
}

DB側ログ

2019-11-03 03:58:17.831 UTC [180] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE p.age IN (2, 3)

LINQによるサブクエリ(副問い合わせ)

POSTGRESにはSQLの実行結果を使用して、絞り込み条件にするサブクエリ(副問い合わせ)という機能があります。C#ではLINQを重ねることで副問い合わせに対応しています。
下の例では、副問い合わせとしてcontexts.PetModels.Where(z=>z.Name=="tama").Select(y=>y.Age).First()のSQLを定義しています。このSQLはWhere関数で"tama"というNameのレコードを検索して、Select関数でAgeの値のみを取得しています。このままではリストが取れるためFirst関数で1つの数字を抜き出しています。
本問い合わせでは、var PetList = contexts.PetModels.Where(x => x.Age==のように副問い合わせの結果とAgeを比較して同じものを絞り込み条件として取得しています。
つまりは、副問い合わせSQLとしてtamaというNameのAgeを取得して、本問い合わせとして副問い合わせとAgeが同じレコードを取得するSQLになっています。

LinqEF.cs

using System;
using System.Linq;
using System.Collections.Generic;
using DBSample.EF;

namespace DBSample
{
    class LinqEF
    {
        public void WhereSample()
        {
            PetsContext contexts = new PetsContext();

            var PetList = contexts.PetModels.Where(
                x => x.Age == contexts.PetModels.Where(z=>z.Name=="tama").Select(y=>y.Age).First()
            );

            foreach(var PetModel in PetList)
            {
                Console.WriteLine("id:{0}, name:{1}, age:{2}, birthday:{3}", PetModel.Id, PetModel.Name, PetModel.Age, PetModel.Birthday);
            }

            Console.WriteLine("Hello World!");
        }
    }
}

DB側ログ

2019-11-03 04:29:08.839 UTC [246] LOG:  execute <unnamed>: SELECT p.id, p.age, p.birthday, p.name
        FROM pets AS p
        WHERE (p.age = (
            SELECT p0.age
            FROM pets AS p0
            WHERE (p0.name = 'tama') AND (p0.name IS NOT NULL)
            LIMIT 1)) AND ((
            SELECT p0.age
            FROM pets AS p0
            WHERE (p0.name = 'tama') AND (p0.name IS NOT NULL)
            LIMIT 1) IS NOT NULL)

テーブル結合

外部キーによる結合

外部キーを設定することで、他のテーブルのカラムをキーにしてデータ取得時に複数のテーブルを結合して1つのテーブルのように取得できる機能があります。C#での方法は、DBのテーブル定義に外部キーを追加してC#のModelクラスとLINQを修正することでできます。

Modelクラスの修正

Modelクラスでは親のテーブルのModelクラス変数に子のテーブルのModelクラスを追加して外部キーを設定するだけです。下の例でいうとOwnerModelが親なので子のPetModelクラスを変数に追加して、外部キーがpet_idカラムなのでその設定をForeignKeyで定義しています。

EFMod.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace LinqForeginJoin.EF
{
    [Table("pets")]
    public class PetModel
    {
        [Key]
        [Column("id")]
        public int PetId {set;get;}
        [Column("name")]
        public string PetName{set;get;}
        [Column("age")]
        public int PetAge{set;get;}
        [Column("birthday")]
        public DateTime PetBirthday{set;get;}
    }

    [Table("owner")]
    public class OwnerModel
    {
        [Key]
        [Column("id")]
        public int OwnerId {set;get;}
        [Column("name")]
        public string OwnerName{set;get;}
        [ForeignKey("pet_id")]    
        public PetModel PetModel { get; set; }
    }

    public class OwnerContext: DbContext
    {
        public DbSet<OwnerModel> OwnerModels { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql("Host=localhost;Port=5431;Username=postgres;Password=postgres;Database=animal_db");
        }
    } 
}

LINQの修正

LINQでは、Select()を使用して実際に扱うクラスを生成するだけでJoinをすることができます。このクラスは匿名クラスでも問題ないです。

LinqEF.cs

using System;
using System.Linq;
using LinqForeginJoin.EF;

namespace LinqForeginJoin
{
    class LinqEF
    {
        public void JoinSample()
        {
            OwnerContext contexts = new OwnerContext();

            var ownerList = contexts.OwnerModels.Select
            (
                x=>new OwnerModel 
                {
                    OwnerId=x.OwnerId, OwnerName=x.OwnerName, PetModel=x.PetModel
                }
            );

            foreach(var owner in ownerList)
            {
                Console.WriteLine("id:{0}, name:{1}, age:{2}", owner.OwnerId, owner.OwnerName, owner.PetModel.PetName);
            }
            Console.WriteLine("Hello World!");
        }
    }
}

結果

上のサンプルを実行するとちゃんとLEFT JOINができていることがわかります。

2019-11-03 10:57:25.760 UTC [1018] LOG:  execute <unnamed>: SELECT o.id AS "OwnerId", o.name AS "OwnerName", p.id, p.age, p.birthday, p.name
        FROM owner AS o
        LEFT JOIN pets AS p ON o.pet_id = p.id

LINQのJOINによる結合

LINQにもJOIN関数があり、C#のContextクラスとLINQを修正することでできます。

Contextクラスの修正

Contextクラスに親のテーブルのModelクラスと子のテーブルのModelクラスを追加するだけです。例では親がOwnerModels、子がPetModelsなのでその定義をContextクラスにしています。

EFMod.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace LinqJoin.EF
{
    [Table("pets")]
    public class PetModel
    {
        [Key]
        [Column("id")]
        public int Id {set;get;}
        [Column("name")]
        public string Name{set;get;}
        [Column("age")]
        public int Age{set;get;}
        [Column("birthday")]
        public DateTime Birthday{set;get;}
    }

    [Table("owner")]
    public class OwnerModel
    {
        [Key]
        [Column("id")]
        public int Id {set;get;}
        [Column("name")]
        public string Name{set;get;}
        [Column("pet_id")]
        public int PetId{set;get;}
    }

    public class OwnerContext: DbContext
    {
        public DbSet<OwnerModel> OwnerModels { get; set; }
        public DbSet<PetModel> PetModels { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql("Host=localhost;Port=5431;Username=postgres;Password=postgres;Database=animal2_db");
        }
    } 
}

LINQの修正

LINQでは、ContextクラスからそれぞれのModelクラスを抜き出して以下の形式でJoin()を使用します。

JOINの形式

var 変数 = 親のモデル.Join
(
    子のモデル, 親の結合用変数, 子の結合用変数,
    (親のモデル用一時変数, 子のモデル用一時変数) => new 
    {
        匿名クラス内の値設定
    }
);

LinqEF.cs

using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using LinqJoin.EF;

namespace LinqJoin
{
    class LinqEF
    {
        public void JoinSample()
        {
            OwnerContext ownerContexts = new OwnerContext();
            DbSet<OwnerModel> models = ownerContexts.OwnerModels;
            DbSet<PetModel> pets = ownerContexts.PetModels;
            
            var ownerList = models.Join
            (
                pets, x=>x.PetId, y=>y.Id,
                (owner, pet) => new 
                {
                    ownerId = owner.Id,
                    ownerName = owner.Name,
                    petName = pet.Name
                }
            );

            foreach(var owner in ownerList)
            {
                Console.WriteLine("id:{0}, name:{1}, petName:{2}", owner.ownerId, owner.ownerName, owner.petName);
            }
            Console.WriteLine("Hello World!");
        }
    }
}

結果

上のサンプルを実行するとちゃんとINNER JOINができていることがわかります。

2019-11-03 11:22:54.579 UTC [1111] LOG:  execute <unnamed>: SELECT o.id AS "ownerId", o.name AS "ownerName", p.name AS "petName"
        FROM owner AS o
        INNER JOIN pets AS p ON o.pet_id = p.id

おわりに

EntityFramworkで絞り込みや結合などDB操作で良く使う方法をまとめました。一部例外はありますが、大体のことはContextクラスとLINQだけで対応できるのでModelクラスは完全にテーブル用データクラスとして処理から切り離されているように感じました。そこがEntityFramworkが普通のORマッパとは異なる点だと思います。