戻る

C# EntityFrameworkのCodeFirstでCRUD（Read）

Published:  at  06:45

概要

CRUDのReadをやってみる。

即時実行、遅延実行などLINQならではの醍醐味がありますが、LINQに関しては詳しく解説しないのでご注意ください。
また本記事のサンプルコードはToList()プロパティを使用して即時実行しております。

目次

ソース

https://github.com/Lycheejam/EntityFramework_Sample/tree/master/EntityFramework_Sample/DataStore 🔗

DataStoreの作成

今回は命名規約としてDBアクセスを行うクラスを〇〇DataStoreとします。
プロジェクト直下にDataStoreフォルダを作成します。
今回はReadなのでReadDataStoreを作成しています。

前提

前回の記事で作成したDbContextを使用してDBに接続しますが、確実にDisposeするためにUsingを使用しています。

using (var db = new ShipsDbContext()) {
	//処理
}

単一Entityから全データを取得する。

今回は護衛隊群クラスを対象にデータの読み取りを行っていきます。

/// <summary>
/// 護衛隊群全読み込み
/// </summary>
/// <returns>List<護衛隊群></returns>
public List<EscortFlotilla> ReadFlotilla() {
    using (var db = new ShipsDbContext()) {
        var fd = db.EscortFlotillas.ToList();
        return fd;
    }
}

今回はLINQ to Entitiesの.ToList()プロパティを使用してリスト型でデータを取得しています。
護衛隊群クラスをList<T>で返します。

期待する結果

SELECT EscortFlotillaId, EscortFlotillaName FROM EscortFlotillas

実行結果

//Read
var readData = new ReadDataStore();
var fd = readData.ReadFlotilla();
foreach (var f in fd) {
    Console.WriteLine(f.EscortFlotillaName);
}

データをちゃんと取得出来ています。

SELECT
    [Extent1].[EscortFlotillaId] AS [EscortFlotillaId],
    [Extent1].[EscortFlotillaName] AS [EscortFlotillaName]
    FROM [dbo].[EscortFlotillas] AS [Extent1]

複数Entityからデータを読み込む

全護衛隊群と指揮下（隷下）の護衛隊を読み込みます。

/// <summary>
/// 護衛隊群と隷下の護衛隊全読み込み
/// </summary>
/// <returns>List<護衛隊群<護衛隊>></returns>
public List<EscortFlotilla> ReadFlotillaAll() {
    using (var db = new ShipsDbContext()) {
        var fd = db.EscortFlotillas.Include("EscortDivision")
                                   .ToList();
        return fd;
    }
}

先程と同様に護衛隊群クラスをList<T>型で返します。
更に護衛隊群クラスの中で護衛隊クラスは関連エンティティとしてvirtual句で定義されています。
.Include()プロパティを使用することで関連エンティティを読み込むことが出来ます。
また1対Nの関係なので護衛隊クラスはICollection型として定義しています。

期待する結果

SELECT EF.EscortFlotillaName, ED.EscortDivisionName
FROM EscortFlotillas AS EF
  LEFT JOIN EscortDivisions AS ED
    ON ED.EscortFlotilla_EscortFlotillaId = EF.EscortFlotillaId

実行結果

//護衛隊群+隷下の護衛隊全読み込み
var fd = readData.ReadFlotillaAll();
foreach (var f in fd) {
    Console.WriteLine(f.EscortFlotillaName);
    foreach (var divi in f.EscortDivision) {
        Console.WriteLine(" - " + divi.EscortDivisionName);
    }
}

護衛隊群クラス＋指揮下（隷下）の護衛隊クラスも読み込まれています。
.Include()を使用して読み込みを行っていない場合はエラーとなるので試してみてください。

