Eager Loading with LinqKit and EFCore

Question

B"H

C#sharps inclusion of linq right into the language is one of its most powerful features. It is also what makes Entity Framework such an enticing option for working with a database.

Unfortunately due to typing restrictions its often difficult to create centralized expressions to use throughout your project/solution.

One answer to that question is LinqKit. This was working great for me in EF 6.x. However when I moved to EF Core, I am facing a showstopper.

Instead of properly compiled expressions which should then be converted into SQL statement. What I am getting are function like expressions which don't include their sub expressions in the SQL that they generate. Instead they create AsyncLinqOperatorProvider.EnumerableAdapters which .Net tries to execute (out of an async context) when you access the IEnumerables a few lines later.

So the question is: How do I get EF Core to execute the entire expression as one SQL statement and return a complete materialized object?

Given two classes

    class OrderItemDTO
    {
        public string OrderItemName { get; set; }
    }
    class OrderDTO
    {
        public string OrderName { get; set; }
        public ICollection<OrderItemDTO> OrderItems { get; set; }
    }

I would like to create a global expressions somewhere

    public static Expression<Func<Order, OrderDTO>> ToDTO = x => new OrderDTO
    {
        OrderName = x.Name,
        OrderItems = x.Items.Select(y => new OrderItemDTO { OrderItemName = y.Name })
    };

Which I would then use somewhere as var orders = await db.Orders.AsExpandable().Select(ToDTO).ToListAsync(); Expecting a fully materialized OrderDTO.

Instead what I get is a DTO with OrderItems as a AsyncLinqOperatorProvider.EnumerableAdapter which causes a race condition when executed

`db.AsExpandable()` doesn't sound correct. `OrderItems = x.Items.Select(y => new OrderItemDTO { OrderItemName = y.Name })` does not compile (`ICollection` cannot be assigned from `IEnumerable`). [Adding .ToList()](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1#optimization-of-correlated-subqueries) at the end would probably solve both compile and runtime issue. — Ivan Stoev, Jul 16 '19 at 19:24

score 1 · Answer 1 · answered Jul 16 '19 at 15:27

1

Call AsQueryable() and then ToList() on your inner collections - EF will then be able to correctly treat this as full projection:

public static Expression<Func<Order, OrderDTO>> ToDTO = x => new OrderDTO
{
    OrderName = x.Name,
    OrderItems = x.Items.AsQueryable().Select(y => new OrderItemDTO { OrderItemName = y.Name }).ToList()
};

answered Jul 16 '19 at 15:27

Vidmantas Blazevicius

4,652
2
11
30

2

Why the call to `AsQueriable()`? – Paulo Morgado Jul 16 '19 at 22:53

Eager Loading with LinqKit and EFCore

1 Answers1