How to group and subgroup and get data from a linq query









up vote
0
down vote

favorite













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









share|improve this question





















  • Do you have any sql query for the same?
    – Gauravsa
    Nov 11 at 22:59










  • No sql query, I am trying to figure it out only using linq. trying the solution below.
    – Christ K
    Nov 11 at 23:08














up vote
0
down vote

favorite













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









share|improve this question





















  • Do you have any sql query for the same?
    – Gauravsa
    Nov 11 at 22:59










  • No sql query, I am trying to figure it out only using linq. trying the solution below.
    – Christ K
    Nov 11 at 23:08












up vote
0
down vote

favorite









up vote
0
down vote

favorite












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









share|improve this question














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






c# entity-framework linq model-view-controller






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 11 at 22:18









Christ K

1




1











  • Do you have any sql query for the same?
    – Gauravsa
    Nov 11 at 22:59










  • No sql query, I am trying to figure it out only using linq. trying the solution below.
    – Christ K
    Nov 11 at 23:08
















  • Do you have any sql query for the same?
    – Gauravsa
    Nov 11 at 22:59










  • No sql query, I am trying to figure it out only using linq. trying the solution below.
    – Christ K
    Nov 11 at 23:08















Do you have any sql query for the same?
– Gauravsa
Nov 11 at 22:59




Do you have any sql query for the same?
– Gauravsa
Nov 11 at 22:59












No sql query, I am trying to figure it out only using linq. trying the solution below.
– Christ K
Nov 11 at 23:08




No sql query, I am trying to figure it out only using linq. trying the solution below.
– Christ K
Nov 11 at 23:08












2 Answers
2






active

oldest

votes

















up vote
0
down vote













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 ;





share|improve this answer



























    up vote
    0
    down vote













    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






    share|improve this answer




















      Your Answer






      StackExchange.ifUsing("editor", function ()
      StackExchange.using("externalEditor", function ()
      StackExchange.using("snippets", function ()
      StackExchange.snippets.init();
      );
      );
      , "code-snippets");

      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "1"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253806%2fhow-to-group-and-subgroup-and-get-data-from-a-linq-query%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      0
      down vote













      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 ;





      share|improve this answer
























        up vote
        0
        down vote













        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 ;





        share|improve this answer






















          up vote
          0
          down vote










          up vote
          0
          down vote









          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 ;





          share|improve this answer












          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 ;






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 11 at 22:58









          Gauravsa

          2,0221816




          2,0221816






















              up vote
              0
              down vote













              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






              share|improve this answer
























                up vote
                0
                down vote













                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






                share|improve this answer






















                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  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






                  share|improve this answer












                  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







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 12 at 8:04









                  Harald Coppoolse

                  11.3k12959




                  11.3k12959



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253806%2fhow-to-group-and-subgroup-and-get-data-from-a-linq-query%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      這個網誌中的熱門文章

                      How to read a connectionString WITH PROVIDER in .NET Core?

                      In R, how to develop a multiplot heatmap.2 figure showing key labels successfully

                      Museum of Modern and Contemporary Art of Trento and Rovereto