score:4

Accepted answer

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

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

score:1

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

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

score:2

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.


Related Query

More Query from same tag