SQL到LINQ实体的SQL查询配置文件不需要

本文关键字：SQL 配置文件 不需要 查询 实体 LINQ | 更新日期: 2023-09-27 18:17:09

所以我有这个查询:

using (var unitOfWork = this.unitOfWorkFactory.Create(LockType.Read))
{
    var blogRepository = this.repositoryFactory.Create<SpaTest.Domain.Blogging.Blog>(unitOfWork);
    var postRepository = this.repositoryFactory.Create<SpaTest.Domain.Blogging.Post>(unitOfWork);
    var blogs = blogRepository.Retrieve()
        .GroupJoin(
            postRepository.Retrieve(),
            b => b.Id,
            p => p.BlogId,
            (blog, posts) =>
            new Models.Blogging.Blog()
                {
                    Title = blog.Title,
                    Content = blog.Content,
                    DateAdded = blog.DateAdded,
                    Id = blog.Id,
                    PostCount = posts.Count()
                }).Distinct();
    var result = blogs.ToArray();
    return this.Ok(result);
}

我希望得到一个连接语句，因为MSDN声明这相当于一个左外连接。

基本上，我想要返回所有的博客和每个博客的帖子数。SQL Profiler实际上显示了2个单独的select语句:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Content] AS [Content], 
[Extent1].[BlogId] AS [BlogId], 
[Extent1].[UserId] AS [UserId], 
[Extent1].[DateAdded] AS [DateAdded]
FROM [dbo].[Posts] AS [Extent1]
SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title], 
[Extent1].[Content] AS [Content], 
[Extent1].[UserId] AS [UserId], 
[Extent1].[DateAdded] AS [DateAdded]
FROM [dbo].[Blogs] AS [Extent1]

所以我不知道这是怎么回事。

编辑

Retrieve()实现:

/// <summary>
        /// Retrieves entities from the repository based on the query provided
        /// </summary>
        /// <param name="query">
        /// The query to use when matching entities in the repository
        /// </param>
        /// <returns>
        /// The <see cref="IEnumerable{T}"/> of entities retrieved from the repository
        /// </returns>
        public virtual IEnumerable<TEntity> Retrieve(Expression<Func<TEntity, bool>> query)
        {
            if (query == null)
            {
                throw new ArgumentNullException("query", "A query must be specified to use the overloaded Retrieve method.  To retrieve all entities, use Retrieve()");
            }
            return this.unitOfWork.Context.CreateObjectSet<TEntity>().AsNoTracking().Where(query);
        }

背景更新

因此，在将using(var unitOfWork....更改为using(var context = new DefaultContext())后，我得到了这个sql输出:

SELECT 
1 AS [C1], 
[Project1].[Title] AS [Title], 
[Project1].[Content] AS [Content], 
[Project1].[DateAdded] AS [DateAdded], 
[Project1].[Id] AS [Id], 
[Project1].[C1] AS [C2]
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[Content] AS [Content], 
    [Extent1].[DateAdded] AS [DateAdded], 
    (SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Posts] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[BlogId]) AS [C1]
    FROM [dbo].[Blogs] AS [Extent1]
)  AS [Project1]

SQL到LINQ实体的SQL查询配置文件不需要

在您的Retrieve方法中，您隐式地从IQueryable<T>转换为IEnumerable<T>，这相当于在您的查询上调用AsEnumerable。这样做的结果是您的GroupJoin调用使用IEnumerable<T>实现(在内存中运行)，而不是IQueryable<T>实现(在DB上运行)。

考虑到执行内存中GroupJoin所需的唯一东西是记录本身，这解释了为什么只生成两个SELECT语句。要强制在DB端执行GroupJoin，您需要从Retrieve方法返回IQueryable<T>。

相关文章：