Combine tables using row values as column LINQ C# SQL

Question

I have a users table:

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

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.

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?

Answer 1

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,
            });

Answer 2

You can do it by utilising GroupJoin, example:

var users = new List<Tuple<int, string, int>> {
    Tuple.Create(1, "Steve", 21),
    Tuple.Create(2, "Jack", 17),
    Tuple.Create(3, "Alice", 25),
    Tuple.Create(4, "Harry", 14)
};
var userInfos = new List<Tuple<int, string, string>> {
    Tuple.Create(1, "Height", "70"),
    Tuple.Create(2, "Height", "65"),
    Tuple.Create(2, "Eyes", "Blue"),
    Tuple.Create(4, "Height", "51"),
    Tuple.Create(3, "Hair", "Brown"),
    Tuple.Create(1, "Eyes", "Green"),
};
var query = users.GroupJoin(userInfos,
    u => u.Item1,
    ui => ui.Item1,
    (u, infos) => new { User = u, Infos = infos });
var result = query.Select(qi => new
{
    Id = qi.User.Item1,
    Name = qi.User.Item2,
    Age = qi.User.Item3,
    Height = qi.Infos.Where(i => i.Item2 == "Height").Select(i => i.Item3).SingleOrDefault(),
    Eyes = qi.Infos.Where(i => i.Item2 == "Eyes").Select(i => i.Item3).SingleOrDefault(),
    Hair = qi.Infos.Where(i => i.Item2 == "Hair").Select(i => i.Item3).SingleOrDefault()
});

Answer 3

First of all I have grouped the user details data using Feature (I have renamed the Key property with Feature to avoid confusion) & UId then I have used group join to combine both results using into g. Finally retrieved the result using specified feature.

var result = from user in users
             join detail in details.GroupBy(x => new { x.UId, x.Feature })
             on user.Id equals detail.Key.UId into g
             select new
        {
           Id = user.Id,
           Name = user.Name,
           Age = user.Age,
           Height = g.FirstOrDefault(z => z.Key.Feature == "Height") != null ? 
              g.First(z => z.Key.Feature == "Height").First().Value : String.Empty,
           Eyes = g.FirstOrDefault(z => z.Key.Feature == "Eyes") != null ? 
              g.First(z => z.Key.Feature == "Eyes").First().Value : String.Empty,
           Hair = g.FirstOrDefault(z => z.Key.Feature == "Hair") != null ? 
              g.First(z => z.Key.Feature == "Hair").First().Value : String.Empty,
        };

I am getting following output:-

Here is the complete Working Fiddle.

Answer 4

Try this

var list = (from u in context.users
                        join ud in context.UserDetails on u.Id equals ud.UId
                        select new
                        {
                            u.Id,
                            u.Name,
                            u.Age,
                            ud.Key,
                            ud.Value
                        });

            var finallist = list.GroupBy(x => new { x.Id, x.Name,x.Age}).Select(x => new
                {
                    x.Key.Id,
                    x.Key.Name,
                    x.Key.Age,
                    Height = x.Where(y => y.Key == "Height").Select(y => y.Value).FirstOrDefault(),
                    Eyes = x.Where(y => y.Key == "Eyes").Select(y => y.Value).FirstOrDefault(),
                    Hair = x.Where(y => y.Key == "Hair").Select(y => y.Value).FirstOrDefault()
                }).ToList();

Answer 5

try this query

var objlist=( form a in contex.user
              join b in contex.UserDetails on a.id equals a.Uid into gj
              from subpet in gj.DefaultIfEmpty()
                        select new { Id=a.id, Name=a.name, Age=a.age, Height =subpet.Height,Eyes=subpet.Eyes, Hair=subpet.Hair}).ToList();