EF Core: "The LINQ expression could not be translated" when using explicit casting

Question

I have a very simple entity called Product. It contains an explicit casting to the DTO type ProductListDTO.

public class Product
{
    public Guid ID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string? Notes { get; set; }

    public static explicit operator ProductListDTO (Product p)
    {
        return new ProductListDTO
        {
            ID = p.ID,
            Name = p.Name,
            Description = p.Description
        };
    }
}

Below are 4 ways that accessed the data. One of them (Which is the desired one) fails.

(1) - this works

var allProducts = db.Products.Select(x => new ProductListDTO
            {
                Name = x.Name,
                Description = x.Description
            }).ToList();

(2) - this also works

var allProducts_Casting = db.Products.Select(x => (ProductListDTO)x).ToList();

(3) - and this also works

var filteredProducts = db.Products.Select(x => new ProductListDTO
            {
                Name = x.Name,
                Description = x.Description
            })
                .Where(x => x.Name == "Product 1")
                .ToList();

(4) - this however does NOT work

var filteredProducts_Casting = db.Products.Select(x => (ProductListDTO) x)
            .Where(x => x.Name == "Product 1")
            .ToList();

The exception is:

System.InvalidOperationException : The LINQ expression 'DbSet()
.Where(p => !(p.IsDeleted))
.Where(p => ((ProductListDTO)p).Name == "Product 1")' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Some context

My only goal is to have a single place where I do the conversion between Product to ProductListDTO and that it can be re-used. And I prefer a simple approach like below.

Desired:

public IQueryable<ProductListDTO> OdataList()
{
    return db.Products.Select(x => (ProductListDTO)x);
}

public IQueryable<PurchaseOrderItemListDTO> OdataList()
{
    return db.PurchaseOrderItems.Select(p => new PurchaseOrderItemListDTO
                                             {
                                                 ID = p.ID,
                                                 UnitPrice = p.UnitPrice,
                                                 Quantity = p.Quantity,
                                                 Product = (ProductListDTO) p.Product,
                                             });
}

But this is where I run into the problem I explained earlier

Trying to avoid

There's also the below approach that works fine but it's more complex and hard to explain to other developers:

public static readonly Expression<Func<Product, ProductListDTO>> ProductListQuery = p => new ProductListDTO
{
   ID = p.ID,
   Name = p.Name,
   Description = p.Description
};

public IQueryable<ProductListDTO> OdataList()
{
    return db.Products.Select(ProductListQuery);
}

public IQueryable<PurchaseOrderItemListDTO> OdataList()
{
    var productListing = ProductListQuery.Compile();

    return db.PurchaseOrderItems.Select(p => new PurchaseOrderItemListDTO
                                             {
                                                 ID = p.ID,
                                                 UnitPrice = p.UnitPrice,
                                                 Quantity = p.Quantity,
                                                 Product = productListing(p.Product),
                                             });
}

Answer 1

This is basically the problem explained here EF Core queries all columns in SQL when mapping to object in Select (and in many other places). My favorite solution (from the link) is based on DelegateDecompiler package and EF Core 7.0+ LINQ query expression tree interception.

So, all you need to make desired (4) working is as follows:

1. Install the aforementioned package
2. Add the following in a code file of your choice:

using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Query;

namespace Microsoft.EntityFrameworkCore
{
    public static class DelegateDecompilerDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder AddDelegateDecompiler(this DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.AddInterceptors(new DelegateDecompilerQueryPreprocessor());
    }
}

namespace Microsoft.EntityFrameworkCore.Query
{
    using DelegateDecompiler;
    using System.Linq.Expressions;
    using static ReplacingExpressionVisitor;

    public class DelegateDecompilerQueryPreprocessor :
        DecompileExpressionVisitor, IQueryExpressionInterceptor
    {
        Expression IQueryExpressionInterceptor.QueryCompilationStarting(
            Expression queryExpression, QueryExpressionEventData eventData)
            => Visit(queryExpression);

        protected override Expression VisitUnary(UnaryExpression node)
        {
            if (node.NodeType == ExpressionType.Convert && node.Method != null)
            {
                var decompiled = node.Method.Decompile();
                return Replace(decompiled.Parameters[0], Visit(node.Operand), decompiled.Body);
            }
            return base.VisitUnary(node);
        }
    }
}

It is the code from the linked answer extended for handling Convert expressions which are the expression equivalent of C# casts. The whole idea is to replace the cast with the actual code, so the EF Core query translator can "see" it, thus turning the non working case #4 into working case #3.

3. Add the following to your derived DbContext class OnConfiguring override:

optionsBuilder.AddDelegateDecompiler();

And that's all. Now all your casts will be translated. Enjoy :)