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; }
}
}
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]
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]
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.
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.
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.
Does anyone have any ideas on what I can do so that it eager loads into the ReportDto.Tags
property?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…