Pfthb

Hi. I have three tables and I am trying to get group them and subgroup as well.

Table Sections
-------------
Id
SectionName


Table Categories
-------------
Id 
CategoryName
SectionRefId


Table Records
-------------
Id
RecordName
CategoryRefId

What I am trying to achieve is to group all Category by SectionName and group all Records by CategoryName and display them using a foreach loop.

I tried this using Linkpad the result it not what i exptected </p>


var result = from doc in Categories
 group doc by doc.SectionRefId into docSections
 select new 
 
 Name = docSections.Key,
 Group = from dl in Records
 group dl by dl.CategoryRefId into dlRecords
 select new 
 
 Name = dlRecords.Key,
 GroupRecords = dlocation
 
 ;


enter code here

You can do this. Need to some joins and group by:

var result = from category in Categories
 join section in Sections on category.Id equals section.ID
 join record in Records on category.Id equals record.CategoryRefId
 group category by section.SectionName into g
 group record by category.categoryName into p
 select new CategoryName = g.Key.CategoryName, SectionName = section.SectionName, RecordName = record.RecordName ;

If you've followed the entity framework code first conventions, your classes will have virtual ICollection<...> properties that will do the grouping for you:

class Section

 public int Id get; set; 
 public string SectionName get; set; 

 // every section has zero or more Categories (one-to-many)
 public virtual ICollection<Category> Categories get; set;


class Category

 public int Id get; set; 
 public string CategoryName get; set; 

 // every Category belongs to exactly one Section using foreign key:
 public int SectionId get; set; 
 public virtual Section Section get; set;

 // every Category has zero or more Records (one-to-many)
 public virtual ICollection<Record> Records get; set;


class Record

 public int Id get; set; 
 public string RecordName get; set; 

 // every Record belongs to exactly one Category
 public int CategoryId get; set; 
 public virtual Category Category get; set;

In entity framework the columns of the database tables are represented
by the non-virtual properties. The virtual properties represent the
relations between the tables

Note, it might be that you've got different identifiers for your tables and columns, the main thing is that you added the virtual properties

I am trying to achieve is to group all Category by SectionName and group all Records by CategoryName and display them using a foreach loop.

var results = myDbContext.Sections
 .Where (section => ...) // only if you don't want all Sections
 .Select(section => new
 
 // select only the properties you plan to use:
 Id = section.Id,
 Name = section.SectionName,

 // This section has zero or more categories:
 Categories = section.Categories
 .Where(category => ...) // only if you don't want all categories
 .Select(category => new
 
 // again, select only the properties you plan to use:
 Id = category.Id,
 ...
 // not needed, you already know the value:
 // SectionId = category.SectionId,

 // this category has zero or more Records:
 // you know the drill by now
 Records = category.Records
 .Where(record => ...) 
 .Select(record => new
 
 Id = record.Id,
 ...
 )
 .ToList(),
 )
 .ToList(),
 );

Entity framework knows your one-to-many relations and will do the proper GroupJoins for you