Unit test error : This function can only be invoked from LINQ to Entities

Question

I am coding a MVC 5 internet application, and I have an expression as follows:

public Expression<Func<Account, bool>> IsExpiresDateTimeLessThanMinimumDaysLeftInFreeTrialSubscription(int minimumDaysLeftInSubscriptionForEmail)
{
    return Account => System.Data.Entity.DbFunctions.DiffHours(Account.freeTrialEndDate, DateTime.UtcNow) < minimumDaysLeftInSubscriptionForEmail;
}

When retrieving data from the database, the above expression completes correctly. However, when writing a unit test that uses the above expression I am getting the following error:

This function can only be invoked from LINQ to Entities

I gather that this is because the System.Data.Entity.DbFunctions.DiffHours function converts the expression into code that only a database system can understand.

Because of the above fact, is it possible to unit test the above expression when using a mock repository that uses a List rather than a DbSet? If not, how should I unit test any code that uses the expression? Is it possible to unit test the expression?

Thanks in advance.

Communize

Answer 1

When EF generates the SQL code, the only thing that matters is the System.Data.Entity.DbFunction attribute. The body of the method throws an exception, but is never called when using the real database.

To test this, you can create your own method with both the DbFunction attribute and an implementation. When running against the database, EF will generate SQL and ignore your code. When running a unit test, your code will be executed.

Your method would look something like this:

public static class TestableDbFunctions
{
    [System.Data.Entity.DbFunction("Edm", "DiffHours")]
    public static int? DiffHours(DateTime? dateValue1, DateTime? dateValue2)
    {
        if (!dateValue1.HasValue || !dateValue2.HasValue)
            return null;

        return (int)((dateValue2.Value - dateValue1.Value).TotalHours);
    }
}

The comparison code just for example, you'd want to be sure this matches the SQL behavior or else your tests will not be valid.

Once you have this, just change your code to use the new method:

return Account => TestableDbFunctions.DiffHours(Account.freeTrialEndDate, DateTime.UtcNow) < minimumDaysLeftInSubscriptionForEmail;

If you write a good test for this, it will catch the bug that you're passing in a number of days and comparing a number of hours.

Levee

Does the above code automatically determine if the code is run against a database or a list? If not, can this be coded into the function? I ask this as the expression is one of many in a where clause. – Communize

It will automatically have the correct behavior running against a database or a list. The actual determination is done by the implementation of IQueryable. List<T>.AsQueryable() will interpret the expression tree as LINQ to objects, and run the C# code against the in-memory list. The ObjectSet<T> implementation will interpret the expression tree to generate SQL, using the DbFunction attribute and ignoring the C# code. – Levee

For SqlFunctions.DatePart() I used the attribute [DbFunction("SqlServer", "DATEPART")] (note the intentionally capitalised second parameter to match precisely this value). – Urea