使用行值作为列LINQ C#SQL合并表 [英] Combine tables using row values as column LINQ C# SQL

问题描述

我有一个users表:

Id  | Name   | Age
-------------------- 
1   | Steve  | 21
2   | Jack   | 17
3   | Alice  | 25
4   | Harry  | 14

我还有一个包含其他用户信息的表:

I also have a table containing additional user info:

UId | Key    | Value
---------------------- 
1   | Height | 70
2   | Height | 65
2   | Eyes   | Blue
4   | Height | 51
3   | Hair   | Brown
1   | Eyes   | Green

UId列链接到users表中的Id列.如您所见，并非所有用户都具有相同的附加信息.爱丽丝(Alice)没有身高值，杰克(Jack)是唯一拥有眼睛颜色值等的人.

The UId column links to the Id column in the users table. As you can see, not all users have the same additional info present. Alice doesn't have a height value, Jack is the only one with an eye color value etc.

是否有一种方法可以使用C#和LINQ查询将这些数据动态地组合到一个表中，以便结果是这样的:

Is there a way to combine this data into one table dynamically using C# and LINQ queries so that the result is something like this:

Id  | Name   | Age | Height | Eyes  | Hair
------------------------------------------ 
1   | Steve  | 21  |   70   | Green |     
2   | Jack   | 17  |   65   | Blue  |       
3   | Alice  | 25  |        |       | Brown   
4   | Harry  | 14  |   51   |

如果用户没有该列的值，则该列可以为空/空.这是否需要某种数据透视?

If a user does not have a value for the column, it can remain empty/null. Does this require some sort of data pivoting?

推荐答案

在这种情况下，您的用户信息字段是恒定的:

For the case, your user info fields are constant:

 var result = users.GroupJoin(details,
            user => user.Id,
            detail => detail.Id,
            (user, detail) => new
            {
                user.Id,
                user.Name,
                user.Age,
                Height = detail.SingleOrDefault(x => x.Key == "Height").Value,
                Eyes = detail.SingleOrDefault(x => x.Key == "Eyes").Value,
                Hair = detail.SingleOrDefault(x => x.Key == "Hair").Value,
            });

这篇关于使用行值作为列LINQ C#SQL合并表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！