linq to entity datediff fails unit test, but not sql data

Question

I have a LINQ to entity unit test that needs to get employee's timeOut - timeIn for each day they worked in a week and sum them together. The method is fine when pulling sql data, but in the unit test I get the error: "This function can only be invoked from LINQ to Entities."

This seems like a very simple thing, but I've tried a lot of things and can't get the test to pass. Don't want to add a lot of code just for testing because that would defeat the purpose of the test(mentioned in questions similar from years back). I was wondering if there are any newer ways to get this to work without adding huge chunks of code that will complicate the method.

method:

    #region CalculateTimeToPay(payrollWeekEnd)
    public IQueryable<CalculatedHours> CalculateTimeToPay(DateTime payrollWeekStart, DateTime payrollWeekEnd)
    {
        var uow = container.Resolve<WWIncomeTaxDataHandlerUnitOfWork>();

        var employeeHours = (from time in uow.Accounting.Repository.Find<V_Time>()
                             where (payrollWeekEnd >= time.PayTimeIn && time.PayTimeIn >= payrollWeekStart)
                             group time by new { EmployeeID = time.EmployeeID} into empGroup
                             select new CalculatedHours
                             {
                                 EmployeeID = empGroup.Key.EmployeeID,
                                 //TimeToPay = (empGroup.Sum(x => (SqlFunctions.DateDiff("second", x.PayTimeIn, x.PayTimeOut)) / 60.0 / 60.0)) ?? 0
                                 //TimeToPay = empGroup.Sum(x => x.PayTimeOut - x.PayTimeIn) //empGroup.Sum(x => (SqlFunctions.DateDiff("second", x.PayTimeIn, x.PayTimeOut)) / 60.0 / 60.0) ?? 0

                                 TimeToPay = (empGroup.Where(x => x.EmployeeID == empGroup.Key.EmployeeID).Sum(x => (SqlFunctions.DateDiff("second", x.PayTimeIn, x.PayTimeOut))/60.0/60.0)) ?? 0
                             });

        //var employeeHours = (from time in uow.Accounting.Repository.Find<V_Time>()
        //                     where (payrollWeekEnd >= time.PayTimeIn && time.PayTimeIn >= payrollWeekStart)
        //                     group time by new { time.EmployeeID } into empGroup
        //                     select new CalculatedHours
        //                     {
        //                         EmployeeID = empGroup.Key.EmployeeID,
        //                         //TimeToPay = (empGroup.Sum(x => (SqlFunctions.DateDiff("second", x.PayTimeIn, x.PayTimeOut)) / 60.0 / 60.0)) ?? 0
        //                         TimeToPay = (empGroup.Sum(x => (SqlFunctions.DateDiff("second", x.PayTimeIn, x.PayTimeOut)) / 60.0 / 60.0)) ?? 0

        //                         //TimeToPay = (empGroup.Where(x => x.EmployeeID == empGroup.Key.EmployeeID).Sum(x => (SqlFunctions.DateDiff("second", x.PayTimeIn, x.PayTimeOut))/60.0/60.0)) ?? 0
        //                     }); 

        return employeeHours;
    }
    #endregion

test:

    #region CalculateTimeToPay_IntegerEmployeeIDAndDateTimeWeekOfDate_IQueryableTimeToPay
    [TestMethod]
    public void CalculateTimeToPay_IntegerEmployeeIDAndDateTimeWeekOfDate_IQueryableTimeToPay()
    {
        //Arrange
        var service = new WWIncomeTaxDataHandlerService("ProdSQL");
        DateTime payrollWeekEnd = Convert.ToDateTime("2017-01-15");
        DateTime payrollWeekStart = Convert.ToDateTime("2017-01-09");

        var V_Times = new List<V_Time>()
        {
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-05 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-05 16:00:00.000"), EmployeeID = 999 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-05 08:30:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-05 16:00:00.000"), EmployeeID = 777 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-05 08:45:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-05 16:00:00.000"), EmployeeID = 888 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-10 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-10 16:00:00.000"), EmployeeID = 999 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-10 08:30:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-10 16:00:00.000"), EmployeeID = 777 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-10 08:45:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-10 16:00:00.000"), EmployeeID = 888 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-11 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-11 16:00:00.000"), EmployeeID = 999 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-11 00:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-11 07:00:00.000"), EmployeeID = 777 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-11 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-11 16:00:00.000"), EmployeeID = 888 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-12 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-12 16:00:00.000"), EmployeeID = 999 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-12 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-12 16:44:00.000"), EmployeeID = 777 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-12 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-12 16:00:00.000"), EmployeeID = 888 },
            new V_Time { PayTimeIn = Convert.ToDateTime("2017-01-14 08:00:00.000"), PayTimeOut = Convert.ToDateTime("2017-01-14 16:07:00.000"), EmployeeID = 777 }
        };

        var mockRepository = new Mock<IRepository>();
        mockRepository.Setup(x => x.Find<V_Time>()).Returns(V_Times.AsQueryable());
        var builder = BuildContainer();
        builder.Register(x => mockRepository.Object).As<IRepository>();
        var container = builder.Build();

        var itrs = container.Resolve<WWIncomeTaxDataHandler.Domain.WWIncomeTaxDataHandlerService>();

        var expected = new List<CalculatedHours>()
        {
            new CalculatedHours { EmployeeID = 999, TimeToPay = 24 },
            new CalculatedHours { EmployeeID = 777, TimeToPay = 31.35},
            new CalculatedHours { EmployeeID = 888, TimeToPay = 23.25}
        };

        //Act
        var actual = itrs.CalculateTimeToPay(payrollWeekStart, payrollWeekEnd).ToList();

        //Assert
        var compareLogic = new CompareLogic();
        var result = compareLogic.Compare(actual, expected);
        Assert.IsTrue(result.AreEqual, result.DifferencesString);
    }
    #endregion

Pranay Rana · Answer 1 · 2017-12-19T17:09:46.287

1

for getting date different in second for in memory collection use like this

var diffInSeconds = (dateTime1 - dateTime2).TotalSeconds;

function you are using is ment to work with sql database not with in memory collection.

its failing because you are relying value which float or decimal, i suggest you try something like Math.Round(val,2) and check result however comparing two decimal or float is always problem, better put value which will give you integer value without fraction part.

Below is one example of double comparison, this leads to ouput value false same thing is happing in your case.

// Initialize two doubles with apparently identical values
double double1 = .33333;
double double2 = 1/3;
// Compare them for equality
Console.WriteLine(double1.Equals(double2));    // displays false

you can check here : Double.Equals Method (Double)

I am taking aout this part of your code

TimeToPay = (empGroup.Where(x => x.EmployeeID == 
 empGroup.Key.EmployeeID).Sum(x => (SqlFunctions.DateDiff("second",
 x.PayTimeIn, x.PayTimeOut))/60.0/60.0)) ?? 0

edited Dec 19 '17 at 17:09

answered Dec 19 '17 at 16:38

Pranay Rana

175,020
35
237
263

No, it's failing because of the canonical method `SqlFunctions.DateDiff` call which is supported only in L2E queries. – Ivan Stoev Dec 19 '17 at 17:01
@IvanStoev - i am not saying it failing because of that function i am saying it failing because of this calcuation `diffvalue/60.0/60.0)) ?? 0`..means because of division you are doing – Pranay Rana Dec 19 '17 at 17:02
And I am saying it's failing because of that function :) – Ivan Stoev Dec 19 '17 at 17:03
@IvanStoev - `SqlFunctions.DateDiff` function is ment to use with database only ...you should use it with the in momery collection data.. – Pranay Rana Dec 19 '17 at 17:04

Balah · Answer 2 · 2017-12-19T19:44:59.837

1

You've mentioned LINQ to entities; as such, I'd recommend trying Dbfunctions in your tests and code instead (I've had this issue happen to me before and that switch made all the difference. Here's a related question which leads to other related questions: SqlFunctions vs DbFunctions)

However, DbFunctions doesn't have DateDiff, so you'll have to use DiffSeconds in your case.

Your line would thus change to:

TimeToPay = (empGroup.Where(x => x.EmployeeID == empGroup.Key.EmployeeID).Sum(x => (DbFunctions.DiffSeconds(x.PayTimeIn, x.PayTimeOut))/60.0/60.0)) ?? 0

Note: This will only work in tests where the query provider is adequately mocked out. Preferrable with Effort.EF6 (here's a post on how to plug it in), or EF Core's in-memory database. You could go about trying to mock your own provider, but that would be a terrible idea just to get these tests working.

Also, once you've switched to an adequate in-memory data context, instead of mocking out the Find() method, you'd save your list to the database and then query it as you normally would have.

edited Dec 19 '17 at 19:44

answered Dec 19 '17 at 17:23

Balah

2,530
2
16
24

This has the same issue as OP - *"This function can only be invoked from LINQ to Entities."* when executed in unit test. – Ivan Stoev Dec 19 '17 at 18:00
this returns the same error. Could I be referencing incorrectly? `TimeToPay = (empGroup.Where(x => x.EmployeeID == empGroup.Key.EmployeeID).Sum(x => (System.Data.Entity.DbFunctions.DiffSeconds(x.PayTimeIn, x.PayTimeOut)) / 60.0 / 60.0)) ?? 0` – jigglymig TheMighty Dec 19 '17 at 18:09
Oh... Yes your reference is probably right. I had made a big assumption - in my tests, I was using [Effort.EF6](https://www.nuget.org/packages/Effort.EF6/) to mock out my DbContext which works pretty well. When I try to mock out the repository in the way I think you've done it I get the exact same error :( back to the drawing board. PS, have you tried Effort? – Balah Dec 19 '17 at 19:05
I have not. Is it possible to use both as queryable and combine? I have ~15 other unit tests/methods that are using repositories. I could change them if it is more robust, though this is the last piece of the puzzle. So preferably not haha. – jigglymig TheMighty Dec 19 '17 at 19:47
Unfortunately you'd have to change all those tests since `AsQueryable()` on a `List` does not return the correct provider (it should return `System.Data.Entity.Linq.QueryProvider`). On the plus side though, since its the last piece of the puzzle, its the last time you'd have to make a change ;) – Balah Dec 19 '17 at 20:28

linq to entity datediff fails unit test, but not sql data

2 Answers2