Resolved Create a Multi-Layer Data Model from a MySQL Query

madaxe2020

Well-known member
Joined
Sep 7, 2020
Messages
50
Programming Experience
5-10
How can i expand the linq query to create additional levels of grouped data.

thanks

Madaxe

Linq Query:
namespace ConsoleApp3
{
    class Program
    {
        static void Main(string[] args)
        {
            List<SignInReport> SignInReports = new List<SignInReport>();
            SignInReports.Add(new SignInReport { UserName = "Bob", EventName = "SignIn", EventTime = new DateTime(2020,1,18,6,0,0) });
            SignInReports.Add(new SignInReport { UserName = "Bob", EventName = "SignOut", EventTime = new DateTime(2020, 1, 18, 10, 0, 0) });
            SignInReports.Add(new SignInReport { UserName = "Bob", EventName = "SignIn", EventTime = new DateTime(2020, 1, 18, 11, 30, 0) });
            SignInReports.Add(new SignInReport { UserName = "Bob", EventName = "SignOut", EventTime = new DateTime(2020, 1, 18, 16, 30, 0) });
            SignInReports.Add(new SignInReport { UserName = "Charlie", EventName = "SignIn", EventTime = new DateTime(2020, 1, 18, 6, 0, 0) });
            SignInReports.Add(new SignInReport { UserName = "Charlie", EventName = "SignOut", EventTime = new DateTime(2020, 1, 18, 15, 30, 0) });
            SignInReports.Add(new SignInReport { UserName = "Charlie", EventName = "SignIn", EventTime = new DateTime(2020, 1, 19, 6, 30, 0) });
            SignInReports.Add(new SignInReport { UserName = "Charlie", EventName = "SignOut", EventTime = new DateTime(2020, 1, 19, 17, 45, 0) });

            IEnumerable<SignInReportGrouping> SignInData = SignInReports.GroupBy(u => u.UserName)
                                                  .Select(group => new SignInReportGrouping {   UserName = group.Key,
                                                                                                SignInReports = group.ToList() })
                                                  .ToList();
        }
    }

    public class SignInReport
    {
        public string UserName { get; set; }
        public string EventName { get; set; }
        public DateTime EventTime { get; set; }
    }

    public class SignInReportGrouping
    {
        public string UserName { get; set; }
        public IEnumerable<SignInReportDay> SignInReportDay { get; set; }
    }

    public class SignInReportDay
    {
        public IEnumerable<SignInReport> SignInReports { get; set; }
    }
}
 
ultimately i want to take my dateset which is a single object and convert it into a more complex object structure by grouping into new classes.

SignInData (root)
SignInReportGrouping( grouped by user)
SignInReportDay(grouped by day)


root
Bob
2020,1,18
{ 2020, 1, 18, 6, 0, 0 , SignIn }
{ 2020, 1, 18, 10, 0, 0 , SignOut }
{ 2020, 1, 18, 11, 30, 0 , SignIn }
{ 2020, 1, 18, 16, 30, 0, SignOut }
Charlie
2020, 1, 18
{ 2020, 1, 18, 6, 0, 0 , SignIn }
{ 2020, 1, 18, 15, 0, 0 , SignOut }
2020, 1, 19
{ 2020, 1, 19, 6, 30, 0 , SignIn }
{ 2020, 1, 19, 17, 45, 0, SignOut }
 
I believe that this is what you're looking for:
C#:
using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApp3
{
    class Program
    {
        static void Main()
        {
            var signInReports = new List<SignInReport>
                                {
                                    new SignInReport
                                    {
                                        UserName = "Bob",
                                        EventName = "SignIn",
                                        EventTime = new DateTime(2020, 1, 18, 6, 0, 0)
                                    },
                                    new SignInReport
                                    {
                                        UserName = "Bob",
                                        EventName = "SignOut",
                                        EventTime = new DateTime(2020, 1, 18, 10, 0, 0)
                                    },
                                    new SignInReport
                                    {
                                        UserName = "Bob",
                                        EventName = "SignIn",
                                        EventTime = new DateTime(2020, 1, 18, 11, 30, 0)
                                    },
                                    new SignInReport
                                    {
                                        UserName = "Bob",
                                        EventName = "SignOut",
                                        EventTime = new DateTime(2020, 1, 18, 16, 30, 0)
                                    },
                                    new SignInReport
                                    {
                                        UserName = "Charlie",
                                        EventName = "SignIn",
                                        EventTime = new DateTime(2020, 1, 18, 6, 0, 0)
                                    },
                                    new SignInReport
                                    {
                                        UserName = "Charlie",
                                        EventName = "SignOut",
                                        EventTime = new DateTime(2020, 1, 18, 15, 30, 0)
                                    },
                                    new SignInReport
                                    {
                                        UserName = "Charlie",
                                        EventName = "SignIn",
                                        EventTime = new DateTime(2020, 1, 19, 6, 30, 0)
                                    },
                                    new SignInReport
                                    {
                                        UserName = "Charlie",
                                        EventName = "SignOut",
                                        EventTime = new DateTime(2020, 1, 19, 17, 45, 0)
                                    }
                                };

            var signInData = signInReports.GroupBy(u => u.UserName)
                                          .Select(group1 => new SignInReportGrouping
                                                            {
                                                                UserName = group1.Key,
                                                                SignInReportDays = group1.GroupBy(g1 => g1.EventTime.Date)
                                                                                         .Select(group2 => new SignInReportDay
                                                                                                           {
                                                                                                               EventDate = group2.Key,
                                                                                                               SignInReports = group2.ToList()
                                                                                                           })
                                                            })
                                          .ToList();

            foreach (var signInReportGrouping in signInData)
            {
                Console.WriteLine(signInReportGrouping.UserName);

                foreach (var signInReportDay in signInReportGrouping.SignInReportDays)
                {
                    Console.WriteLine(signInReportDay.EventDate.ToShortDateString());

                    foreach (var signInReport in signInReportDay.SignInReports)
                    {
                        Console.WriteLine($"{signInReport.EventName}: {signInReport.EventTime}");
                    }
                }
            }

            Console.ReadLine();
        }
    }

    public class SignInReport
    {
        public string UserName { get; set; }
        public string EventName { get; set; }
        public DateTime EventTime { get; set; }
    }

    public class SignInReportGrouping
    {
        public string UserName { get; set; }
        public IEnumerable<SignInReportDay> SignInReportDays { get; set; }
    }

    public class SignInReportDay
    {
        public DateTime EventDate { get; set; }
        public IEnumerable<SignInReport> SignInReports { get; set; }
    }
}
 
public class SignInReport { public string UserName { get; set; } public string EventName { get; set; } public DateTime EventTime { get; set; } } public class SignInReportGrouping { public string UserName { get; set; } public IEnumerable<SignInReportDay> SignInReportDays { get; set; } } public class SignInReportDay { public DateTime EventDate { get; set; } public IEnumerable<SignInReport> SignInReports { get; set; } }

Thats exactly what i wanted great thanks,

Madaxe
 
Back
Top Bottom