Reuse a LINQ To Entities statement

Question

I have the following Select expression that will be performed database-side:

        var model = new ProductListViewModel()
        {
            Products = products
                .Select(q => new ProductViewModel()
                {
                    // Other mapping
                    Name = q.LanguageKey.LanguageValues.FirstOrDefault(p => p.LanguageKeyID == currentLanguageID && p.Active).Value,
                })
                .OrderBy(q => q.Name)
        };

As you can see, the Name part is quite long, and other part of my program also use the same logic. Therefore, I would like to reuse it (not entire Select statement, just the Name mapping part).

I tried to write a function that only use the entity:

public static string GetProductName(Product product, int languageID)
{
    return product.LanguageKey.LanguageValues.AsQueryable()
        .FirstOrDefault(q => q.LanguageID == languageID && q.Active).Value;
}

However, on calling the method, I (obviously) receive the error

LINQ to Entities does not recognize the method {X} method, and this method cannot be translated into a store expression

I have searched through many articles on StackOverflow, but most solve the problem of reusing entire Select expression. Is it possible to reuse it? Or do I have to create database Stored Procedure or Function?

Answer 1

One solution is to use LINQKit.

First, you need to modify your method to return an expression like this:

public Expression<Func<Product, string>> CreateExpression(int languageID)
{
    return product => product.LanguageKey.LanguageValues.AsQueryable()
        .FirstOrDefault(q => q.LanguageID == languageID && q.Active).Value;
}

By the way, I don't think that you need to invoke .AsQueryable() here.

Then you can do the following:

var expression = CreateExpression(currentLanguageID);

var model = new ProductListViewModel()
{
    Products = products.AsExpandable()
        .Select(q => new ProductViewModel()
        {
            // Other mapping
            Name = expression.Invoke(q),
        })
        .OrderBy(q => q.Name)
};

Notice the .AsExpandable() call on the products variable.

This method is from LINQKit, and it creates a wrapper around the IQueryable (in your case products) to enable using expressions from inside your lambda expressions.

Also notice the Invoke method that is also coming from LINQKit. It allows you to use the expression inside your other expression, i.e., q => new ProductViewModel(....