title: Entity Framework One to many relationship
body: <p>My EF query takes around 3 seconds to fetch 10 players because it fetches all 500k+ rows of the other table, instead of the few I need.</p>
  
  <p>This is the PlayerEntity:</p>
  
  <pre><code>namespace RocketLeagueStats.Database.Entities
  {
      [Table("players", Schema = "public")]
      public class PlayerEntity
      {
  
          [Key]
          [Column("id")]
          public int Id { get; set; }
  
          [Column("unique_id")]
          public string UniqueId { get; set; }
  
          [Column("display_name")]
          public string DiplayName { get; set; }
  
          [Column("platform_id")]
          [JsonIgnore]
          public int PlatformId { get; set; }
  
          [ForeignKey("PlatformId")]
          public PlatformEntity Platform { get; set; }
  
          [Column("avatar")]
          public string Avatar { get; set; }
  
          public PlayerStatsEntity Stats { get; set; }
  
          public List&lt;PlayerRankedEntity&gt; Ranks { get; set; }
  
          [Column("last_requested")]
          public DateTime LastRequested { get; set; }
  
          [Column("created_at")]
          public DateTime CreatedAt { get; set; }
  
          [Column("updated_at")]
          public DateTime UpdatedAt { get; set; }
  
      }
  }
  </code></pre>
  
  <p>This is the PlayerRankedEntity:</p>
  
  <pre><code>namespace RocketLeagueStats.Database.Entities
  {
      [Table("player_ranked", Schema = "public")]
      public class PlayerRankedEntity
      {
  
          [ForeignKey("Player")]
          [Column("player_id")]
          [JsonIgnore]
          public int PlayerId { get; set; }
  
          [Column("season_id")]
          [JsonIgnore]
          public int SeasonId { get; set; }
  
          [Column("playlist_id")]
          [JsonIgnore]
          public int PlaylistId { get; set; }
  
          [Column("matches_played")]
          public int MatchesPlayed { get; set; }
  
          [Column("rank_points")]
          public int RankPoints { get; set; }
  
          [Column("tier")]
          public int Tier { get; set; }
  
          [Column("division")]
          public int Division { get; set; }
  
          public PlayerEntity Player { get; set; }
  
      }
  }
  </code></pre>
  
  <p>This is the PlayerStatsEntity:</p>
  
  <pre><code>namespace RocketLeagueStats.Database.Entities
  {
      [Table("player_stats", Schema = "public")]
      public class PlayerStatsEntity
      {
  
          [Key, ForeignKey("Player")]
          [Column("player_id")]
          [JsonIgnore]
          public int PlayerId { get; set; }
  
          [Column("wins")]
          public int Wins { get; set; }
  
          [Column("goals")]
          public int Goals { get; set; }
  
          [Column("mvps")]
          public int Mvps { get; set; }
  
          [Column("saves")]
          public int Saves { get; set; }
  
          [Column("shots")]
          public int Shots { get; set; }
  
          [Column("assists")]
          public int Assists { get; set; }
  
          public PlayerEntity Player { get; set; }
  
      }
  }
  </code></pre>
  
  <p>This is my DatabaseContext.OnModelCreating method:</p>
  
  <pre><code>protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity&lt;PlayerRankedEntity&gt;()
          .HasKey(k =&gt; new { k.PlayerId, k.SeasonId, k.PlaylistId });
  }
  </code></pre>
  
  <p>This is the code that takes over 3 seconds to complete (If I remove '.Include(x => x.Ranks)' it just takes a few ms):</p>
  
  <pre><code>using (var database = new DatabaseContext())
  {
      var serviceProvider = database.GetInfrastructure();
      var loggerFactory = serviceProvider.GetService&lt;ILoggerFactory&gt;();
      loggerFactory.AddNLog();
  
      var stopwatch = Stopwatch.StartNew();
  
      var players = database.Players
          .Include(x =&gt; x.Ranks)
          .Take(10)
          .ToArray();
  
      Console.WriteLine($"Took {stopwatch.ElapsedMilliseconds}ms to fetch {players.Length} players");
  }
  </code></pre>
  
  <p>This is the output, notice that it generates two queries instead of one:</p>
  
  <pre><code>2016-10-31 22:50:18.6416 INFO Executed DbCommand (8ms) [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='30']
  SELECT "x"."id", "x"."avatar", "x"."created_at", "x"."display_name", "x"."last_requested", "x"."platform_id", "x"."unique_id", "x"."updated_at"
  FROM "public"."players" AS "x"
  ORDER BY "x"."id"
  LIMIT @__p_0
  2016-10-31 22:50:18.7128 INFO Executed DbCommand (0ms) [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='30']
  SELECT "p"."player_id", "p"."season_id", "p"."playlist_id", "p"."division", "p"."matches_played", "p"."rank_points", "p"."tier"
  FROM "public"."player_ranked" AS "p"
  WHERE EXISTS (
      SELECT 1
      FROM "public"."players" AS "x"
      WHERE "p"."player_id" = "x"."id"
      LIMIT @__p_0)
  ORDER BY "p"."player_id"
  Took 3991ms to fetch 10 players
  </code></pre>
  
  <p>I think that I messed up on the relations somewhere, causing it to select all rows. But I don't know what I messed up.</p>
  
  <p>How can I fix this, and are there any other problems with my attributes?</p>
  
  <p>I am using Microsoft.EntityFrameworkCore v1.0.1.</p>
  
  <p><strong>Edit:</strong> If I use <code>.OrderBy(x =&gt; x.CreatedAt)</code> or <code>.Where(x =&gt; x.DiplayName.Contains("mike"))</code> in the query, it goes a lot faster.</p>
  
  <p>Generated query:</p>
  
  <pre><code>2016-11-01 00:14:15.9638 INFO Executed DbCommand (24ms) [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='30']
  SELECT "x"."id", "x"."avatar", "x"."created_at", "x"."display_name", "x"."last_requested", "x"."platform_id", "x"."unique_id", "x"."updated_at"
  FROM "public"."players" AS "x"
  ORDER BY "x"."created_at", "x"."id"
  LIMIT @__p_0
  2016-11-01 00:14:16.0972 INFO Executed DbCommand (44ms) [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='30']
  SELECT "p"."player_id", "p"."season_id", "p"."playlist_id", "p"."division", "p"."matches_played", "p"."rank_points", "p"."tier"
  FROM "public"."player_ranked" AS "p"
  INNER JOIN (
      SELECT DISTINCT "x"."created_at", "x"."id"
      FROM "public"."players" AS "x"
      ORDER BY "x"."created_at", "x"."id"
      LIMIT @__p_0
  ) AS "x0" ON "p"."player_id" = "x0"."id"
  ORDER BY "x0"."created_at", "x0"."id"
  Took 314ms to fetch 10 players
  </code></pre>