EntityFramework SELECT N+1 issue

EF_Select_N_plus_1_Repro.cs

/*
 * Create a new Class Library and copy/paste this file over the class1.cs file.
 *
 * On Package Mananager Console:
 *
 *     @("MSTest.TestFramework","MSTest.TestAdapter","EntityFramework") | foreach { Install-Package $_ }
 *
 * Run `sqllocaldb c soquestion` to create the instance.
 * Afterwards create database `TestDb` e.g. with SSMS on that instance.
 */

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace EntityFrameworkIntegrationTests {

    public class Team {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Member> Members { get; set; }
    }

    public class Member {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Pet {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public Member Member { get; set; }
    }

    public class MyDbContext : DbContext {
        public MyDbContext(string nameOrConnectionString)
            : base(nameOrConnectionString) {
            Database.SetInitializer<MyDbContext>(null);
            Database.Log = Console.WriteLine;
        }

        public virtual DbSet<Team> Teams { get; set; }
        public virtual DbSet<Member> Members { get; set; }
        public virtual DbSet<Pet> Pets { get; set; }
    }

    public class UnitUnderTest {
        private string connectionString;

        public UnitUnderTest(string connectionString) {
            this.connectionString = connectionString;
        }

        public PetViewModel[] QueryPetViewModel_1(string pattern) {
            using (var context = new MyDbContext(connectionString)) {
                return context.Pets
                    .Where(p => p.Name.Contains(pattern))
                    .ToArray()
                    .Select(p => new PetViewModel {
                        Name = p.Name,
                        TeamItIndirectlyBelongsTo = "TODO",
                    })
                    .ToArray();
            }
        }

        public PetViewModel[] QueryPetViewModel_2(string pattern) {
            using (var context = new MyDbContext(connectionString)) {
                var petInfos = context.Pets
                    .Where(p => p.Name.Contains(pattern))
                    .Join(context.Members,
                        p => p.Member.Id,
                        m => m.Id,
                        (p, m) => new { Pet = p, Member = m }
                    )
                    .ToArray();

                var result = new List<PetViewModel>();

                foreach (var info in petInfos) {
                    var team = context.Teams
                        .SingleOrDefault(t => t.Members.Any(m => m.Id == info.Member.Id));

                    result.Add(new PetViewModel {
                        Name = info.Pet.Name,
                        TeamItIndirectlyBelongsTo = team?.Name,
                    });
                }

                return result.ToArray();
            }
        }
    }

    public class PetViewModel {
        public string Name { get; set; }
        public string TeamItIndirectlyBelongsTo { get; set; }
    }

    [TestClass]
    public class Tests {
        const string connectionString = @"Data Source=(LocalDb)\soquestion;Integrated Security=True;Initial Catalog=TestDb";

        [AssemblyInitialize]
        public static void AssemblyInitialize(TestContext context) {
            ExecuteNonQuery(@"
                IF OBJECT_ID('Pets') IS NOT NULL EXEC('DROP TABLE Pets;');
                IF OBJECT_ID('Members') IS NOT NULL EXEC('DROP TABLE Members;');
                IF OBJECT_ID('Teams') IS NOT NULL EXEC('DROP TABLE Teams;');
 
                CREATE TABLE Teams (
                    Id INT PRIMARY KEY, 
                    Name NVARCHAR(250)
                );

                CREATE TABLE Members (
                    Id INT PRIMARY KEY,
                    Name NVARCHAR(250),
                    Team_Id INT,
                    CONSTRAINT FK_Members_Teams FOREIGN KEY (Team_Id) REFERENCES Teams(Id)
                );

                CREATE TABLE Pets (
                    Id INT PRIMARY KEY,
                    Name NVARCHAR(250),
                    Member_Id INT,
                    CONSTRAINT FK_Pets_Mebmers FOREIGN KEY (Member_Id) REFERENCES Members(Id)
                );
            ");
        }

        [TestInitialize]
        public void SetUp() {
            ExecuteNonQuery(@"DELETE FROM Pets;");
            ExecuteNonQuery(@"DELETE FROM Members;");
            ExecuteNonQuery(@"DELETE FROM Teams;");
        }

        [TestMethod]
        public void Sanity_check() {
            ExecuteNonQuery("INSERT INTO Teams (Id, Name) VALUES (1, 'Alpha')");
            ExecuteNonQuery("INSERT INTO Members (Id, Name, Team_Id) VALUES (50, 'John', 1)");
            ExecuteNonQuery("INSERT INTO Pets (Id, Name, Member_Id) VALUES (80, 'Wookie', 50)");

            using (var context = new MyDbContext(connectionString)) {
                var team = context.Teams
                    .Include(t => t.Members)
                    .Single(t => t.Id == 1);

                Assert.AreEqual("Alpha", team.Name);
                Assert.AreEqual("John", team.Members.Single().Name);

                var pet = context.Pets.Single(p => p.Id == 80);

                Assert.AreEqual("Wookie", pet.Name);
                Assert.AreEqual("John", pet.Member.Name);
            }
        }

        [TestMethod]
        public void Can_query_pet_team_1() {
            ExecuteNonQuery("INSERT INTO Teams (Id, Name) VALUES (1, 'Alpha')");
            ExecuteNonQuery("INSERT INTO Members (Id, Name, Team_Id) VALUES (50, 'John', 1)");
            ExecuteNonQuery("INSERT INTO Pets (Id, Name, Member_Id) VALUES (80, 'Wookie', 50)");
            ExecuteNonQuery("INSERT INTO Pets (Id, Name, Member_Id) VALUES (81, 'Cookie', 50)");

            var sut = new UnitUnderTest(connectionString);

            using (var context = new MyDbContext(connectionString)) {
                var petViewModels = sut.QueryPetViewModel_1("ookie");
                Assert.AreEqual(2, petViewModels.Count());
                Assert.AreEqual("Wookie", petViewModels.First().Name);
                Assert.AreEqual("Alpha", petViewModels.First().TeamItIndirectlyBelongsTo);
            }
        }

        [TestMethod]
        public void Can_query_pet_team_2() {
            ExecuteNonQuery("INSERT INTO Teams (Id, Name) VALUES (1, 'Alpha')");
            ExecuteNonQuery("INSERT INTO Members (Id, Name, Team_Id) VALUES (50, 'John', 1)");
            ExecuteNonQuery("INSERT INTO Pets (Id, Name, Member_Id) VALUES (80, 'Wookie', 50)");
            ExecuteNonQuery("INSERT INTO Pets (Id, Name, Member_Id) VALUES (81, 'Cookie', 50)");

            var sut = new UnitUnderTest(connectionString);

            using (var context = new MyDbContext(connectionString)) {
                var petViewModels = sut.QueryPetViewModel_2("ookie");
                Assert.AreEqual(2, petViewModels.Count());
                Assert.AreEqual("Wookie", petViewModels.First().Name);
                Assert.AreEqual("Alpha", petViewModels.First().TeamItIndirectlyBelongsTo);
            }
        }

        private static void ExecuteNonQuery(string ddl) {
            using (var conn = new SqlConnection(connectionString))
            using (var cmd = new SqlCommand(ddl, conn)) {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}