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
c# entity-framework linq model-view-controller
add a comment |
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
c# entity-framework linq model-view-controller
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
add a comment |
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
c# entity-framework linq model-view-controller
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
c# entity-framework linq model-view-controller
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
add a comment |
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
add a comment |
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 ;
add a comment |
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
add a comment |
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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 ;
add a comment |
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 ;
add a comment |
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 ;
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 ;
answered Nov 11 at 22:58
Gauravsa
2,0221816
2,0221816
add a comment |
add a comment |
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
add a comment |
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
add a comment |
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
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
answered Nov 12 at 8:04
Harald Coppoolse
11.3k12959
11.3k12959
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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