[asp.net] - Query trên các computed property không hỗ trợ LINQ

In a recent project at work I was stunned to realize that:

LINQ and Entity Framework don’t support querying on properties computed from other fields

So what do we do now? Luckily there is still a way

• 1. Computed Property
• 2. Simple LINQ
• 3. Solution
  • 3.1. [Slow performance] Calling ToList
  • 3.2. [DRY Principle violated] Writing out the expression
• 4. The best solution
• 5. Make life easier

1. Computed Property

A property with only a get, where the returned value is computed from other properties

public class TestViewModel
{
    [Required]
    [MaxLength(10, ErrorMessage = "Length must fewer than {1}")]
    public string FirstName { get; set; }
 
    [Required]
    [StringLength(10)]
    public string LastName { get; set; }
 
    // Computed Property
    [NotMapped]
    public string FullName => FirstName + LastName;
}

By database design standards, a column must hold data that cannot be derived from other data. The [NotMapped] Attribute serves that purpose. EF won’t generate code creating a FullName column if you use code first, and won’t try to find a FullName column in the table if you use database first

2. Simple LINQ

To query some value in the Database with Entity Framework, you can use LINQ very simply like this

// TableNameWithS is your table name in plural
 
var names = dbContext.TableNameWithS.Where(x => x.FirstName.Contains("test"));

But that same code throws an error if you try to use it with the FullName Property

// BUG BUG BUG

var names = dbContext.TableNameWithS.Where(x => x.FullName.Contains("test"));

3. Solution

TL;DR: The best solution

3.1. [Slow performance] Calling ToList

var names = dbContext.TableNameWithS.ToList().Where(x => x.FullName.Contains("test"));

Calling ToList makes Entity Framework hit the database, execute whatever query came before, and only then run your LINQ with the computed property

Its drawback is that EF pulls more results than needed, hurting the app’s performance

3.2. [DRY Principle violated] Writing out the expression

Another way: instead of using the computed property, we write that property’s expression straight into the query

var names = dbContext.TableNameWithS.Where(x => (x.FirstName + x.LastName).Contains("test"));

The drawback of this approach is that you violated the “DRY” principle - Don’t repeat yourself. One expression written twice. If you later change that expression in one place, you’ll have to change it in the other too. If you forget -> BUG, immediately

The DRY principle is stated as “Every piece of knowledge must have a single, unambiguous, authoritative representation within a system”

Source: https://en.wikipedia.org/wiki/Don%27t_repeat_yourself

4. The best solution

DelegateDecompiler - an extremely powerful library that decompiles a computed property’s expression and translates it into LINQ, which EF then translates into an SQL statement.

Its drawback is that it can’t translate when you use methods or classes you defined yourself, outside the .NET Framework

Using it couldn’t be easier

Step 1: install the DelegateDecompiler nuget

Install-Package DelegateDecompiler

Step 2: decorate the property with the [Computed] attribute

public class TestViewModel
{
    [Required]
    [MaxLength(10, ErrorMessage = "Length must fewer than {1}")]
    public string FirstName { get; set; }
 
    [Required]
    [StringLength(10)]
    public string LastName { get; set; }
 
    // Computed Property
    [NotMapped]
    [Computed]
    public string FullName => FirstName + LastName;
}

Step 3: call the Decompile method

var names = dbContext.TableNameWithS
                     .ToList()
                     .Where(x => x.FullName.Contains("test")).Decompile();

This library even supports async and EF’s advanced functions like Include and AsNoTracking with the DelegateDecompiler.EntityFramework extension

5. Make life easier

You can also configure asp to handle [NotMapped] properties automatically

Create a Configuration class

public class DelegateDecompilerConfiguration : DefaultConfiguration
{
    public override bool ShouldDecompile(MemberInfo memberInfo)
    {
        // Automatically decompile all NotMapped members
        return base.ShouldDecompile(memberInfo) || memberInfo.GetCustomAttributes(typeof(NotMappedAttribute), true).Length > 0;
    }
}

Then register it in the Startup method like this

DelegateDecompiler.Configuration
                  .Configure(new DelegateDecompilerConfiguration());