LoginSignup
7
7

More than 3 years have passed since last update.

@mink0212

C#のentityframworkでテーブル結合や様々な絞り込み(where)をする

Posted at

はじめに

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マッパとは異なる点だと思います。

7
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
7