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?

check this it will help you stackoverflow.com/questions/14066679/using-pivot-table-in-linq — Pranay Rana
– Pranay Rana, Commented Jul 2, 2015 at 9:13
Is the additional user info table constant or should new keys always be possible without changing your LINQ? — Matt
– Matt, Commented Jul 2, 2015 at 9:44
You think you hide your self after removing your downvoted anwer ? Already the Stackoverflow is notified for your activity with any detail. This is not a place to play, it is a place to learn and share your knowledge. — KodeFor.Me
– KodeFor.Me, Commented Sep 11, 2015 at 9:56
Don't worry, I don't play the detective. Already have give enough information to Stackoverflow, to make a reasearch and decide who is who. — KodeFor.Me
– KodeFor.Me, Commented Sep 11, 2015 at 15:23

Matt · Accepted Answer · 2015-07-02 10:17:22Z

4

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

answered Jul 2, 2015 at 10:17

Matt

4,6092 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Reece Kenney Over a year ago

Thank you for your answer! While there were a few answers that produced the desired results, yours was the simplest in my opinion.

Matt Over a year ago

But be careful if multiple UId/Key values are possible. Then you should use FirstOrDefault or LastOrDefault.

Reece Kenney Over a year ago

Yes that is actually the exact situation I have. Multiple UId's are possible so I used FirstOrDefault. Thanks again!

user5038678 Over a year ago

Yes this query is very simple. But if the Key value does not exist then this will fail, null exception is not handled but other answer did that.

Matt Over a year ago

Hmm.. when I look into the generated SQL query I cannot see a possible problem. Also it works fine with my generated test table. Thanks for further info.

Dzienny · Accepted Answer · 2015-07-02 09:35:33Z

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

answered Jul 2, 2015 at 9:35

Dzienny

3,4271 gold badge23 silver badges32 bronze badges

2 Comments

Reece Kenney Over a year ago

Can you explain what Item1, Item2 and Item3 are? Because I noticed they are not declared anywhere. Thanks for the response!

Dzienny Over a year ago

@Reece Kenney These are the properties of a Tuple class that is used to create the elements of users and userInfos. For example in case of a users element Item1 refers to the Id, Item2 refers to the Name, and so on.

Rahul Singh · Accepted Answer · 2015-07-02 09:57:44Z

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:-

enter image description here

Here is the complete Working Fiddle.

Palanikumar · Accepted Answer · 2015-07-03 04:33:48Z

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

Mukesh Kalgude · Accepted Answer · 2015-07-02 09:47:10Z

0

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

edited Jul 2, 2015 at 9:47

answered Jul 2, 2015 at 9:22

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

3 Comments

Reece Kenney Over a year ago

On line 2, is it supposed to be from b in instead of from b into because I get a syntax error using into. I am also getting an error at the join part which says invalid expression term 'join'. Thanks for the response!

Mukesh Kalgude Over a year ago

Ok Now check. No say thanks given right mark of query and vote

Palanikumar Over a year ago

Where did the properties subpet.Height, subpet.Eyes, subpet.Hair come from? This query will not work.

Collectives™ on Stack Overflow

Combine tables using row values as column LINQ C# SQL

5 Answers 5

5 Comments

2 Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

2 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related