c# - Entity Framework 一对多关系

Question

我的 EF 查询需要大约 3 秒来获取 10 个玩家，因为它获取了另一个表的所有 500k+ 行，而不是我需要的几行。

这是玩家实体:

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<PlayerRankedEntity> 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; }

    }
}

这是 PlayerRankedEntity:

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; }

    }
}

这是 PlayerStatsEntity:

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; }

    }
}

这是我的 DatabaseContext.OnModelCreating 方法:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<PlayerRankedEntity>()
        .HasKey(k => new { k.PlayerId, k.SeasonId, k.PlaylistId });
}

这是需要 3 多秒才能完成的代码(如果我删除 '.Include(x => x.Ranks)' 它只需要几毫秒):

using (var database = new DatabaseContext())
{
    var serviceProvider = database.GetInfrastructure();
    var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
    loggerFactory.AddNLog();

    var stopwatch = Stopwatch.StartNew();

    var players = database.Players
        .Include(x => x.Ranks)
        .Take(10)
        .ToArray();

    Console.WriteLine($"Took {stopwatch.ElapsedMilliseconds}ms to fetch {players.Length} players");
}

这是输出，注意它生成两个查询而不是一个:

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

我认为我搞砸了某处的关系，导致它选择了所有行。但我不知道我搞砸了什么。

我该如何解决这个问题，我的属性是否还有其他问题？

我正在使用 Microsoft.EntityFrameworkCore v1.0.1。

编辑:如果我使用 .OrderBy(x => x.CreatedAt) 或 .Where(x => x.DiplayName.Contains("mike ")) 在查询中，速度快了很多。

生成的查询:

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

Answer 1

EF Core 目前是一场噩梦。

您可以尝试以下解决方法(但如果您问我，最好切换回 EF6)。

代替:

var players = database.Players
    .Include(x => x.Ranks)
    .Take(10)
    .ToArray();

使用:

var players = database.Players
    .Take(10)
    .ToArray();
var playerIds = players.Select(p => p.Id);
database.PlayerRanks.Where(r => playerIds.Contains(r.PlayerId)).Load();

这应该产生与 Include 相同的效果。