Creating a specific dataset with LINQ

Question

I have trouble with creating one specific "dataset" from list of objects which I get via Dapper from database. So the thing is about daily attendancies in company, so user comes and click and goes out and click and we have one table row like this: sql table So I have a model class like this:

    public class Attendance
    {
        public int UserId { get; set; }
        public string FullName { get; set; }
        public DateTime DateTimeUtcIn { get; set; }
        public DateTime DateTimeUtcOut { get; set; }
        public string DayOfTheWeek { get; set; }
        public int LocationId { get; set; }
        public TimeSpan TotalTime { get; set; }
        public byte StatusId { get; set; }
        public TimeSpan BreakTime { get; set; }
    }

and then after a call to the database I have result like this IEnumerable<Attendace>:

var result = await _platformDB.Con.QueryAsync<Models.Attendance.Attendance>(query);

then I created a simple viewmodel like this:

    public class Time
    {
        public DateTime DateTimeUtcIn { get; set; }
        public DateTime DateTimeUtcOut { get; set; }
    }

    public class AttendaceViewModel
    {
        public int UserId { get; set; }
        public string FullName { get; set; }
        public string Date { get; set; }
        public string DayOfTheWeek { get; set; }
        public List<Time> TimesInAndOut { get; set; }
        public string Locations { get; set; }
        public string TotalTime { get; set; } //need to calculate
        public string Status { get; set; }
        public string BreakTime { get; set; } //need to calculate
     }

and here comes the QUESTION now: how can I create List<AttendaceViewModel> from my result grouped by UserId and calculate TotalTime for one day (sum of all dateTimeUtcIn + dateTimeUtcOut) and breaktime for this day and also populate this List<Time> TimesInAndOut in here? I have really no idea.

Answer 1

I think i know what you need:

List<AttendaceViewModel> resultList = result
    .GroupBy(a => (a.UserId, a.FullName, DateIn: a.DateTimeUtcIn.Date, a.DayOfTheWeek, a.LocationId, a.StatusId))
    .Select(ag => new AttendaceViewModel
    {
        UserId = ag.Key.UserId,
        FullName = ag.Key.FullName,
        Date = ag.Key.DateIn.ToString(),
        DayOfTheWeek = ag.Key.DayOfTheWeek,
        Locations = ag.Key.LocationId.ToString(),
        Status = ag.Key.StatusId.ToString(),
        TotalTime = new TimeSpan(ag.Sum(a => (a.DateTimeUtcOut - a.DateTimeUtcIn).Ticks)).ToString(),
        TimesInAndOut = ag.Select(a => new Time { DateTimeUtcIn = a.DateTimeUtcIn, DateTimeUtcOut = a.DateTimeUtcOut }).ToList(),
        BreakTime = CalcBreaks(ag.Select(a => (a.DateTimeUtcIn, a.DateTimeUtcOut))).ToString()
    })
    .ToList();

For the BreakTime i used this helper method:

private static TimeSpan CalcBreaks(IEnumerable<(DateTime inTime, DateTime outTime)> times)
{
    if (!times.Skip(1).Any())
        return TimeSpan.Zero;

    TimeSpan totalBreakTime = TimeSpan.Zero;
    TimeSpan lastLeaveTime = times.First().outTime.TimeOfDay;

    foreach(var attendanceTime in times.OrderBy(at => at.inTime).ThenBy(at => at.outTime).Skip(1))
    {
        TimeSpan breakTime = attendanceTime.inTime.TimeOfDay - lastLeaveTime;
        totalBreakTime += breakTime;
        lastLeaveTime = attendanceTime.outTime.TimeOfDay;
    }

    return totalBreakTime;
}