c# - 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.