LINQ查询以提取包含关联实体的自定义格式化字段的实体？ [英] LINQ query to fetch entities that include custom formatted field of associated entities?

问题描述

LINQ
有点生锈我想从给定用户的相关表中获取单个结果。参见下面的模式。
每个用户都有一个或多个角色。我想要一个用户名列表和一个自定义字符串，它们是一个格式的列表，其格式如Role1 - Role2 - Role3，其中的值是与该用户的UserRole / Role相关联的RoleNames。

 角色
 ===== 
 RoleId 
 RoleCode 
 RoleName 
 
 UserRole 
 ======== 
 UserRoleId 
 RoleId 
 UserId 
 
用户
 ==== X-454545454545 X-4545 X- 20045 X-454545 X-454545 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X-的用户名及其角色，而不是RoleName，我希望结果中的单个字段成为所有用户角色的格式化字符串，如上所述。
 
 
 
这是我现在所在如何构建每个用户的角色列表？
  from u in Users 
 join User in UserRoles on u.UserId等于ur.UserKey 
在r.RoleKey中的角色中连接r等于r.RoleId 
选择新的{
 u.UserId，
 u.UserName，
 r.RoleName 
} 
  
 
 
解决方案
 200新新新新旗新新新新旗新新旗旗新200 200 200 200新新新新旗新新旗200新新新旗新新旗新新旗200新新新新旗新新旗200新新新新旗新新旗200新新新新旗新新旗2001-新新新新旗新新旗2001-新新新新新新旗新新旗2001-新新新新新旗新新旗2001-新新新新新旗新新旗2001-新新新新新旗新新旗2001-新新新新新新新新新新旗新新旗2001-新新新新新旗新新旗新新旗新新旗新旗新旗新旗新新旗新新旗新新旗新旗新旗新新旗新新旗新旗新新旗新新旗新新旗新新旗新旗新旗新旗新旗新新旗新新旗新旗新旗新新旗新新旗新新旗新旗新旗新旗新旗X-4545 X-4545 X-454545 X-454545 X-454545 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X- 20045 X-4545 X- 20045 X- RoleCode =PU，RoleName =Power User}}; 
 var Users = new [] {new {UserId = 1，UserName =Bit Shift}，新的{UserId = 2，UserName =Edward}}; 
 var UserRoles = new [] {new {UserRoleId = 1，RoleId = 1，UserId = 1}，新的{UserRoleId = 2，RoleId = 2，UserId = 1}，新{UserRoleId = 3，RoleId = 2 ，用户ID = 2}}; 
 
 var userRoles =来自u在用户
在UserRoles中连接u.UserId等于ur.UserId 
在r.RoleId中的角色中连接r等于r.RoleId 
 select new 
 {
 u.UserId，
 u.UserName，
 r.RoleName 
} 
 into userRole 
 group userRole by userRole .UserName into userGroups 
 select new {UserName = userGroups.Key，Roles = string.Join（ - ，userGroups.Select（ug => ug.RoleName））}; 
 userRoles.Dump（）; 
  

更简洁的版本，其中包括结果中的UserId + UserName

  var userRoles = from u in Users 
加入ur在UserRoles上的u.UserId等于ur.UserId 
加入r在Roles on ur.RoleId equals r.RoleId 
 group r by u into userGroups 
 select new {User = userGroups.Key，Roles = string.Join（ - ，userGroups.Select（r => r.RoleName））}; 
 userRoles.Dump（）;新新p新p新新新新旗新新旗旗哨位置200 200 200 X- 200 200 200 X- 20045 X- 20045 X- 20045 X-454545 X- alt =LINQPad result 2> 
 
 
 
当执行SQL数据库时，需要将查询分为两部分，如LINQ to SQL不支持String.Join ，像这样 -  
  var userRoleGroups =（从u在用户
中加入ur在UserRoles上u.UserId等于ur新新新旗新新新新旗新新新新旗新新旗新新旗200新新新新旗新新旗200新新新新旗新新名：新新新旗新新旗新新款： //这样可以生成和执行SQL $ user 
 var userRoles = userRoleGroups中的userGroups select（new {User = userGroups.Key，Roles = string.Join（ - ，userGroups.Select（r => r .RoleName））}）; 
 userRoles.Dump（）; X-454545454545 X- 20045 X-454545 X-454545 X-454545 X- 20045 X- 20045 X-4545 X- 20045 X-454545 X- 20045 X- 
 
 
  var userRoles =从u在​​用户
