EntityFunctions.TruncateTime and unit tests

Question

I'm using System.Data.Objects.EntityFunctions.TruncateTime method to get date part of a datetime in my query:

if (searchOptions.Date.HasValue)
    query = query.Where(c => 
        EntityFunctions.TruncateTime(c.Date) == searchOptions.Date);

This method (I believe the same applies to other EntityFunctions methods) cannot be executed outside of LINQ to Entities. Executing this code in a unit test, which effectively is LINQ to objects, causes a NotSupportedException to be thrown:

System.NotSupportedException : This function can only be invoked from LINQ to Entities.

I'm using a stub for a repository with fake DbSets in my tests.

So how should I unit test my query?

Answer 1

You can't - if unit testing means that you are using a fake repository in memory and you are therefore using LINQ to Objects. If you test your queries with LINQ to Objects you didn't test your application but only your fake repository.

Your exception is the less dangerous case as it indicates that you have a red test, but probably actually a working application.

More dangerous is the case the other way around: Having a green test but a crashing application or queries which do not return the same results as your test. Queries like...

context.MyEntities.Where(e => MyBoolFunction(e)).ToList()

or

context.MyEntities.Select(e => new MyEntity { Name = e.Name }).ToList()

...will work fine in your test but not with LINQ to Entities in your application.

A query like...

context.MyEntities.Where(e => e.Name == "abc").ToList()

...will potentially return different results with LINQ to Objects than LINQ to Entities.

You can only test this and the query in your question by building integration tests which are using the LINQ to Entities provider of your application and a real database.

Edit

If you still want to write unit tests I think you must fake the query itself or at least expressions in the query. I could imagine that something along the lines of the following code might work:

Create an interface for the Where expression:

public interface IEntityExpressions
{
    Expression<Func<MyEntity, bool>> GetSearchByDateExpression(DateTime date);
    // maybe more expressions which use EntityFunctions or SqlFunctions
}

Create an implementation for your application...

public class EntityExpressions : IEntityExpressions
{
    public Expression<Func<MyEntity, bool>>
        GetSearchByDateExpression(DateTime date)
    {
       return e => EntityFunctions.TruncateTime(e.Date) == date;
       // Expression for LINQ to Entities, does not work with LINQ to Objects
    }
}

...and a second implementation in your Unit test project:

public class FakeEntityExpressions : IEntityExpressions
{
    public Expression<Func<MyEntity, bool>>
        GetSearchByDateExpression(DateTime date)
    {
        return e => e.Date.Date == date;
       // Expression for LINQ to Objects, does not work with LINQ to Entities
    }
}

In your class where you are using the query create a private member of this interface and two constructors:

public class MyClass
{
    private readonly IEntityExpressions _entityExpressions;

    public MyClass()
    {
        _entityExpressions = new EntityExpressions(); // "poor man's IOC"
    }

    public MyClass(IEntityExpressions entityExpressions)
    {
        _entityExpressions = entityExpressions;
    }

    // just an example, I don't know how exactly the context of your query is
    public IQueryable<MyEntity> BuildQuery(IQueryable<MyEntity> query,
        SearchOptions searchOptions)
    {
        if (searchOptions.Date.HasValue)
            query = query.Where(_entityExpressions.GetSearchByDateExpression(
                searchOptions.Date));
        return query;
    }
}

Use the first (default) constructor in your application:

var myClass = new MyClass();
var searchOptions = new SearchOptions { Date = DateTime.Now.Date };

var query = myClass.BuildQuery(context.MyEntities, searchOptions);

var result = query.ToList(); // this is LINQ to Entities, queries database

Use the second constructor with FakeEntityExpressions in your unit test:

IEntityExpressions entityExpressions = new FakeEntityExpressions();
var myClass = new MyClass(entityExpressions);
var searchOptions = new SearchOptions { Date = DateTime.Now.Date };
var fakeList = new List<MyEntity> { new MyEntity { ... }, ... };

var query = myClass.BuildQuery(fakeList.AsQueryable(), searchOptions);

