使用group by和join编写linq查询 [英] Write a linq query with group by and joins

问题描述

你好团队，



我有3张桌子（Roles，RolesLink，RolesTitle）



And这些表与列RoleID有关系。

取决于RolesLink的重复列（如果重复值应为2个值）我想将数据作为输出返回如下所示。

表：角色 
 RuleID名称Col3 Col4 
 1管理员
 2账户
 
表： RolesLink  
 ID RoleID DuplicateCol Col4 Col5 
 1 1 Title_ID 
 2 2 Title_ID 
 3 1 Title_Name 
 
表： 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 

请帮我写一个LINQ查询（在C＃）返回上面的输出结果。



谢谢，

jayaram



我尝试过：



我试图通过DuplicateCol获取基于组的数据，但我无法使用titlename获取数据。

解决方案

< blockquote>由于数据库结构不正确，无法按预期获得结果。主要原因是 DuplicateCol 和 TitleName 字段之间没有关系。因此，基于 RoleID 字段加入数据：

  var  result =（ from  r  in  Roles.AsEnumerable（）
  join  rl 在 RolesLink.AsEnumerable（）上的r [  RoleID]等于rl [  RoleID] 
  join  rt  in  RolesTitle.AsEnumerable（）on r [  RoleID]等于rt [  RoleID] 
 选择  new  
 {
 RoleName = r [ 名称]，
 Dupl icateCol = rl [  DuplicateCol]，
 TitleName = rt [  TitleName] 
}）
 .GroupBy（x => x.RoleName）; 

返回：

 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 

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.

解决方案

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"]
		})
	.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

这篇关于使用group by和join编写linq查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！