Create a Multi-Layer Data Model from a MySQL Query

mjeeves

I have the following table, i can join this table to others to build a bigger dataset but I only wnat to run one query to get the data and then build a data model shown below. I can build a Single model but i want to do more like a JSON serialization. Can anybody show me a LINQ or other method that would be efficient and could be expanded to use generics? Thanks Madaxe School Class Student Bakers 19A Bob Bakers 19A Jim Bakers 17A Gary Bakers 17A Stuart DataClass IEnumerable Schools SchoolClass IEnumerable Classes StudentClass

mjeeves

I found this one example is this the best method / approach?

namespace ConsoleApp3
{
class Program
{
static void Main(string[] args)
{
List CustomerList = new List();
CustomerList.Add(new Customer { ID = 1, Name = "One", GroupID = 1 });
CustomerList.Add(new Customer { ID = 2, Name = "Two", GroupID = 1 });
CustomerList.Add(new Customer { ID = 3, Name = "Three", GroupID = 2 });
CustomerList.Add(new Customer { ID = 4, Name = "Four", GroupID = 1 });
CustomerList.Add(new Customer { ID = 5, Name = "Five", GroupID = 3 });
CustomerList.Add(new Customer { ID = 6, Name = "Six", GroupID = 3 });

        IEnumerable CustomerGroupings = CustomerList.GroupBy(u => u.GroupID)
                                              .Select(group => new CustomerGrouping { GroupID = group.Key, Customers = group.ToList() })
                                              .ToList();
    }
}

public class Customer
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int GroupID { get; set; }
}

public class CustomerGrouping
{
    public int GroupID { get; set; }
    public IEnumerable Customers { get; set; }
}

Lost User

mjeeves wrote:

s this the best method / approach

It all depends on what you are trying to achieve.

mjeeves

here is another example data set, i want to go one more level down and group by username and EventTime, but i don't know how to expand the linq, plus i will have to remove the time stamp from the event dattime

namespace ConsoleApp3
{
class Program
{
static void Main(string[] args)
{
List SignInReports = new List();
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 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 { get; set; }
}

public class SignInReportDay
{
    public IEnumerable SignInReports { get; set; }
}

}

Richard Deeming

Since you're using Linq-to-Objects, that's simple enough to do:

IEnumerable<SignInReportGrouping> SignInData = SignInReports
.GroupBy(u => u.UserName)
.Select(user => new SignInReportGrouping
{
UserName = user.Key,
SignInReportDay = user
.GroupBy(u => u.EventTime.Date)
.Select(day => new SignInReportDay
{
Day = day.Key,
SignInReports = day.ToList(),
})
.ToList(),
})
.ToList();

You'll probably want to add the Day property to your SignInReportDay class so you know which day it represents:

public class SignInReportDay
{
public DateTime Day { get; set; }
public IEnumerable<SignInReport> SignInReports { get; set; }
}

NB: If you're using Entity Framework 6, you probably won't be able to use the .Date property. For EF6, use DbFunctions.TruncateTime:

.GroupBy(u => DbFunctions.TruncateTime(u.EventTime))

For EF Core, the .Date property should work correctly.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer