2014/01/04

[Entity Framework] LINQ で サブクエリ(副問い合わせ)を使用してデータを取得する


Entity Framework で、サブクエリ(副問い合わせ)を LINQ で使用してデータを取得する方法をご紹介します。

前提条件

以下の前提条件で動作検証しています。

  • Visual Studio Express 2013 for Windows Desktop
  • Entity Framework 6.0.2
  • SQL Server 2012 Express
  • コードファーストのコンソールアプリケーション

Entity Framework 関連

作成するデータベースはブログを想定しています。ブログのポスト(投稿)とコメントを管理します。ポストは Post クラス、コメントは Comment クラスになります。

・Post.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFSubQuery
{
  public class Post
  {
    public int PostId { get; set; }

    public string Author { get; set; }

    public string Title { get; set; }

    public string Body { get; set; }

    public DateTime Posted { get; set; }

    //コメント一覧
    //テーブルのフィールドは作成されない
    public virtual ICollection<Comment> Comments { get; set; }
  }
}

・Comment.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFSubQuery
{
  public class Comment
  {
    public int CommentId { get; set; }

    public string Author { get; set; }

    public string Body { get; set; }

    public DateTime Posted { get; set; }

    //外部キー
    //Post を設定すると自動的に更新される
    public int PostId { get; set; }

    //親フィールド、テーブルには作成されないが
    //このフィールドの更新が必要
    public virtual Post Post { get; set; }
  }
}

テーブルの定義を行うコンテキストクラスを作成する必要があります。

・BlogContext.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace EFSubQuery
{
  class BlogContext : DbContext
  {
    public DbSet<Post> Posts { get; set; }

    public DbSet<Comment> Comments { get; set; }
  }
}

App.config にデータベースの接続文字列を追加します。コンテキストクラスと同じ名前である必要があるので注意してください。”MultipleActiveResultSets=true;”は、DataReader のエラーが発生するようでしたら追加してください。

・App.config

<connectionStrings>
  <add name="BlogContext"
       connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=Blog;Integrated Security=True;MultipleActiveResultSets=true;"
       providerName="System.Data.SqlClient" />
</connectionStrings>

データ更新・参照プログラム

データを更新・参照するプログラムは以下のようになります。

サブクエリは、let 句でデータを取得した結果の Contains メソッドを使用して実現します。

なお、このプログラムは、SQL ログをデバッグ出力しているので、あとで Entity Framework が出力した SQL を確認します。

・Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
using System.Diagnostics;

namespace EFSubQuery
{
  class Program
  {
    static void Main(string[] args)
    {
      //データベース初期化
      Database.SetInitializer(new DropCreateDatabaseIfModelChanges<BlogContext>());

      using (var context = new BlogContext())
      {
        //SQLログ設定
        context.Database.Log = Logger;

        var post1 = new Post(){
                Author="山田太郎",
                Title="いい天気",
                Body="こんにちは。いい天気です。",
                Posted = DateTime.Now};

        context.Posts.Add(post1);

        var comment1 = new Comment() {
                Author = "山田花子",
                Body ="こんにちは",
                Posted = DateTime.Now,
                Post = post1};

        context.Comments.Add(comment1);

        var comment2 = new Comment() {
                Author = "佐藤次郎",
                Body = "よろしくお願いします。",
                Posted = DateTime.Now,
                Post = post1};

        context.Comments.Add(comment2);

        var post2 = new Post(){
                Author = "田中一郎",
                Title = "散歩",
                Body = "いい景色です。",
                Posted = DateTime.Now};

        context.Posts.Add(post2);

        var comment3 = new Comment()
        {
          Author = "吉田花子",
          Body = "いいですね。",
          Posted = DateTime.Now,
          Post = post2
        };

        context.Comments.Add(comment3);     

        context.SaveChanges();

        //サブクエリでデータを取得(CommentId=3)
        var posts = from t in context.Posts
              orderby t.PostId
              let c2 = from c in context.Comments
                   where c.CommentId == 3
                   select c.PostId
              where c2.Contains(t.PostId)
              select t;

        foreach (var post in posts)
        {
          Console.WriteLine("[Post]");
          Console.WriteLine(post.PostId.ToString());
          Console.WriteLine(post.Author);
          Console.WriteLine(post.Title);
          Console.WriteLine(post.Body);
          Console.WriteLine(post.Posted.ToLongDateString());

          foreach (var comment in post.Comments)
          {
            Console.WriteLine("[Comment]");
            Console.WriteLine(comment.CommentId.ToString());
            Console.WriteLine(comment.Author);
            Console.WriteLine(comment.Body);
            Console.WriteLine(comment.Posted.ToLongDateString());
          }
        }
        Console.ReadKey();

      }
    }

    private static void Logger(string message)
    {
      Debug.Write(message);
    }

  }
}

コンソールに出力された内容は以下のようになります。サブクエリの条件として、コメントID = 3 としていたので、正しく出力されていることが分かります。

[Post]
2
田中一郎
散歩
いい景色です。
2014年1月4日
[Comment]
3
吉田花子
いいですね。
2014年1月4日

そして、サブクエリを Entity Framework が変換して出力した SQL ログは以下のようになります。EXISTS 句を使用していますね。ですので、こちらも期待通りです。

SELECT
    [Extent1].[PostId] AS [PostId],
    [Extent1].[Author] AS [Author],
    [Extent1].[Title] AS [Title],
    [Extent1].[Body] AS [Body],
    [Extent1].[Posted] AS [Posted]
    FROM [dbo].[Posts] AS [Extent1]
    WHERE  EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[Comments] AS [Extent2]
        WHERE (3 = [Extent2].[CommentId]) AND ([Extent2].[PostId] = [Extent1].[PostId])
    )
    ORDER BY [Extent1].[PostId] ASC
-- Executing at 2014/01/04 21:52:50 +09:00
-- Completed in 2 ms with result: SqlDataReader

まとめ

Entity Framework の LINQ で、サブクエリを使用する方法を見てましたがいかがだったでしょうか。

Entity Framework でサブクエリを使用する方法は、ネット上にほとんどありません。

ですので、このエントリーを参考にしてくださればと思います。

参考サイト


スポンサーリンク


このエントリーをはてなブックマークに追加




Twitter ではブログにはない、いろんな情報を発信しています。


コメント

コメントを書く



プロフィール

  • 名前:fnya
    経歴:
    SE としての経験は15年以上。様々な言語と環境で業務系システム開発を行い、セキュリティ対策などもしていました。現在は趣味SE。

    Twitter では、ブログでは取り上げない情報も公開しています。


    ブログについて

    このブログは、IT、スマートフォン、タブレット、システム開発などに関するさまざまな話題を取り上げたり、雑感などをつづっています。

    >>ブログ詳細
    >>自作ツール
    >>運営サイト
    >>Windows 10 まとめ

    Twitter のフォローはこちらから Facebook ページはこちら Google+ページはこちら RSSフィードのご登録はこちらから