如何避免EF Core 2.1中的n + 1个查询?

问题描述：

我正在使用EF Core 2.1预览版，可以减少N + 1查询问题. 我正在尝试进行查询，该查询选择了帖子作者的论坛主题:

I'm using EF Core 2.1 preview which was supposed to reduce N+1 queries problem. I'm trying to make query, that selects Forum Threads with authors of posts:

dbContext.ForumThreads
   .Include(t => t.Posts)
   .Take(n)
   .Select(t => new
   {
      t.Id,
      t.Title,
      PostAuhtors = t.Posts.Select(p => p.Author).Take(5)
   }).ToArray();

这将产生n + 1个查询:对于每个ForumThread，它都会选择帖子作者

This produces n+1 queries: For each ForumThread it selects post authors

模式很简单:

public class ForumThread 
{
   public Guid Id {get;set;}
   public string Title {get;set;}
   public ICollection<ForumPost> Posts {get;set;}
}

public class ForumPost 
{
  public Guid Id {get;set;}
  public string Author {get;set;}
  public string Content {get;set;}
}

答

我认为您可以用更少的查询(只有2个)来实现这一点，从而在内存中实现某些行为.这段代码能满足您的要求吗?

I think you can achieve that with less queries (only 2), making some of that behavior in memory. Does this code do what you want?

class Program
    {
        static void Main(string[] args)
        {
            using (var db = new SampleContext())
            {
                Console.ReadLine();
                var result = db.Threads
                    .Include(t => t.Posts)
                    .Take(10)
                    .Select(t => new
                    {
                        t.Id,
                        t.Title,
                        t.Posts
                        // Do this in memory  
                        //PostAuhtors = t.Posts.Select(p => p.Author).Take(5)
                    }).ToArray();

                Console.WriteLine($"» {result.Count()} Threads.");
                foreach (var thread in result)
                {
                    // HERE !!
                    var PostAuhtors = thread.Posts.Select(p => p.Author).Take(5);
                    Console.WriteLine($"» {thread.Title}:  {string.Join("; ", PostAuhtors)} authors");
                }
                Console.ReadLine();
            }
        }
    }

    public class SampleContext : DbContext
    {
        public static readonly LoggerFactory MyLoggerFactory = new LoggerFactory(new[] {
                new ConsoleLoggerProvider((category, level)
                    => category == DbLoggerCategory.Database.Command.Name
                       && level == LogLevel.Debug, true)
            });

        public DbSet<ForumThread> Threads { get; set; }
        public DbSet<ForumPost> Posts { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder  
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(MyLoggerFactory)
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFStart;Trusted_Connection=True;");
        }
    }

    public class ForumThread
    {
        public Guid Id { get; set; }
        public string Title { get; set; }
        public ICollection<ForumPost> Posts { get; set; }
    }

    public class ForumPost
    {
        public Guid Id { get; set; }
        public string Author { get; set; }
        public string Content { get; set; }
    }

这是输出:

如何避免EF Core 2.1中的n + 1个查询?

相关推荐