SELECT
    [Project1].[EscortFlotillaId] AS [EscortFlotillaId],
    [Project1].[EscortFlotillaName] AS [EscortFlotillaName],
    [Project1].[C1] AS [C1],
    [Project1].[EscortDivisionId] AS [EscortDivisionId],
    [Project1].[EscortDivisionName] AS [EscortDivisionName],
    [Project1].[EscortFlotilla_EscortFlotillaId] AS [EscortFlotilla_EscortFlotillaId]
    FROM ( SELECT
        [Extent1].[EscortFlotillaId] AS [EscortFlotillaId],
        [Extent1].[EscortFlotillaName] AS [EscortFlotillaName],
        [Extent2].[EscortDivisionId] AS [EscortDivisionId],
        [Extent2].[EscortDivisionName] AS [EscortDivisionName],
        [Extent2].[EscortFlotilla_EscortFlotillaId] AS [EscortFlotilla_EscortFlotillaId],
        CASE WHEN ([Extent2].[EscortDivisionId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[EscortFlotillas] AS [Extent1]
        LEFT OUTER JOIN [dbo].[EscortDivisions] AS [Extent2] ON [Extent1].[EscortFlotillaId] = [Extent2].[EscortFlotilla_EscortFlotillaId]
    )  AS [Project1]
    ORDER BY [Project1].[EscortFlotillaId] ASC, [Project1].[C1] ASC

ネストしたEntityからデータを読み込む

全護衛隊群と隷下の護衛隊、さらに護衛隊に所属している全護衛艦を読み込みます。
3つ以上ネストしているEntityの場合はInclude()プロパティの中でSelectを使用して読み込みます。

/// <summary>
/// 護衛隊群と隷下の護衛隊+護衛隊隷下の護衛艦全読み込み
/// </summary>
/// <returns>List<護衛隊群<護衛隊<護衛艦>>></returns>
public List<EscortFlotilla> ReadFlotillaNest() {
    using (var db = new ShipsDbContext()) {
        var fd = db.EscortFlotillas.Include(x => x.EscortDivision
                                        .Select(y => y.SelfDefenseShips))
                                   .ToList();
        return fd;
    }
}

（補足）LINQ toEntitiesとラムダ式

先程までは.Include()プロパティの中でEntityクラス名を明示的に記入していましたが、ラムダ式を使用して記述することが可能です。

期待する結果

SELECT EF.EscortFlotillaName, ED.EscortDivisionName, ED.ShipName
FROM EscortFlotillas AS EF
  LEFT JOIN (SELECT * FROM EscortDivisions
      LEFT JOIN (SELECT * FROM SelfDefenseShips) AS SDS
        ON SDS.EscortDivision_EscortDivisionId = EscortDivisions.EscortDivisionId) AS ED
    ON ED.EscortFlotilla_EscortFlotillaId = EF.EscortFlotillaId

実行結果

//護衛隊群+隷下の護衛隊+護衛隊隷下の護衛艦全読み込み
var fd = readData.ReadFlotillaNest();
foreach (var f in fd) { //護衛隊群のforeach
    Console.WriteLine(f.EscortFlotillaName);
    foreach (var divi in f.EscortDivision) {    //護衛隊のforeach
        Console.WriteLine(" - {0}", divi.EscortDivisionName);
        foreach (var sds in divi.SelfDefenseShips) {    //護衛艦のforeach
            Console.WriteLine("  - {0}", sds.ShipName);
        }
    }
}

全護衛隊群と隷下の護衛隊、さらに護衛隊に所属している護衛艦を読み込むことが出来ています。

SELECT
    [Project1].[EscortFlotillaId] AS [EscortFlotillaId],
    [Project1].[EscortFlotillaName] AS [EscortFlotillaName],
    [Project1].[C2] AS [C1],
    [Project1].[EscortDivisionId] AS [EscortDivisionId],
    [Project1].[EscortDivisionName] AS [EscortDivisionName],
    [Project1].[EscortFlotilla_EscortFlotillaId] AS [EscortFlotilla_EscortFlotillaId],
    [Project1].[C1] AS [C2],
    [Project1].[ShipNumber] AS [ShipNumber],
    [Project1].[ShipName] AS [ShipName],
    [Project1].[StandardDisplacement] AS [StandardDisplacement],
    [Project1].[FullLoadDisplacement] AS [FullLoadDisplacement],
    [Project1].[FullLength] AS [FullLength],
    [Project1].[FullWidth] AS [FullWidth],
    [Project1].[CommissionYear] AS [CommissionYear],
    [Project1].[EscortDivision_EscortDivisionId] AS [EscortDivision_EscortDivisionId],
    [Project1].[HullCode_HullCodeId] AS [HullCode_HullCodeId],
    [Project1].[ShipClass_ShipClassId] AS [ShipClass_ShipClassId]
    FROM ( SELECT
        [Extent1].[EscortFlotillaId] AS [EscortFlotillaId],
        [Extent1].[EscortFlotillaName] AS [EscortFlotillaName],
        [Join1].[EscortDivisionId] AS [EscortDivisionId],
        [Join1].[EscortDivisionName] AS [EscortDivisionName],
        [Join1].[EscortFlotilla_EscortFlotillaId] AS [EscortFlotilla_EscortFlotillaId],
        [Join1].[ShipNumber] AS [ShipNumber],
        [Join1].[ShipName] AS [ShipName],
        [Join1].[StandardDisplacement] AS [StandardDisplacement],
        [Join1].[FullLoadDisplacement] AS [FullLoadDisplacement],
        [Join1].[FullLength] AS [FullLength],
        [Join1].[FullWidth] AS [FullWidth],
        [Join1].[CommissionYear] AS [CommissionYear],
        [Join1].[EscortDivision_EscortDivisionId] AS [EscortDivision_EscortDivisionId],
        [Join1].[HullCode_HullCodeId] AS [HullCode_HullCodeId],
        [Join1].[ShipClass_ShipClassId] AS [ShipClass_ShipClassId],
        CASE WHEN ([Join1].[EscortDivisionId] IS NULL) THEN CAST(NULL AS int) WHEN ([Join1].[ShipNumber] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
        CASE WHEN ([Join1].[EscortDivisionId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
        FROM  [dbo].[EscortFlotillas] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[EscortDivisionId] AS [EscortDivisionId], [Extent2].[EscortDivisionName] AS [EscortDivisionName], [Extent2].[EscortFlotilla_EscortFlotillaId] AS [EscortFlotilla_EscortFlotillaId], [Extent3].[ShipNumber] AS [ShipNumber], [Extent3].[ShipName] AS [ShipName], [Extent3].[StandardDisplacement] AS [StandardDisplacement], [Extent3].[FullLoadDisplacement] AS [FullLoadDisplacement], [Extent3].[FullLength] AS [FullLength], [Extent3].[FullWidth] AS [FullWidth], [Extent3].[CommissionYear] AS [CommissionYear], [Extent3].[EscortDivision_EscortDivisionId] AS [EscortDivision_EscortDivisionId], [Extent3].[HullCode_HullCodeId] AS [HullCode_HullCodeId], [Extent3].[ShipClass_ShipClassId] AS [ShipClass_ShipClassId]
            FROM  [dbo].[EscortDivisions] AS [Extent2]
            LEFT OUTER JOIN [dbo].[SelfDefenseShips] AS [Extent3] ON [Extent2].[EscortDivisionId] = [Extent3].[EscortDivision_EscortDivisionId] ) AS [Join1] ON [Extent1].[EscortFlotillaId] = [Join1].[EscortFlotilla_EscortFlotillaId]
    )  AS [Project1]
    ORDER BY [Project1].[EscortFlotillaId] ASC, [Project1].[C2] ASC, [Project1].[EscortDivisionId] ASC, [Project1].[C1] ASC
Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email
前の記事
ASP.NET MVCのBootstrap3を4にアップデート
次の記事
SQLServerに日本語データをInsertすると文字化けする。