Use Expression<Func<T, TResult>> as select part in EF

Question

I have this property in my model class:

[NotMapped]
public static Expression<Func<Product, bool>> ProductColorHasStockExp = (x => x.ProductColors.Any(c => !c.IsDeleted && (c.Stock > c.SellCount + c.WarningPoint)));

I can use it in OrderBy and it works fine:

.ThenByDescending(Product.ProductColorHasStockExp)
.ThenByDescending(Product.FinalPriceExp)

But I can't use it in the Select part as shown here:

.Select(p => new
             {
                 p.Title,
                 p.TitleURL,
                 FinalPrice = Product.FinalPriceExp
                 HasStock = Product.ProductColorHasStockExp,
                 SellCount = Product.SellCountExp,
             }).ToList();

Of course I can use something like this:

FinalPrice = p.ProductColors == null ||
             !p.ProductColors.Any(c => !c.IsDeleted) ? 0 : Convert.ToInt32(p.ProductColors.FirstOrDefault(c => !c.IsDeleted).Price - ((float)p.ProductColors.FirstOrDefault(c => !c.IsDeleted).Price * p.ProductColors.FirstOrDefault(c => !c.IsDeleted).Discount / 100)),

and this works fine, but I need a way to reuse things so if the formula changes, I just change a part of code.

Ah and it is not .NET Core - it is the "old" ASP.NET MVC

Thanks

Answer 1

EF doesn't know how to translate a custom method call (which Product.SellCountExp basically is) into SQL when it's embedded into another expression (i.e. the entire LINQ query).

The best option to solve this (I think) is to create a DTO class and use AutoMapper to map SellCount to Product.SellCountExp, etc.

class ProductDto
{
    public string Title { get; set; }
    public string TitleURL { get; set; }
    public int FinalPrice { get; set; }
    public bool HasStock { get; set; }
    public int SellCount { get; set; }
}

Automapper configuration:

class ProductMappingProfile : AutoMapper.Profile
{
    public ImportProfile()
    {
        CreateMap<Product, ProductDto>()
            .ForMember(dest => dest.FinalPrice, m => m.MapFrom(Product.FinalPriceExp))
            .ForMember(dest => dest.HasStock, m => m.MapFrom(Product.ProductColorHasStockExp))
            .ForMember(dest => dest.SellCount, m => m.MapFrom(Product.SellCountExp));
    }
}

Example usage:

using AutoMapper;
using AutoMapper.QueryableExtensions;

...

var config = new MapperConfiguration(cfg => cfg.AddProfile<ProductMappingProfile>());

var dtos = context.Products
    .ProjectTo<ProductDto>(config).ToList();

I assume you're using EF6 (classic). If so, you'll have to replace Convert.ToInt32 by (int).

There are libraries that are able to convert method calls to expressions, but they usually require some addition to DTO classes or to the LINQ query to make them work. AutoMapper has become a first-class citizen in the .net stack, making it my preferred library.