c# - Entity Framework Core is lazy loading when transforming

Question

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?

Answer 1

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.

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.

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.

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();