实体框架核心在转换时延迟加载

问题描述

将实体模型转换为DTO时，Entity Framework Core（v2.0.1）出现问题。基本上，用任何其他版本的短语来说，当我不希望加载时，都是延迟加载。这是一个简单的.NET Core控制台应用程序（带有Microsoft.EntityFrameworkCore.SqlServer（2.0.1）程序包）。

I'm having an issue with Entity Framework Core (v2.0.1) when transforming an entity model into a DTO. Basically it is, by any other version of the phrase, lazy loading when I don't want it to. Here's a simple .NET Core Console application (with the Microsoft.EntityFrameworkCore.SqlServer (2.0.1) package).

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace EfCoreIssue
{
    class Program
    {
        static void Main(string[] args)
        {
            var dbOptions = new DbContextOptionsBuilder<ReportDbContext>()
                .UseSqlServer("Server=.;Database=EfCoreIssue;Trusted_Connection=True;")
                .Options;

            // Create and seed database if it doesn't already exist.
            using (var dbContext = new ReportDbContext(dbOptions))
            {
                if (dbContext.Database.EnsureCreated())
                {
                    string alphas = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

                    foreach (char alpha in alphas)
                    {
                        var report = new Report { Title = $"Report { alpha }" };

                        for (int tagId = 0; tagId < 10; tagId++)
                            report.Tags.Add(new ReportTag { TagId = tagId });

                        dbContext.Reports.Add(report);
                        dbContext.SaveChanges();
                    }
                }
            }

            using (var dbContext = new ReportDbContext(dbOptions))
            {
                var reports = dbContext.Reports
                    .Select(r => new ReportDto
                    {
                        Id = r.Id,
                        Title = r.Title,
                        Tags = r.Tags.Select(rt => rt.TagId)
                    })
                    .ToList();
            }
        }
    }

    class ReportDbContext : DbContext
    {
        public DbSet<Report> Reports { get; set; }

        public ReportDbContext(DbContextOptions<ReportDbContext> options)
            : base(options) { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ReportTag>().HasKey(rt => new { rt.ReportId, rt.TagId });
        }
    }

    [Table("Report")]
    class Report
    {
        [Key]
        public int Id { get; set; }
        public string Title { get; set; }
        public virtual ICollection<ReportTag> Tags { get; set; }

        public Report()
        {
            Tags = new HashSet<ReportTag>();
        }
    }

    [Table("ReportTag")]
    class ReportTag
    {
        public int ReportId { get; set; }
        public int TagId { get; set; }
    }

    class ReportDto
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public IEnumerable<int> Tags { get; set; }
    }
}

现在，当 ToList （）方法被执行以检索数据，它正在执行以下SQL

Now when the ToList() method is executed to retrieve the data, it's executing the following SQL

SELECT [r].[Id], [r].[Title]
FROM [Report] AS [r]

如您所见，它并没有努力加入 [ReportTag] 表，如果您实际尝试读取<$ c $的值， c> ReportDto 上的标记属性，然后触发另一个SQL查询

As you can see, it has made no effort to join to the [ReportTag] table, and if you actually try and read the values of the Tags property on a ReportDto then it fires off another SQL query

SELECT [rt].[TagId]
FROM [ReportTag] AS [rt]
WHERE @_outer_Id = [rt].[ReportId]

现在，我知道EF Core不支持延迟加载，但这对我来说非常像延迟加载。在这种情况下，我不希望它延迟加载。我尝试将 var reports = dbContext.Reports 更改为 var reports = dbContext.Reports.Include（r => r.Tags）无效。

Now I know EF Core doesn't support lazy loading, but this looks very much like lazy loading to me. In this instance I don't want it to lazy load. I've tried changing var reports = dbContext.Reports to var reports = dbContext.Reports.Include(r => r.Tags) which has no effect.

我什至尝试更改 Tags = r.Tags.Select（rt => rt.TagId ）到 Tags = r.Tags.Select（rt => rt.TagId）.ToList（），但这只是触发了上面的辅助SQL最后再查询26次。

I've even tried changing Tags = r.Tags.Select(rt => rt.TagId) to Tags = r.Tags.Select(rt => rt.TagId).ToList() but that just fires off the above secondary SQL query a further 26 times.

最后我无奈地尝试将 var report = dbContext.Reports 更改为 var reports = dbContext.Reports.Include（r =>
r.Tags）.ThenInclude（（ReportTag rt）=> rt.TagId）但这会引发异常， ReportTag.TagId 不是导航属性。

Finally in desperation I tried changing var reports = dbContext.Reports to var reports = dbContext.Reports.Include(r => r.Tags).ThenInclude((ReportTag rt) => rt.TagId) but that understandably throws an exception that ReportTag.TagId isn't a navigation property.

有人对我能做什么有任何想法吗？它渴望加载到 ReportDto.Tags 属性中？

Does anyone have any ideas on what I can do so that it eager loads into the ReportDto.Tags property?

推荐答案

注意，目前包含集合投影的EF Core投影查询存在两个问题-（1）它们导致每个集合执行N个查询，（2）它们被懒惰地执行。

As you noticed, currently there are two problems with EF Core projection queries containing collection projections - (1) they cause execution of N queries per collection and (2) they are executed lazily.

问题（2）很奇怪，因为具有讽刺意味的是EF Core不支持惰性加载相关实体数据，而这种行为可以有效地将其用于投影。至少您可以使用 ToList（）或类似的方法来强制立即执行。

Problem (2) is weird, because ironically EF Core does not support lazy loading related entity data, while this behavior effectively implements it for projections. At least you can force immediate execution by using ToList() or similar, as you already found.

问题（ 1）目前无法解决。它由并根据（ Reduce n + 1个查询项）最终将在下一个EF Core 2.1版本中得到修复（改进）。

Problem (1) is unresolvable at this time. It's tracked by Query: optimize queries projecting correlated collections, so that they don't result in N+1 database queries #9282 and according to the Roadmap (Reduce n + 1 queries item) will eventually be fixed (improved) in the next EF Core 2.1 release.

我唯一想到的解决方法是较高的数据传输和内存使用成本）以使用急切加载并随后进行投影（在LINQ to Entities中）：

The only workaround I can think of is (with the cost of higher data transfer and memory usage) to use eager loading and do the projection afterwards (in the context of LINQ to Entities):

var reports = dbContext.Reports
    .Include(r => r.Tags) // <-- eager load
    .AsEnumerable() // <-- force the execution of the LINQ to Entities query
    .Select(r => new ReportDto
    {
        Id = r.Id,
        Title = r.Title,
        Tags = r.Tags.Select(rt => rt.TagId)
    })
    .ToList();

这篇关于实体框架核心在转换时延迟加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！