How do I use Moq and DbFunctions in unit tests to prevent a NotSupportedException?

Question

I'm currently attempting to run some unit tests on a query that is running through the Entity Framework. The query itself runs without any issues on the live version, but the unit tests are always failing.

I've narrowed this down to my usage of DbFunctions.TruncateTime, but I don't know of a way around this to get the unit tests to reflect what is happening on the live server.

Here is the method that I am using:

    public System.Data.DataTable GetLinkedUsers(int parentUserId)
    {
        var today = DateTime.Now.Date;

        var query = from up in DB.par_UserPlacement
                    where up.MentorId == mentorUserId
                        && DbFunctions.TruncateTime(today) >= DbFunctions.TruncateTime(up.StartDate)
                        && DbFunctions.TruncateTime(today) <= DbFunctions.TruncateTime(up.EndDate)
                    select new
                    {
                        up.UserPlacementId,
                        up.Users.UserId,
                        up.Users.FirstName,
                        up.Users.LastName,
                        up.Placements.PlacementId,
                        up.Placements.PlacementName,
                        up.StartDate,
                        up.EndDate,
                    };

        query = query.OrderBy(up => up.EndDate);

        return this.RunQueryToDataTable(query);
    }

If I comment out the lines with DbFunctions in, the tests all pass (except for the ones that are checking that only valid results for a given date are run).

Is there a way I can provide a mocked version of DbFunctions.TruncateTime to use in these tests? Essentially it should just be returning Datetime.Date, but that isn't available in EF queries.

Edit: Here's the test that's failing that uses the date check:

    [TestMethod]
    public void CanOnlyGetCurrentLinkedUsers()
    {
        var up = new List<par_UserPlacement>
        {
            this.UserPlacementFactory(1, 2, 1), // Create a user placement that is current
            this.UserPlacementFactory(1, 3, 2, false) // Create a user placement that is not current
        }.AsQueryable();

        var set = DLTestHelper.GetMockSet<par_UserPlacement>(up);

        var context = DLTestHelper.Context;
        context.Setup(c => c.par_UserPlacement).Returns(set.Object);

        var getter = DLTestHelper.New<LinqUserGetLinkedUsersForParentUser>(context.Object);

        var output = getter.GetLinkedUsers(1);

        var users = new List<User>();
        output.ProcessDataTable((DataRow row) => students.Add(new UserStudent(row)));

        Assert.AreEqual(1, users.Count);
        Assert.AreEqual(2, users[0].UserId);
    }

Edit 2: This is the message and debug trace from the test in question:

Test Result: Failed

Message: Assert.AreEqual failed. Expected:<1>. Actual:<0>

Debug Trace: This function can only be invoked from LINQ to Entities

