LINQ（C＃）Join - 在单行中显示重复数据（Pivot） [英] LINQ (C#) Join - Displaying duplicated data in single row (Pivot)

问题描述

我正在尝试加入一个表并查看并在数据表中显示结果。

考虑我的场景（数据库）：

表格：请求

RequestId               SurrogateId

RequestId              SurrogateId

1                                   1

1                                  1

2                                     2

2                                    2

查看：DemoView

View : DemoView

SurrogateId        ;   ApproverName              ApproverType

SurrogateId          ApproverName              ApproverType

1                            拉姆                             
财务

1                            Ram                             Finance

1                             萨姆                             
管理员

1                            Sam                             Admin

1                            丹                                 Manager

1                            Dan                               Manager

2                             ABC                               
财务

2                            abc                               Finance

我的要求是在DataTable的UI上显示如下：

RequestId         SurrogateId         金融及NBSP;        经理         管理员

RequestId        SurrogateId          Finance          Manager          Admin

1                            1                    
Ram             山姆               Dan

1                           1                     Ram              Sam               Dan

2                             2                     
abc

2                            2                      abc

我尝试过以下查询：

var query = (

 from request in DB.Request

 join financeaprv in DB.DemoView on request.SurrogateID equals financeaprv.SurrogateID into financeaprvList
                             from financeaprv in financeaprvList.DefaultIfEmpty()
                             where financeaprv.ApproverType=="Finance"

  join sysadmin in DB.DemoView on request.SurrogateID equals sysadmin.SurrogateID into sysadminList
                             from sysadmin in legalaprvList.DefaultIfEmpty()
                             where sysadmin.ApproverType=="Admin"

  select new queryResult                             
  {
        RequestId = request.RequestId,
        FinanceApprv = financeaprv.ApproverName,
        Admin = sysadmin.ApproverName

  }

但是连接不正确，因为不返回所有行。 （加入RequestID和SurrogateId
并不是一个问题，因为它很直接。  它正在加入管理，财务和经理我'我无法完成）

But the join is not correct as not returns all rows.(Joining RequestID and SurrogateId is not much of an issue as it's straight forward.   It's joining Admin,Finance and Manager I'm unable to accomplish)

任何人都可以告诉LINQ查询（在查询语法中）是什么来完成我的要求。

Can anybody tell what would be the LINQ query ( in Query syntax ) to accomplish my requirement.

提前致谢。

注意： - 我是
每次有意加入DemoView （而不是一次性将所有值存储在列表中）到期出于要求原因...我正在尝试以与上面提到的相同的方式完成查询。

Note :- I'm intentionally joining DemoView each time (instead of storing all it's value in a list at once) due to requirement reasons... I'm trying to accomplish the query in the same way I've mentioned above.

推荐答案

根据
https://social.msdn.microsoft.com/Forums/en-US/e09b50af-5ab4-4cfb-9617 -ef2be339c17f ，尝试一个复杂的"开启"条件，没有'where'，可能是这样的：

According to https://social.msdn.microsoft.com/Forums/en-US/e09b50af-5ab4-4cfb-9617-ef2be339c17f, try a complex ‘on’ condition without ‘where’, probably like this:

from request in DB.Request
join financeaprv in DB.DemoView on new { request.SurrogateID, ApproverType = "Finance" } equals new { financeaprv.SurrogateID, financeaprv.ApproverType } into financeaprvList
from financeaprv in financeaprvList.DefaultIfEmpty() 
. . .

生成最终输出值时，请检查数据是否为null，例如'选择新{FinanceApprv = financeaprv？.ApproverName，...'。

When you generate the final output values, check the data for null, like ‘select new { FinanceApprv = financeaprv?.ApproverName, …’.

这篇关于LINQ（C＃）Join - 在单行中显示重复数据（Pivot）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！