Nesting EF Core Projection in EF Core Projection

Question

Can I have a projection method/property, which I can use in another projection method/property?

I have Domain Entities Customer and Sale.

public class Customer : IEntity<long>
{
    public long Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Sale> Sales { get; set; }
}

public class Sale : IEntity<long>
{
    public long Id { get; set; }
    public decimal UnitPrice { get; set; }
    public int Quantity  { get; set; }
    public decimal TotalPrice  { get; set; }
    public DateTimeOffset Date { get; set; }

    public long CustomerId { get; set; }
    public virtual Customer Customer { get; set; }

    public long EmployeeId { get; set; }
    public virtual Employee Employee { get; set; }

    public long ProductId { get; set; }
    public virtual Product Product { get; set; }

I know I can nest a projection as follows:

public class GetCustomerQuery : IGetCustomerQuery
{
    private readonly IAppDbContext _context;

    public GetCustomerQuery(IAppDbContext context)
    {
        _context = context;
    }

    public Task<CustomerModel> ExecuteAsync(long id)
    {
        return _context
            .Customers
            .Where(x => x.Id == id)
            .Select(GetEmployeeProjection())
            .SingleOrDefaultAsync();
    }

    private Expression<Func<Domain.Customers.Customer, CustomerModel>> GetEmployeeProjection()
    {
        return x => new CustomerModel
        {
            Id = x.Id,
            Name = x.Name,
            Sales = x.Sales.Select(y => new Sale
            {
                Employee = y.Employee.Name,
                Product = y.Product.Name,
                ProductCount = y.Quantity,
                TotalPrice = y.TotalPrice
            })
        };
    }
}

But can I separate the projections in two different method, f.e.:

private Expression<Func<Domain.Customers.Customer, CustomerModel>> GetEmployeeProjection()
{
    return x => new CustomerModel
    {
        Id = x.Id,
        Name = x.Name,
        Sales = x.Sales.Select(GetSaleProjection())
    };
}

private Expression<Func<Domain.Sales.Sale, Sale>> GetSaleProjection()
{
    return y => new Sale
    {
        Employee = y.Employee.Name,
        Product = y.Product.Name,
        ProductCount = y.Quantity,
        TotalPrice = y.TotalPrice
    };
}

And is it more common to write the projection as a method or as a property?

EDIT: 2020-12-14 @joakimriedel, adding .AsQueryable() does kind of work, but I also need to add .ToList(), else a runtime exception was thrown (EF Core 5.0.1), as @Ivan Stoev did mention.

        public Task<CustomerModel> ExecuteAsync(long id)
        {
            return _context
                .Customers
                .Where(x => x.Id == id)
                .Select(_projection)
                .SingleOrDefaultAsync();
        }

        private static readonly Expression<Func<Domain.Customers.Customer, CustomerModel>> _projection =
            x => new CustomerModel
            {
                Id = x.Id,
                Name = x.Name,
                Sales = x.Sales
                    .AsQueryable()
                    .Select(_salesProjection)
                    .ToList()
            };

        private static readonly Expression<Func<Domain.Sales.Sale, Sale>> _salesProjection =
             x => new Sale
             {
                 Employee = x.Employee.Name,
                 Product = x.Product.Name,
                 ProductCount = x.Quantity,
                 TotalPrice = x.TotalPrice
             };

I won't mark this as duplicate of [EF Core queries all columns in SQL when mapping to object in Select](https://stackoverflow.com/questions/62115690/ef-core-queries-all-columns-in-sql-when-mapping-to-object-in-select/62138200#62138200), but would suggest to take a look at the solution there, which is one of the possible ways (and IMHO the most natural) to solve this common problem with query expressions and the lack of native solution. With that approach, you would write the mapping code as regular extension methods and the DelegateDecompiler plugin will do the necessary "translation". — Ivan Stoev, Dec 11 '20 at 16:19
@joakimriedel In some limited scenarios, yes. For instance, in your example `GetSaleProjection()` must be called outside the expression tree, the expression stored in variable and the variable used inside query. Which is not applicable when the method needs other arguments from the query. Shortly, `AsQueryable()` is more like workaround than a general solution. — Ivan Stoev, Dec 14 '20 at 09:56
@IvanStoev I know that applied for earlier versions but from at least 3.1 I've been able to use expressions like this without storing in a variable first. — joakimriedel, Dec 14 '20 at 10:01
@joakimriedel Could be, I won't argue against. Still it is a workaround which works in very limited scenarios and also only in some versions of the framework. Also passing related parameters is still not possible. Also EFC5.0 requires you to add `.ToList()`, otherwise you get exception saying something like he final projection is queryable, but must be enumerable etc. Which of course is a bug, but just proves the workaround is unreliable. Solutions which "expand" parts of the expression tree by replacing property access and method calls with their content have no such defects. — Ivan Stoev, Dec 14 '20 at 10:38

joakimriedel · Accepted Answer · 2020-12-14T19:54:38.633

Yes, but you would need to modify your expression slightly.

EF Core 3.1

private Expression<Func<Domain.Customers.Customer, CustomerModel>> GetEmployeeProjection()
{
    return x => new CustomerModel
    {
        Id = x.Id,
        Name = x.Name,
        Sales = x.Sales.AsQueryable().Select(GetSaleProjection())
    };
}

EF Core 5.0

private Expression<Func<Domain.Customers.Customer, CustomerModel>> GetEmployeeProjection()
{
    return x => new CustomerModel
    {
        Id = x.Id,
        Name = x.Name,
        Sales = x.Sales.AsQueryable().Select(GetSaleProjection()).ToList()
    };
}

The extra AsQueryable() is necessary since the IEnumerable implementation of Select only accepts Func<Domain.Sales.Sale, Sale> but IQueryable supports Expression<Func<Domain.Sales.Sale, Sale>>.

Forcing AsQueryable is useful also if you want to use expression predicates in subqueries such as Any().

Nesting EF Core Projection in EF Core Projection

1 Answers1

EF Core 3.1

EF Core 5.0