lambda查询翻译.COUNT作为选择行(EF6代码首先)

Question

我有2个简单的表。

职位是主人，有n票。我想以他们的投票数返回帖子。这是我的代码：

db.posts.Select(p=>
new PostlDto{Title= p.Title, VoteCount= p.Votes.Count})

可以，我有1个呼叫来接听帖子和几个呼叫以获取计数。，但问题在于，每次选择行时，它都不是对它们进行计数，这是一个耗时的操作。这就是SQL Profiler向我展示的

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[PostId] AS [PostId], 
    FROM [dbo].[Votes] AS [Extent1]
    WHERE [Extent1].[PostId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=6

我还试图通过在基本查询中加入投票表来接听Count 1调用，但这也花费了太长时间，因为它也返回行而不是计数。：|

 db.posts
.Include(p=>p.Votes)
.Select(p=>
 new PostlDto{Title= p.Title, VoteCount= p.Votes.Count})

这是翻译成

的查询

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Title] AS [Title], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[Pid] AS [Pid], 
    [Project1].[PostId] AS [PostId], 
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Title] AS [Title], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[PostId] AS [PostId], 
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Posts] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Votes] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PostId]
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

如何更改它以运行简单的计数？：|

这些查询需要多秒钟才能运行！

Answer 1

必须进行其他事情。将您的代码与此非Repro进行比较：

using System;
using System.Linq;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data;
using System.Diagnostics;
namespace Ef6Test
{
    public class Post
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public virtual ICollection<Vote> Votes { get; } = new HashSet<Vote>();
    }
    public class Vote
    {
        public int Id { get; set; }
        public Post Post { get; set; }
    }
    public class PostDto
    {
        public string Title { get; set; }
        public int VoteCount { get; set; }
    }
    class Db : DbContext
    {

        public DbSet<Post> Posts { get; set; }
        public DbSet<Vote> Votes { get; set; }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }


    }
    class Program
    {

        static void Main(string[] args)
        {

            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                db.Database.Log = m => Console.WriteLine(m);
                db.Database.Initialize(false);
                var posts = db.Posts.Select(p => new PostDto { Title = p.Title, VoteCount = p.Votes.Count }).ToList();

            }
            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}

输出

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Title] AS [Title],
    (SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[Votes] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[Post_Id]) AS [C1]
    FROM [dbo].[Posts] AS [Extent1]