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.

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.