From what I've read, this is because there isn't a LINQ to Entities implementation of this method that could be used in this place for the Unit Test, although there is on the live version (as it's querying an SQL server).

Answer 1

I know I'm late to the game, but a very simple fix is to write your own method which uses the DbFunction attribute. Then use that function instead of DbFunctions.TruncateTime.

[DbFunction("Edm", "TruncateTime")]
public static DateTime? TruncateTime(DateTime? dateValue)
{
    return dateValue?.Date;
}

Using this function will execute the EDM TruncateTime method when used by Linq to Entities and will run the provided code otherwise.

Answer 2

Thanks for all of the help everyone, I managed to track down a solution that worked for me after reading up on shims that qujck mentioned. After adding a fake assembly of EntityFramework, I was able to fix these tests by changing them to the following:

[TestMethod]
public void CanOnlyGetCurrentLinkedUsers()
{
    using (ShimsContext.Create())
    {
        System.Data.Entity.Fakes.ShimDbFunctions.TruncateTimeNullableOfDateTime =
            (DateTime? input) =>
            {
                return input.HasValue ? (DateTime?)input.Value.Date : null;
            };

        var up = new List<par_UserPlacement>
        {
            this.UserPlacementFactory(1, 2, 1), // Create a user placement that is current
            this.UserPlacementFactory(1, 3, 2, false) // Create a user placement that is not current
        }.AsQueryable();

        var set = DLTestHelper.GetMockSet<par_UserPlacement>(up);

        var context = DLTestHelper.Context;
        context.Setup(c => c.par_UserPlacement).Returns(set.Object);

        var getter = DLTestHelper.New<LinqUserGetLinkedUsersForParentUser>(context.Object);

        var output = getter.GetLinkedUsers(1);
    }

    var users = new List<User>();
    output.ProcessDataTable((DataRow row) => users.Add(new User(row)));

    Assert.AreEqual(1, users.Count);
    Assert.AreEqual(2, users[0].UserId);
}

Answer 3

There is a way to do it. Since unit testing of business logic is generally encouraged, and since it is perfectly OK for business logic to issue LINQ queries against application data, then it must be perfectly OK to unit test those LINQ queries.

Unfortunately, DbFunctions feature of Entity Framework kills our ability to unit test code that contains LINQ queries. Moreover, it is architecturally wrong to use DbFunctions in business logic, because it couples business logic layer to a specific persistence technology (which is a separate discussion).

Having said that, our goal is the ability to run LINQ query like this:

var orderIdsByDate = (
    from o in repo.Orders
    group o by o.PlacedAt.Date 
         // here we used DateTime.Date 
         // and **NOT** DbFunctions.TruncateTime
    into g
    orderby g.Key
    select new { Date = g.Key, OrderIds = g.Select(x => x.Id) });

In unit test, this will boil down to LINQ-to-Objects running against a plain array of entities arranged in advance (for example). In a real run, it must work against a real ObjectContext of Entity Framework.

Here is a recipe of achieving it - although, it requires a few steps of yours. I'm cutting down a real working example:

Step 1. Wrap ObjectSet<T> inside our own implementation of IQueryable<T> in order to provide our own intercepting wrapper of IQueryProvider.

public class EntityRepository<T> : IQueryable<T> where T : class
{
    private readonly ObjectSet<T> _objectSet;
    private InterceptingQueryProvider _queryProvider = null;

    public EntityRepository<T>(ObjectSet<T> objectSet)
    {
        _objectSet = objectSet;
    }
    IEnumerator<T> IEnumerable<T>.GetEnumerator()
    {
        return _objectSet.AsEnumerable().GetEnumerator();
    }
    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return _objectSet.AsEnumerable().GetEnumerator();
    }
    Type IQueryable.ElementType
    {
        get { return _objectSet.AsQueryable().ElementType; }
    }
    System.Linq.Expressions.Expression IQueryable.Expression
    {
        get { return _objectSet.AsQueryable().Expression; }
    }
    IQueryProvider IQueryable.Provider
    {
        get
        {
            if ( _queryProvider == null )
            {
                _queryProvider = new InterceptingQueryProvider(_objectSet.AsQueryable().Provider);
            }
            return _queryProvider;
        }
    }

    // . . . . . you may want to include Insert(), Update(), and Delete() methods
}

Step 2. Implement the intercepting query provider, in my example it is a nested class inside EntityRepository<T>:

private class InterceptingQueryProvider : IQueryProvider
{
    private readonly IQueryProvider _actualQueryProvider;

    public InterceptingQueryProvider(IQueryProvider actualQueryProvider)
    {
        _actualQueryProvider = actualQueryProvider;
    }
    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        var specializedExpression = QueryExpressionSpecializer.Specialize(expression);
        return _actualQueryProvider.CreateQuery<TElement>(specializedExpression);
    }
    public IQueryable CreateQuery(Expression expression)
    {
        var specializedExpression = QueryExpressionSpecializer.Specialize(expression);
        return _actualQueryProvider.CreateQuery(specializedExpression);
    }
    public TResult Execute<TResult>(Expression expression)
    {
        return _actualQueryProvider.Execute<TResult>(expression);
    }
    public object Execute(Expression expression)
    {
        return _actualQueryProvider.Execute(expression);
    }
}

Step 3. Finally, implement a helper class named QueryExpressionSpecializer, which would replace DateTime.Date with DbFunctions.TruncateTime.

public static class QueryExpressionSpecializer
{
    private static readonly MethodInfo _s_dbFunctions_TruncateTime_NullableOfDateTime = 
        GetMethodInfo<Expression<Func<DateTime?, DateTime?>>>(d => DbFunctions.TruncateTime(d));

    private static readonly PropertyInfo _s_nullableOfDateTime_Value =
        GetPropertyInfo<Expression<Func<DateTime?, DateTime>>>(d => d.Value);

    public static Expression Specialize(Expression general)
    {
        var visitor = new SpecializingVisitor();
        return visitor.Visit(general);
    }
    private static MethodInfo GetMethodInfo<TLambda>(TLambda lambda) where TLambda : LambdaExpression
    {
        return ((MethodCallExpression)lambda.Body).Method;
    }
    public static PropertyInfo GetPropertyInfo<TLambda>(TLambda lambda) where TLambda : LambdaExpression
    {
        return (PropertyInfo)((MemberExpression)lambda.Body).Member;
    }

