CODEWITHSUNDEEP
entity-frameworklinqexpressioncalculated-field
Afflatus
Afflatus

Reputation: 943

Entity Framework Where Clause for Calculated property

I have the following model:

public class Account
{
    public Guid Id{ get; set; }

    [Filterable]
    public string Code{ get; set; }

    [Filterable]
    public string AccountStatus { get; set; }
}

the value of the property AccountStatus is being calculated based on the value of StartDateand EndDate so in my Linq query in my repository I have the following:

public IEnumerable<Account> Get(QueryClause<Account> queryClause, out ForGetCollectionOptions forGetCollectionOptions)
{
        using (_dbContext)
        {
            var result = (
              from account in _dbContext.Accounts
              select new Account
              {
               Id = account.ID,
               Code = account.Code.Trim(),
               AccountStatus = StatusCalculator.GetStatus(account.StartDate, account.EndDate)
               }
           ApplyFilterClause(ref result, queryClause.FilterClause);
        }
}

for calculating the Account staus we have the following: public enum Status { Inactive, Active, Future }

public static class StatusCalculator
{
    public static string GetStatus(DateTime? startDate, DateTime? endDate)
    {
        var now = DateTime.Now;

        if (startDate != null && startDate > now)
        {
            return Status.Future.ToString().ToLower();
        }

        if (startDate != null && endDate == null)
        {
            return Status.Active.ToString().ToLower();
        }

        return Status.Inactive.ToString().ToLower();
    }
}

The problem is that as I try to filter the accountStatus of for example 'inactive' I get the following run-time warning:

warn: Microsoft.EntityFrameworkCore.Query[20500]
  The LINQ expression 'where (GetStatus([account].StartDate, [account].EndDate) == "inactive")' could not be translated and will be evaluated locally.
 warn: Microsoft.EntityFrameworkCore.Query[20500]
  The LINQ expression 'Count()' could not be translated and will be evaluated locally.

because the calculated property can't be translated, the query will be sent without that condition and retrieve all the data and then apply that filter locally which will damage the performance.

How can I add custom translation for this property for the where clause and sort and count?

Upvotes: 0

Views: 1106

Answers (1)

Marco
Marco

Reputation: 23937

Your business logic to calculate that property is currently simple enough, so it can be inlined:

public IEnumerable<Account> Get(QueryClause<Account> queryClause, out ForGetCollectionOptions forGetCollectionOptions)
{
        using (_dbContext)
        {
            var result = (
              from account in _dbContext.Accounts
              select new Account
              {
               Id = account.ID,
               Code = account.Code.Trim(),
               AccountStatus = (account.StartDate != null && account.EndDate == null)
                                  ? "Active"
                                  : (account.StartDate != null && account.StartDate > DateTime.Now) 
                                     ? "Future"
                                     : "Inactive"
               }
           ApplyFilterClause(ref result, queryClause.FilterClause);
        }
}

This should enable EF to convert this into a CASE statement like:

SELECT  foo
       ,(CASE 
          WHEN t.StartDate IS NOT NULL...
          WHEN t.StartDate IS NOT NULL AND t.StartDate > GetDate()
          ELSE ...
        )
FROM bar

Upvotes: 1

Related Questions