CODEWITHSUNDEEP
c#entity-frameworklinq-to-sqlentity-framework-core
Bogey
Bogey

Reputation: 5744

EntityFramework Core: Eager loading navigation properties of derived types

I'm using EntityFramework Core, and am trying to eager-load navigation properties that only exist on some derived type (all within a single query). Probably best to demonstrate with a simple example.

Assume you have some data structure like

class Transaction
{
    public Product product { get; set; }
    public DateTime date { get; set; }
}

abstract class Product
{
    public string Name { get; set; }
}

class PhysicalProduct : Product
{
    public Photo photo { get; set; }
}

class Service : Product
{
    public Person provider { get; set; }
}

And some DbContext

class MyContext : DbContext
{
    public DbSet<Transaction> Transactions;
}

How can I query MyContext.Transactions to return all transactions, and include (eager load) Transaction.product.photo (in case product is PhysicalProduct), and Transaction.product.provider (in case product is Service) ? As mentioned, trying to achieve this with only one query.

I've tried the following:

// This is conceptually what I want to achieve.
// Not very surprisingly, this will throw an InvalidCastException
Transactions
  .Include(x => ((PhysicalProduct)x.product).photo)
  .Include(x => ((Service)x.product).provider)
  .ToList();

// Based on http://stackoverflow.com/questions/7635152/entity-framework-eager-loading-of-subclass-related-objects
// Projection into an anonymous type, then transform back.
// doesn't work though, throws an InvalidOperationException, e.g.
// The property "photo" on entity type "Product" could not be found. Ensure that the property exists and has been included in the model.
// i.e. even though I wrapped this in a condition (x.product is PhysicalProduct), seems like EntityFramework still tries to execute or parse the statement thereafter even if the condition is not true.
var query = Transactions.Select(x => new
{
  _transaction = x,
  _physicalProductPhoto = (x.product is PhysicalProduct) ? ((PhysicalProduct)x.product).photo : null;
  _serviceProvider = (x.product is Service) ? ((Service)x.product).provider : null;
})
.ToList()  // Execute query. Exception will be thrown at this step.
.Select(x =>
{
  var result = x._transaction;

  if (x.product is PhysicalProduct)
    ((PhysicalProduct)x.product).photo = x._physicalProductPhoto;
  else if(x.product is Service)
    ((Service)x.product).provider = x._serviceProvider;

  return result;
})
.ToList();

Can anyone think of a way to achieve this? Thanks!

Upvotes: 3

Views: 2335

Answers (3)

user1650604
user1650604

Reputation: 1

I am to believe from the linked documentation that by using "ThenInclude". You are able to accomplish this. Sorry but I did not try it myself, so I can not verify that it works.

var blogs = context.Blogs
.Include(blog => blog.Posts)
    .ThenInclude(post => post.Author)
    .ThenInclude(author => author.Photo)
.Include(blog => blog.Owner)
    .ThenInclude(owner => owner.Photo)
.ToList();

https://docs.efproject.net/en/latest/querying/related-data.html

Upvotes: -1

Ivan Stoev
Ivan Stoev

Reputation: 205759

Yesterday I was fighting similar issue in EF6 - EF Eager fetching derived class. Currently EF Core is not better in that regard - in fact it's worse, because from the 3 EF6 workarounds only the #2 works here.

The workaround is:

It definitely can't be done with a single query. You need to execute the master query and materialize the result in memory. Then for each derived navigation type, collect the PKs and execute a query filtered by these keys. At the end, due to EF navigation property fixup you'll end up with all the navigation properties loaded.

var transactions = db.Transactions.Include(e => e.product).ToList();

var productIds = transactions.Where(e => e.product is PhysicalProduct)
    .Select(e => e.product.Id).Distinct();
db.BaseProducts.OfType<PhysicalProduct>().Include(e => e.photo)
    .Where(e => productIds.Contains(e.Id)).Load();

var serviceIds = transactions.Where(e => e.product is Service)
    .Select(e => e.product.Id).Distinct();
db.BaseProducts.OfType<Service>().Include(e => e.provider)
    .Where(e => serviceIds.Contains(e.Id)).Load();

Upvotes: 3

Arthur Vickers
Arthur Vickers

Reputation: 7533

This isn't yet supported in EF Core. See https://github.com/aspnet/EntityFramework/issues/3910 for the issue tracking it.

I believe the only workaround is to execute multiple queries and let the EF context do the fixup for you.

Upvotes: 3

Related Questions