Join tables using row values ​​as a LINQ column in 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 information about the user:

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

The UId column refers to the Id column in the users table. As you can see, not all users have the same additional information. Alice has no height value, only Jack has eye color, 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 the user does not have a value for the column, he can remain empty / null. Does this require some kind of data rotation?

Answer 1

In this case, the user information 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 this using GroupJoin , for 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 grouped user data data using a function (I renamed the Key property to Feature to avoid confusion) and UId, then I used group aggregation to combine both results with into g . Finally, the result was obtained using the specified function.

 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 get the following output: -

Here is the full working script.

Answer 4

try it

 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();