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

c# entity-framework moq unit-testing

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).

1
28
1/23/2018 5:34:54 PM

Accepted Answer

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);
}
16
1/31/2014 9:15:40 AM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow