实体框架一对多的关系 [英] Entity Framework One to many relationship

问题描述

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

这是PlayerEntity：

 命名空间RocketLeagueStats.Database.Entities 
 {
 [Table（players，Schema = public）] 
 public class PlayerEntity 
 {
 
 [Key] 
 [Column（id）] 
 public int Id {get;组; } 
 
 [Column（unique_id）] 
 public string UniqueId {get;组; } 
 
 [Column（display_name）] 
 public string DiplayName {get;组; } 
 
 [Column（platform_id）] 
 [JsonIgnore] 
 public int PlatformId {get;组; } 
 
 [ForeignKey（PlatformId）] 
 public PlatformEntity Platform {get;组; } 
 
 [Column（avatar）] 
 public string头像{get;组; } 
 
 public PlayerStatsEntity Stats {get;组; } 
 
 public List< PlayerRankedEntity>排名{get;组; } 
 
 [Column（last_requested）] 
 public DateTime LastRequested {get;组; } 
 
 [Column（created_at）] 
 public DateTime CreatedAt {get;组; } 
 
 [Column（updated_at）] 
 public DateTime UpdatedAt {get;组; } 
 
} 
} 
  

这是PlayerRankedEntity： / p>

 命名空间RocketLeagueStats.Database.Entities 
 {
 [表（player_ranked，Schema =public ）] 
 public class PlayerRankedEntity 
 {
 
 [ForeignKey（Player）] 
 [Column（player_id）] 
 [JsonIgnore] 
 public int PlayerId {get;组; } 
 
 [Column（season_id）] 
 [JsonIgnore] 
 public int SeasonId {get;组; } 
 
 [Column（playlist_id）] 
 [JsonIgnore] 
 public int PlaylistId {get;组; } 
 
 [Column（matches_played）] 
 public int MatchesPlayed {get;组; } 
 
 [Column（rank_points）] 
 public int RankPoints {get;组; } 
 
 [Column（tier）] 
 public int Tier {get;组; } 
 
 [Column（division）] 
 public int Division {get;组; } 
 
 public PlayerEntity Player {get;组; } 
 
} 
} 
  

这是PlayerStatsEntity： / p>

 命名空间RocketLeagueStats.Database.Entities 
 {
 [表（player_stats，Schema =public ）] 
 public class PlayerStatsEntity 
 {
 
 [Key，ForeignKey（Player）] 
 [Column（player_id）] 
 [JsonIgnore ] 
 public int PlayerId {get;组; } 
 
 [Column（wins）] 
 public int Wins {get;组; } 
 
 [Column（goals）] 
 public int Goals {get;组; } 
 
 [Column（mvps）] 
 public int Mvps {get;组; } 
 
 [列（saves）] 
 public int保存{get;组; } 
 
 [Column（shots）] 
 public int Shots {get;组; } 
 
 [Column（assist）] 
 public int协助{get;组; } 
 
 public PlayerEntity Player {get;组; } 
 
} 
} 
  

这是我的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）'只需要几ms）：

  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以获取{players.Length}玩家）; 
} 
  

这是输出，注意它会生成两个查询而不是一个： / p>

  2016-10-31 22：50：18.6416 INFO执行DbCommand（8ms）[参数= [@__ p_0 ='？'] ，CommandType ='Text'，CommandTimeout = '30'] 
 SELECTxid，x，avatar，x，created_at，x x，last_requested，x，platform_id，x，unique_id，x，updated_at
 FROMpublic。playersASx
 ORDER BYx。id
 LIMIT @__ p_0 
 2016-10-31 22：50：18.7128 INFO执行DbCommand（0ms）[参数= [@__ p_0 ='？']，CommandType =Text，CommandTimeout = '30'] 
 SELECTp。player_id，p。season_id，p，playlist_id，p，division matches_played，p。rank_points，p。tier
 FROMpublic。player_rankedASp
 WHERE EXISTS（
 SELECT 1 
 FROMpublic。playersASx
 WHEREp。player_id=x。id
 LIMIT @__ p_0）
 ORDER BY p。player_id
 Took 39 91ms获取10个玩家
  

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

我如何解决这个问题，我的属性有什么其他问题吗？

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

编辑如果我使用 .OrderBy（x => x.CreatedAt）或 .Where（x => x.DiplayName.Contains（mike））在查询中，它的速度要快很多。

生成的查询：

  2016-11-01 00：14：15.9638 INFO执行DbCommand（24ms）[Parameters = [@__ p_0 ='？']，CommandType ='Text'，CommandTimeout = '30'] 
 SELECTx id，x，avatar，x，created_at，x，display_name，x，last_requested，x，platform_id，x _$ b LIMIT @__ p_0 
 2016-11-01 00：14：16.0972 INFO执行DbCommand（44ms）[Parameters = [@__ p_0 ='？']，CommandType ='Text'，CommandTimeout = '30'] 
 SELECTp。player_id，p。 season_id，p，playlist_id，p，division，p，matches_played，p，rank_points，p player_rankedASp
 INNER JOIN（
 SELECT DISTINCTx。created_at，x。id
 FROMpublic x
 ORDER BYx。created_at，x。id
 LIMIT @__ p_0 
）ASx0ONpplayer_id= x0。id
 ORDER BYx0。created_at，x0。id
取得314ms获取10个玩家
  pre> 
 
解决方案
 EF Core目前是一场噩梦。
 
 
 
尝试以下解决方法（但如果您问我，更好地切换到EF6）。
 
 
 
而不是：
  var players = database.Players 
 .Include（x => x.Ranks）
 .Take（10）
 .ToArray（）; 
  
使用：
 code> var players = database.Players 
 .Take（10）
 .ToArray（）; 
 var playerIds = players.Select（p => p.Id）; 
 database.PlayerRanks.Where（r => playerIds.Contains（r.PlayerId））。Load（）; 
  
应该产生与 Include相同的效果 。
 
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.

This is the PlayerEntity:
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; }

    }
}
This is the 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; }

    }
}
This is the 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; }

    }
}
This is my DatabaseContext.OnModelCreating method:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<PlayerRankedEntity>()
        .HasKey(k => new { k.PlayerId, k.SeasonId, k.PlaylistId });
}
This is the code that takes over 3 seconds to complete (If I remove '.Include(x => x.Ranks)' it just takes a few ms):
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");
}
This is the output, notice that it generates two queries instead of one:
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
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.

How can I fix this, and are there any other problems with my attributes?

I am using Microsoft.EntityFrameworkCore v1.0.1.

Edit: If I use .OrderBy(x => x.CreatedAt) or .Where(x => x.DiplayName.Contains("mike")) in the query, it goes a lot faster.

Generated query:
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

解决方案
EF Core currently is a nightmare.

You can try the following workaround (but if you ask me, better switch back to EF6).

Instead of:
var players = database.Players
    .Include(x => x.Ranks)
    .Take(10)
    .ToArray();
use:
var players = database.Players
    .Take(10)
    .ToArray();
var playerIds = players.Select(p => p.Id);
database.PlayerRanks.Where(r => playerIds.Contains(r.PlayerId)).Load();
which should produce the same effect as Include.

                        
这篇关于实体框架一对多的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！