c# – 实体框架一对多关系
发布时间:2020-12-15 22:36:42 所属栏目:百科 来源:网络整理
导读:我的EF查询需要大约3秒来获取10个玩家,因为它获取了另一个表的所有500k行,而不是我需要的几个. 这是PlayerEntity: namespace RocketLeagueStats.Database.Entities{ [Table("players",Schema = "public")] public class PlayerEntity { [Key] [Column("id")
我的EF查询需要大约3秒来获取10个玩家,因为它获取了另一个表的所有500k行,而不是我需要的几个.
这是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; } } } 这是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='?'],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='?'],"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='?'],"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目前是一场噩梦.
您可以尝试以下解决方法(但如果您问我,最好切换回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相同的效果. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |