Calculate DateTime Difference Sum within Group Linq to Entity

Question

I have a unit test that isn't returning anything and I was curious if my group or calculation within the method is causing the list to remain at 0 count. Any help or point in the right direction would be appreciated. Thank you!

unit test:

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

        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 = 888, TimeToPay = 31.35},
            new calculatedHours { EmployeeID = 777, TimeToPay = 23.25}
        };

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

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

Method:

            #region CalculateTimeToPay(payrollWeekEnd)
    public IQueryable 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 { time.EmployeeID } into empGroup
                             select new calculatedHours
                             {
                                 EmployeeID = empGroup.Key.EmployeeID,
                                 TimeToPay = empGroup.Where(x => x.EmployeeID == empGroup.Key.EmployeeID).Sum (x => ((x.PayTimeOut - x.PayTimeIn).TotalSeconds) / 60 / 60)
                             }).ToList(); 


        return employeeHours.AsQueryable();
    }
    #endregion

note the tolist and back to queryable was for faster testing

Answer 1

You're doing it very complicated with a bunch of anonymous datatypes. You could try it a little bit more C#-style:

public class CalculatedHours
{
    public int EmployeeId { get; set; }
    public TimeSpan TimeToPay { get; set; }
}

public class PayTime
{
    public DateTime PayTimeIn { get; set; }
    public DateTime PayTimeOut { get; set; }
    public int EmployeeId { get; set; }

    public TimeSpan CalculateTimeToPay(DateTime payrollWeekStart, DateTime payrollWeekEnd)
    {
        DateTime realIn = payrollWeekStart > this.PayTimeIn ? payrollWeekStart : this.PayTimeIn;
        DateTime realOut = payrollWeekEnd < this.PayTimeOut ? payrollWeekEnd : this.PayTimeOut;

        return realOut - realIn;
    }

    public static void Test()
    {
        List<PayTime> someData = new List<PayTime>() { };

        DateTime payrollWeekEnd = Convert.ToDateTime("2017-01-22");
        DateTime payrollWeekStart = Convert.ToDateTime("2017-01-16");

        IEnumerable<CalculatedHours> result = someData.GroupBy(g => g.EmployeeId)
            .Select(s => new CalculatedHours() { EmployeeId = s.Key, TimeToPay = new TimeSpan(s.Sum(a => a.CalculateTimeToPay(payrollWeekStart, payrollWeekEnd).Ticks)) });
       // Do some assertions
    }
}

Btw. your Testdata is wrong. payrollWeekStart and payrollWeekEnd are too late (2017-01-16 to 2017-01-22 Data is max 2017-01-14). Additionaly you don't calculate parttime payments..