Click here to Skip to main content
15,741,818 members
Please Sign up or sign in to vote.
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

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.


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.
Updated 19-Mar-19 0:55am

1 solution

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:
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"]

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
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900