    private class SpecializingVisitor : ExpressionVisitor
    {
        protected override Expression VisitMember(MemberExpression node)
        {
            if ( node.Expression.Type == typeof(DateTime?) && node.Member.Name == "Date" )
            {
                return Expression.Call(_s_dbFunctions_TruncateTime_NullableOfDateTime, node.Expression);
            }

            if ( node.Expression.Type == typeof(DateTime) && node.Member.Name == "Date" )
            {
                return Expression.Property(
                    Expression.Call(
                        _s_dbFunctions_TruncateTime_NullableOfDateTime, 
                        Expression.Convert(
                            node.Expression, 
                            typeof(DateTime?)
                        )
                    ),
                    _s_nullableOfDateTime_Value
                );
            }

            return base.VisitMember(node);
        }
    }
}

Of course, the above implementation of QueryExpressionSpecializer can be generalized to allow plugging in any number of additional conversions, allowing members of custom types to be used in LINQ queries, even though they are not known to Entity Framework.

Answer 4

Check out this answer: https://stackoverflow.com/a/14975425/1509728

To be honest, upon thinking about it I totally agree with the answer and generally follow the principle that my EF queries are tested against the database and only my application code is tested with Moq.

It looks like there is no elegant solution to using Moq for testing EF queries with your query above, while there are some hacky ideas out there. For example this one and the answer that follows it. Both seem like they could work for you.

Another approach to testing your queries would be one implemented on another project I worked on: Using VS out of box unit tests, each query (again refactored into its own method) test would be wrapped in a transaction scope. Then the project's test framework would take care of manually entering phony data into the db and the query would try to filter this phony data. At the end, the transaction is never completed so it is rolled back. Due to the nature of transaction scopes, this might not be an ideal scenario for a lot of projects. Most probably not on prod environments.

Otherwise if you must continue mocking functionality, you might want to consider other mocking frameworks.

Answer 5

Hmm, not sure but couldn't you do something like this?

context.Setup(s => DbFunctions.TruncateTime(It.IsAny<DateTime>()))
    .Returns<DateTime?>(new Func<DateTime?,DateTime?>(
        (x) => {
            /*  whatever modification is required here */
            return x; //or return modified;
        }));

Answer 6

since i hit the same problem recently, and opted for a simpler solution, wanted to post it here.. this solution requires no Shims, Mocking, nothing expansive etc.

1. Pass a 'useDbFunctions' boolean flag to your method with default value as true.
2. When your live code executes, your query will use DbFunctions and everything will work. Due to the default value, callers need not worry about it.
3. When your unit tests invoke the method to test, they can pass useDbFunctions: false.
4. In your method, you can make use the flag to compose your IQueryable.. if useDbFunctions is true, use the DbFunctions to add the predicate to the queryable. if useDbFunctions is false, then skip the DbFunctions method call, and do an explicit C# equivalent solution.

This way, your unit tests will check almost 95% of your method in parity with live code. You still have the delta of "DbFunctions" vs. your equivalent code, but be diligent about it and the 95% will look like a lot of gain.

public System.Data.DataTable GetLinkedUsers(int parentUserId, bool useDbFunctions = true)
{
    var today = DateTime.Now.Date;

    var queryable = from up in DB.par_UserPlacement
                where up.MentorId == mentorUserId;

    if (useDbFunctions) // use the DbFunctions
    {
     queryable = queryable.Where(up => 
     DbFunctions.TruncateTime(today) >= DbFunctions.TruncateTime(up.StartDate)
     && DbFunctions.TruncateTime(today) <= DbFunctions.TruncateTime(up.EndDate));
    }  
    else
    {
      // do db-functions equivalent here using C# logic
      // this is what the unit test path will invoke
      queryable = queryable.Where(up => up.StartDate < today);
    }                    

    var query = from up in queryable
                select new
                {
                    up.UserPlacementId,
                    up.Users.UserId,
                    up.Users.FirstName,
                    up.Users.LastName,
                    up.Placements.PlacementId,
                    up.Placements.PlacementName,
                    up.StartDate,
                    up.EndDate,
                };

    query = query.OrderBy(up => up.EndDate);

    return this.RunQueryToDataTable(query);
}

Unit tests will invoke the mthod as:

GetLinkedUsers(parentUserId: 10, useDbFunctions: false);

Because unit tests would have setup local DbContext entities, the C# logic/DateTime functions would work.

Answer 7

Use of Mocks ended sometime ago. Do not Mock, just connect to real DB. Regenerate/Seed DB on start of test. If you still want to go ahead with mocks then create your own method as given below. IT changes behaviour runtime. When using real DB it uses DB functions, else this method. Replace DBfunctions method in code with this method

public static class CanTestDbFunctions
{
    [System.Data.Entity.DbFunction("Edm", "TruncateTime")]
    public static DateTime? TruncateTime(DateTime? dateValue)
    {
        ...
    }
}

This is the real function that is called. And remember, time cannot be removed from DateTime object, live with midnight or create a string equivalent.