Write a linq query with group by and joins

Question

0.00/5 (No votes)

See more:

Hi team,

I have 3 tables (Roles,RolesLink,RolesTitle)

And the tables have relation with column RoleID.
Depend on duplicate column (if duplicate values should be 2 values) of RolesLink I want to return data as Output shown below.

Table: Roles
RuleID	  Name	  Col3	   Col4	
1         Admin			
2         Account			
			
Table: RolesLink
ID	RoleID	DuplicateCol	Col4	Col5
1 	 1      Title_ID		
2	 2      Title_ID		
3    1      Title_Name		
				
Table: RolesTitle
ID    RoleID  TitleName	Col4	Col5
1       1     Title_1		
2       2     Title_2		
3       1     Title_3

Output

RoleID1	RoleID2	Title1	Title2	
1       2       Title_1	Title_2

Please help me to write a LINQ query (in C#) to return above output result.

Thanks,
jayaram

What I have tried:

I have tried to get data based on group by DuplicateCol but I am not able to fetch data with titlename.

Posted 18-Mar-19 20:15pm

Hi,jayaram

Updated 19-Mar-19 0:55am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-03-19T00:56:00

Due to the bad database structure, it's impossible to get result as expected. The main reason is that that there's no relationship between DuplicateCol and TitleName fields. So, joining the data based on RoleID field:

C#

var result = (from r in Roles.AsEnumerable()
	join rl in RolesLink.AsEnumerable() on r["RoleID"] equals rl["RoleID"]
	join rt in RolesTitle.AsEnumerable()  on r["RoleID"] equals rt["RoleID"]
	select new
		{
			RoleName = r["Name"],
			DuplicateCol = rl["DuplicateCol"],
			TitleName = rt["TitleName"]
		})
	.GroupBy(x=>x.RoleName);

returns:

Key= Admin 
RoleName     DuplicateCol    TitleName
Admin        Title_ID        Title_1 
Admin        Title_ID        Title_3 
Admin        Title_Name      Title_1 
Admin        Title_Name      Title_3 
 
Key= Account 
RoleName    DuplicateCol    TitleName
Account     Title_ID        Title_2