中加入ur在UserRoles上u.UserId等于ur.UserId 
 join r in ur.RoleId上的角色等于r.RoleId 
 group r by u into userGroups 
 select（new {
 User = userGroups.Key，
 Roles = userGroups.Select（s => ; s.RoleName）.Aggregate（（current，next）=> current + - + next）}）; 
 userRoles.Dump（）; 
  
 
A bit rusty on LINQ 
I want to get a single result from related tables for a given user.  See schema below.
Each user has one or more roles.  I want a list of usernames and a custom string that is a list of their roles in a format such as "Role1 - Role2 - Role3", where the values are the RoleNames associated with the UserRole/Role for that user.
Role
=====
RoleId
RoleCode
RoleName

UserRole
========
UserRoleId
RoleId
UserId

Users
======
UserId
UserName  
Testing it out in LINQpad, I can get a list of usernames and their roles, but instead of the RoleName, I want a single field in the result to be a formatted string of ALL the users roles, as mentioned above.

Here is what I have now.  How can I construct a list of the roles for each user?
from u in Users 
join ur in UserRoles on u.UserId equals ur.UserKey
join r in Roles on ur.RoleKey equals r.RoleId
select new { 
    u.UserId,
    u.UserName,
    r.RoleName
}

解决方案
Add group by UserName to your LINQ query, and use string.Join to format the roles separated by "-".

You can test this in LINQPad -
var Roles = new [] {new{RoleId=1,RoleCode="SU",RoleName="Super User"},new{RoleId=2,RoleCode="PU",RoleName="Power User"}};
var Users = new [] {new{UserId=1,UserName="Bit Shift"},new{UserId=2,UserName="Edward"}};
var UserRoles = new [] {new{UserRoleId=1,RoleId=1,UserId=1},new{UserRoleId=2,RoleId=2,UserId=1},new{UserRoleId=3,RoleId=2,UserId=2}};

var userRoles = from u in Users 
join ur in UserRoles on u.UserId equals ur.UserId
join r in Roles on ur.RoleId equals r.RoleId
select new
{ 
    u.UserId,
    u.UserName,
    r.RoleName
}
into userRole
group userRole by userRole.UserName into userGroups
select new{ UserName=userGroups.Key, Roles = string.Join(" - ", userGroups.Select(ug => ug.RoleName))};
userRoles.Dump();


More succinct version, which includes UserId+UserName in result -
var userRoles = from u in Users 
join ur in UserRoles on u.UserId equals ur.UserId
join r in Roles on ur.RoleId equals r.RoleId
group r by u into userGroups
select new{ User=userGroups.Key, Roles = string.Join(" - ", userGroups.Select(r => r.RoleName))};
userRoles.Dump();


When executing against SQL database, you need to the query split into two parts, as String.Join is not supported by LINQ to SQL, like this - 
   var userRoleGroups = (from u in Users 
    join ur in UserRoles on u.UserId equals ur.UserId
    join r in Roles on ur.RoleId equals r.RoleId
    group r by u into userGroups select userGroups)
    .ToList(); // This causes SQL to be generated and executed
    var userRoles = from userGroups in userRoleGroups select(new{ User=userGroups.Key, Roles = string.Join(" - ", userGroups.Select(r => r.RoleName))});
    userRoles.Dump();
Or try using Aggregate instead of String.Join, as you suggested, like this -
var userRoles = from u in Users 
join ur in UserRoles on u.UserId equals ur.UserId
join r in Roles on ur.RoleId equals r.RoleId
group r by u into userGroups 
select(new{ 
    User=userGroups.Key,
    Roles = userGroups.Select(s => s.RoleName).Aggregate((current, next) =>  current + " - " + next)});
userRoles.Dump();


                        
这篇关于LINQ查询以提取包含关联实体的自定义格式化字段的实体？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！