LINQ查询以连接多个表并在单行中获取逗号分隔的值 [英] LINQ query for joining multiple tables and get comma separated values in single row

问题描述

我在下表中列出了这些值.

帐户:

<身体>

Id	名称	电子邮件
101	纳西尔·乌丁	[e-mail]

角色:

<身体>

Id	标题
101	管理员
102	操作员

AccountRole:

<身体>

AccountId	RoleId
101	101
101	102

现在，我想编写一个linq，使其结果如下:

UserAccount

<身体>

AccountId	名称	电子邮件	角色
101	纳西尔·乌丁	[e-mail]	管理员、操作员

要获得以上结果，我已在LINQ中编写了以下查询.但却没有得到预期的结果.

  var userAccount1 =(来自_db.Accounts中的帐户在account.Id上的_db.AccountRoles中加入accountRole等于accountRole.AccountIdaccountRole.RoleId上_db.Roles中的join角色等于role.Id选择新的UserAccountInfo{AccountId = account.Id，名称= account.UserFullName，电子邮件=帐户.电子邮件，角色= string.Join(，"，role.Title)}); 

解决方案

下面根据您帖子中提供的信息(以及一些假设，因为我找不到例如)，使用Lambda表达式(而非查询表达式)为您提供了预期的结果您的任何表中的 UserFullName )

注意:我也相信有一种更有效的方法可以做到这一点，但这是没有其他条件的起点.

(正在使用.NET以下小提琴:

I have below tables with the values.

Account:

Id	Name	Email
101	Nasir Uddin	[e-mail]

Role:

Id	Title
101	Admin
102	Operator

AccountRole:

AccountId	RoleId
101	101
101	102

Now I want to write a linq to have the result like below:

UserAccount

AccountId	Name	Email	Roles
101	Nasir Uddin	[e-mail]	Admin, Operator

To get the above result I have written the below query in LINQ. But it does not get the expected result.

var userAccount1 = (from account in _db.Accounts
                               join accountRole in _db.AccountRoles on account.Id equals accountRole.AccountId
                               join role in _db.Roles on accountRole.RoleId equals role.Id             
                               select new UserAccountInfo
                               {
                                   AccountId = account.Id,
                                   Name = account.UserFullName,
                                   Email = account.Email,                                 
                                   Roles = string.Join(",", role.Title)
                               });

解决方案

The below gives you the expected result using Lambda Expression (not Query Expression) based on the information provided in your post (and some assumptions since I could not find e.g. UserFullName in any of your tables)

Note: I'm also convinced there is a more efficient way to do this, but it is a starting point if nothing else.

(Here is working .NET Fiddle of the below: https://dotnetfiddle.net/aGra15):

    // Join the AccountRoles and Roles together and group all Titles for
    // a given AccountId together
    var groupedAccountRoles = AccountRoles.GroupJoin(Roles, i => i.RoleId, o => o.Id, (o, i) => new {o, i})
        .Select(x => new {AccountId = x.o.AccountId, Titles = string.Join(",", x.i.Select(y => y.Title))});

    // Perform another GroupJoin to group by AccountId and Join to groupedAccountRoles table. Then `string.Join()`
    var userAccount1 = Accounts.GroupJoin(AccountRoles, acc => acc.Id, accrol => accrol.AccountId,
            (o, i) => new {o, UserAccountRoles = i})
        .GroupJoin(groupedAccountRoles, ii => ii.o.Id, oo => oo.AccountId,
            (ii, oo) => new UserAccountInfo
            {
                AccountId = ii.o.Id, 
                Email = ii.o.Email, 
                Name = ii.o.Name,
                Roles = string.Join(",", oo.Select(x => x.Titles))
            });

This will give the following output:

这篇关于LINQ查询以连接多个表并在单行中获取逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！