Entity Framework Core 在转换时延迟加载 [英] Entity Framework Core is lazy loading when transforming
问题描述
我在将实体模型转换为 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] 表，如果您真的尝试读取 Tags 属性的值，一个 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 reports = 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) 目前无法解决.它由 查询:优化投影相关集合的查询，以便它们不会导致 N+1数据库查询 #9282 并根据 Roadmap(减少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();
这篇关于Entity Framework Core 在转换时延迟加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