var result = query.ToList(); // this is LINQ to Objects, queries in memory

If you are using a dependency injection container you could leverage it by injecting the appropriate implementation if IEntityExpressions into the constructor and don't need the default constructor.

I've tested the example code above and it worked.

Answer 2

As outlined in my answer to How to Unit Test GetNewValues() which contains EntityFunctions.AddDays function, you can use a query expression visitor to replace calls to EntityFunctions functions with your own, LINQ To Objects compatible implementations.

The implementation would look like:

using System;
using System.Data.Objects;
using System.Linq;
using System.Linq.Expressions;

static class EntityFunctionsFake
{
    public static DateTime? TruncateTime(DateTime? original)
    {
        if (!original.HasValue) return null;
        return original.Value.Date;
    }
}
public class EntityFunctionsFakerVisitor : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if (node.Method.DeclaringType == typeof(EntityFunctions))
        {
            var visitedArguments = Visit(node.Arguments).ToArray();
            return Expression.Call(typeof(EntityFunctionsFake), node.Method.Name, node.Method.GetGenericArguments(), visitedArguments);
        }

        return base.VisitMethodCall(node);
    }
}
class VisitedQueryProvider<TVisitor> : IQueryProvider
    where TVisitor : ExpressionVisitor, new()
{
    private readonly IQueryProvider _underlyingQueryProvider;
    public VisitedQueryProvider(IQueryProvider underlyingQueryProvider)
    {
        if (underlyingQueryProvider == null) throw new ArgumentNullException();
        _underlyingQueryProvider = underlyingQueryProvider;
    }

    private static Expression Visit(Expression expression)
    {
        return new TVisitor().Visit(expression);
    }

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        return new VisitedQueryable<TElement, TVisitor>(_underlyingQueryProvider.CreateQuery<TElement>(Visit(expression)));
    }

    public IQueryable CreateQuery(Expression expression)
    {
        var sourceQueryable = _underlyingQueryProvider.CreateQuery(Visit(expression));
        var visitedQueryableType = typeof(VisitedQueryable<,>).MakeGenericType(
            sourceQueryable.ElementType,
            typeof(TVisitor)
            );

        return (IQueryable)Activator.CreateInstance(visitedQueryableType, sourceQueryable);
    }

    public TResult Execute<TResult>(Expression expression)
    {
        return _underlyingQueryProvider.Execute<TResult>(Visit(expression));
    }

    public object Execute(Expression expression)
    {
        return _underlyingQueryProvider.Execute(Visit(expression));
    }
}
public class VisitedQueryable<T, TExpressionVisitor> : IQueryable<T>
    where TExpressionVisitor : ExpressionVisitor, new()
{
    private readonly IQueryable<T> _underlyingQuery;
    private readonly VisitedQueryProvider<TExpressionVisitor> _queryProviderWrapper;
    public VisitedQueryable(IQueryable<T> underlyingQuery)
    {
        _underlyingQuery = underlyingQuery;
        _queryProviderWrapper = new VisitedQueryProvider<TExpressionVisitor>(underlyingQuery.Provider);
    }

    public IEnumerator<T> GetEnumerator()
    {
        return _underlyingQuery.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return GetEnumerator();
    }

    public Expression Expression
    {
        get { return _underlyingQuery.Expression; }
    }

    public Type ElementType
    {
        get { return _underlyingQuery.ElementType; }
    }

    public IQueryProvider Provider
    {
        get { return _queryProviderWrapper; }
    }
}

And here is a usage sample with TruncateTime:

var linq2ObjectsSource = new List<DateTime?>() { null }.AsQueryable();
var visitedSource = new VisitedQueryable<DateTime?, EntityFunctionsFakerVisitor>(linq2ObjectsSource);
// If you do not use a lambda expression on the following line,
// The LINQ To Objects implementation is used. I have not found a way around it.
var visitedQuery = visitedSource.Select(dt => EntityFunctions.TruncateTime(dt));
var results = visitedQuery.ToList();
Assert.AreEqual(1, results.Count);
Assert.AreEqual(null, results[0]);