using System.Collections.Generic;
private static List<Client> clientsTable = new List<Client>();
private static List<ClientLocation> clientLocationsTable = new List<ClientLocation>();
private static List<LoadComplete> loadsTable = new List<LoadComplete>();
private static List<Invoice> invoicesTable = new List<Invoice>();
private static List<ClientLocationNote> notesTable = new List<ClientLocationNote>();
public static void Main()
private static void QueryData()
var dataModel = new Data();
var dataOne = from client in clientsTable
join clientLocTable in clientLocationsTable on client.ClientId equals clientLocTable.ClientId into clientLocJoin
from clientLoc in clientLocJoin.DefaultIfEmpty()
join loadTable in loadsTable on (clientLoc == null ? -1 : clientLoc.LocationId) equals loadTable.LocationId into loadJoin
from load in loadJoin.DefaultIfEmpty()
join invoiceTable in invoicesTable on (load == null ? -1 : load.LoadId) equals invoiceTable.LoadId into invoiceJoin
from invoice in invoiceJoin.DefaultIfEmpty()
join noteTable in notesTable on (clientLoc == null ? -1 : clientLoc.LocationId) equals noteTable.LocationId into noteJoin
from note in noteJoin.DefaultIfEmpty()
group new { client, clientLoc, load, invoice, note } by new { client.ClientId, clientLoc.LocationId, load.LoadId } into grp
select new { grp.Key.ClientId, grp.Key.LocationId, grp.Key.LoadId, Items = grp.OrderBy(g => g.load.LoadId).Take(2) };
dataModel.Clients = dataOne.GroupBy(d => new { d.client.ClientId, d.client.Name })
.Select(g => new ClientModel()
ClientId = g.Key.ClientId,
ClientLocations = g.Where(cg => cg.clientLoc != null).GroupBy(d => new
.Select(g1 => new ClientLocationModel()
LocationId = g1.Key.LocationId,
ClientId = g1.Key.ClientId,
Pickups = g1.Where(lg1 => lg1.load != null).GroupBy(lg => new
.Select(lg => new PickupModel()
LocationId = lg.Key.LocationId,
PickupItems = lg.Where(pig => pig.invoice != null).GroupBy(pig => new
pig.clientLoc.LocationId,
.Select(pig => new PickupItemModel()
LocationId = lg.Key.LocationId
Notes = g1.Where(lg1 => lg1.note != null).GroupBy(ng => new
.Select(ng => new ClientLocationNoteModel()
LocationId = ng.Key.LocationId,
foreach(var clientId in dataModel.Clients)
Console.WriteLine($"Client: {clientId.ClientId}");
foreach(var clientLocation in clientId.ClientLocations)
Console.WriteLine($"\t\tClientLocation: {clientLocation.LocationId}");
foreach(var load in clientLocation.Pickups)
Console.WriteLine($"\t\t\t\tLoad: {load.LoadId}");
foreach(var loadItem in load.PickupItems)
Console.WriteLine($"\t\t\t\t\t\tLoad Item: {loadItem.Id}");
foreach(var clNote in clientLocation.Notes)
Console.WriteLine($"\t\t\t\tNote: {clNote.NoteId}");
private static void PopulateTables()
clientsTable.Add(new Client() { ClientId = 1, Name = "Client 1" });
clientLocationsTable.Add(new ClientLocation() { LocationId = 1, ClientId = 1, Name = "Location 1" });
clientLocationsTable.Add(new ClientLocation() { LocationId = 2, ClientId = 1, Name = "Location 2" });
loadsTable.Add(new LoadComplete() { LoadId = 1, LocationId = 1, Name = "Load 1" });
loadsTable.Add(new LoadComplete() { LoadId = 2, LocationId = 1, Name = "Load 2" });
loadsTable.Add(new LoadComplete() { LoadId = 3, LocationId = 2, Name = "Load 3" });
loadsTable.Add(new LoadComplete() { LoadId = 4, LocationId = 2, Name = "Load 4" });
loadsTable.Add(new LoadComplete() { LoadId = 5, LocationId = 2, Name = "Load 5" });
invoicesTable.Add(new Invoice() { InvoiceId = 1, LoadId = 1, Name = "Load Item 1" });
invoicesTable.Add(new Invoice() { InvoiceId = 2, LoadId = 1, Name = "Load Item 2" });
invoicesTable.Add(new Invoice() { InvoiceId = 3, LoadId = 3, Name = "Load Item 3" });
invoicesTable.Add(new Invoice() { InvoiceId = 4, LoadId = 3, Name = "Load Item 4" });
invoicesTable.Add(new Invoice() { InvoiceId = 5, LoadId = 3, Name = "Load Item 5" });
notesTable.Add(new ClientLocationNote() { NoteId = 1, LocationId = 1, Note = "Note 1" });
notesTable.Add(new ClientLocationNote() { NoteId = 2, LocationId = 1, Note = "Note 2" });
notesTable.Add(new ClientLocationNote() { NoteId = 3, LocationId = 1, Note = "Note 3" });
notesTable.Add(new ClientLocationNote() { NoteId = 4, LocationId = 2, Note = "Note 4" });
notesTable.Add(new ClientLocationNote() { NoteId = 5, LocationId = 2, Note = "Note 5" });
clientsTable.Add(new Client() { ClientId = 2, Name = "Client 2" });
clientLocationsTable.Add(new ClientLocation() { LocationId = 3, ClientId = 2, Name = "Location 3" });
loadsTable.Add(new LoadComplete() { LoadId = 6, LocationId = 3, Name = "Load 6" });
loadsTable.Add(new LoadComplete() { LoadId = 7, LocationId = 3, Name = "Load 7" });
loadsTable.Add(new LoadComplete() { LoadId = 8, LocationId = 3, Name = "Load 8" });
loadsTable.Add(new LoadComplete() { LoadId = 9, LocationId = 3, Name = "Load 9" });
loadsTable.Add(new LoadComplete() { LoadId = 10, LocationId = 3, Name = "Load 10" });
clientsTable.Add(new Client() { ClientId = 3, Name = "Client 3" });
clientsTable.Add(new Client() { ClientId = 4, Name = "Client 4" });
clientLocationsTable.Add(new ClientLocation() { LocationId = 4, ClientId = 4, Name = "Location 4" });
clientsTable.Add(new Client() { ClientId = 5, Name = "Client 5" });
clientLocationsTable.Add(new ClientLocation() { LocationId = 5, ClientId = 5, Name = "Location 5" });
notesTable.Add(new ClientLocationNote() { NoteId = 6, LocationId = 5, Note = "Note 6" });
notesTable.Add(new ClientLocationNote() { NoteId = 7, LocationId = 5, Note = "Note 7" });
notesTable.Add(new ClientLocationNote() { NoteId = 8, LocationId = 5, Note = "Note 8" });
public List<ClientModel> Clients { get; set; }
public int ClientId { get; set; }
public string Name { get; set; }
public List<ClientLocationModel> ClientLocations { get; set; }
this.ClientLocations = new List<ClientLocationModel>();
public class ClientLocationModel
public int LocationId { get; set; }
public int ClientId { get; set; }
public string Name { get; set; }
public List<PickupModel> Pickups { get; set; }
public List<ClientLocationNoteModel> Notes { get; set; }
public ClientLocationModel()
this.Pickups = new List<PickupModel>();
this.Notes = new List<ClientLocationNoteModel>();
public int LoadId { get; set; }
public int LocationId { get; set; }
public List<PickupItemModel> PickupItems { get; set; }
this.PickupItems = new List<PickupItemModel>();
public class PickupItemModel
public int Id { get; set; }
public int LoadId { get; set; }
public int LocationId { get; set; }
public class ClientLocationNoteModel
public int NoteId { get; set; }
public int LocationId { get; set; }
public string Note { get; set; }
public int ClientId { get; set; }
public string Name { get; set; }
public class ClientLocation
public int LocationId { get; set; }
public int ClientId { get; set; }
public string Name { get; set; }
public class LoadComplete
public int LoadId { get; set; }
public int LocationId { get; set; }
public string Name { get; set; }
public int InvoiceId { get; set; }
public int LoadId { get; set; }
public string Name { get; set; }
public class ClientLocationNote
public int NoteId { get; set; }
public int LocationId { get; set; }
public string Note { get; set; }