score: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,
});
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:-
here is the complete working fiddle.
Source: stackoverflow.com
Related Query
- Combine tables using row values as column LINQ C# SQL
- In a table a column datatype is varchar, retrieve only those values of the column which are number / numeric using linq to sql
- How can I access all values from a specific column in tables using linq and Entity Framework?
- Select values from two tables using “WHERE NOT IN” and DISTINCT in LINQ in Linq to Sql
- Select All distinct values in a column using LINQ
- How to Convert Row to Column in Linq and SQL
- Using LINQ to get DataGridView row index where first column has specific value
- How to reuse a linq expression for 'Where' when using multiple source tables
- Obtain max length for a "string" column using LINQ to SQL
- Count distinct values of a column in dataGridView using linq in .NET
- Selecting rows with distinct column values using LINQ
- LINQ to SQL join 3 tables and select multiple columns and also using Sum
- Using LINQ to SQL with Dynamic tables
- Change values of datatable column conditionally using Linq
- Separating the column values of DataTable using LINQ
- Joining two tables and returning multiple records as one row using LINQ
- How to combine Mapping Tables classes into one class using LINQ
- Find foreign key matching multiple row values using linq
- Pull data from multiple tables in one SQL query using LINQ and Entity Framework (Core)
- Using LINQ to SQL where database tables can have extra columns
- Delete a sql row using Linq in C#
- how to get two column values in a single query using linq to entities
- Dynamic distinct values using Linq to SQL
- To concatenate values of a particular column in multiple rows using linq
- Convert row values into columns using LINQ in c#
- Using LINQ to update every field in SQL row without specifically declaring each field?
- Multiple Tables Group and substract sum of columns using linq sql
- Using C#, LINQ - want to get column names AND data values
- Exclude null values while summation by specific column in a left joined table using LINQ
- Using Linq to SQL is it possible to retrieve two values and subtract them?
More Query from same tag
- Get SQL statement from a predicate?
- Enterprise Library calling oracle stored procedure with ExecuteSprocAccessor error
- OrderBy, GetNewBindingList and Linq to SQL
- how to change the string array to string not use loop clause? Can it be implement by LINQ?
- Atlassian SDK InvalidCastException when using LINQ expressions
- Entity framework, compare complex types
- How to Return class with list using LINQ in C#?
- how to group by letter and put in same letter size
- How I can pass the LINQ result anonymous type to another method?
- Reconfigure Linq query to create objects: LINQ to Entities does not recognize the method
- HttpGet HttpPost method deviation
- Lambda expressions and nullable types
- Chaining C# LINQ , in a Rails-scope-like way
- Mapping List not getting all items from master list
- Equivalent to HashSet.Contains that returns HashSet or index?
- MVC models - how to get all the data i need in 1 linq
- LINQ Nested group syntax
- XML Deserialize with same element name and different attributes name
- LINQ Query without for/foreach loop
- Grabbing value from column and show everything from that row depending if value is correct (LINQ - c# web API)
- How to get max sum range for specific time period from values in Dictionary
- Split a list of linq strings backwards for a search model
- Linq To Sql ManyToMany Update Existing Records
- What can be used instead of Datatable in LINQ
- Getting wrong sum and count in linq
- How to query data within a session variable using linq
- c# linq lambda where not
- How to convert SQL to linq for this query
- Linq to NHibernate: how to get a list of child objects without having the lists nested
- expression cannot be translate into store expression