Creating a specific dataset with LINQ-CodePudding

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.

CodePudding user response：

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;
}

CodePudding user response：

You probably won't be able accomplish that with just LINQ. You should be able to loop over the items returned in the results that you just fetched, and do your logic there.

Implement a function like this, and call it after retrieving your results. You could even handle the database querying in this function too if you'd like, then just call the function and get back your list.

private List<AttendanceViewModel> GetAttendanceViewModels((whatever type 'results' is goes here) {type} results)
{
    var output = new List<AttendanceViewModel>();
    foreach(result in results)
    {
        var totalTime = CalculateTotalTimeForOneDay(result);
        var breakTime = CalculateBreakTime(result);
        result.TotalTime = totalTime;
        result.BreakTime = breakTime;
    }
    return output.GroupBy(u => u.UserId)
}