score:0
it's very easy, just change "from p" to "from q2":
from edc in table1
join q1 in table2 on __edc.id equals q1__.id
join q2 in _table3 on q2.id equals q1.id into leftjoin
from q2 in leftjoin.defaultifempty()
group q1 by p.description
into grouped
select new myobj
{
label = grouped.key,
value = grouped.count(),
}
score:1
consider the following example. we have three tables, with a left join between table1 and table2, and a second left join to table3. you need to specify defaultifempty()
on the two joins to include rows where there is no match in the right table.
public class item
{
public int id { get; set; }
public string description { get; set; }
}
class program
{
static void main(string[] args)
{
var table1 = new list<item>
{
new item {id = 1, description = "a"},
new item {id = 2, description = "b"},
new item {id = 3, description = "c"},
new item {id = 4, description = "d"}
};
var table2 = new list<item>
{
new item {id = 1, description = "e"},
new item {id = 2, description = "f"},
new item {id = 4, description = "g"}
};
var table3 = new list<item>
{
new item {id = 1, description = "h"},
new item {id = 4, description = "h"},
new item {id = 5, description = "i"},
new item {id = 6, description = "j"}
};
var leftjoin = from t1 in table1
join t2 in table2 on t1.id equals t2.id into firstjoin
from x in firstjoin.defaultifempty()
join t3 in table3 on x?.id equals t3.id into secondjoin
from y in secondjoin.defaultifempty()
select new
{
table1id = t1?.id,
table1description = t1?.description,
table2id = x?.id,
table2description = x?.description,
table3id = y?.id,
table3description = y?.description
};
console.writeline("left join:");
foreach (var i in leftjoin)
{
console.writeline($"t1id: {i.table1id}, t1desc: {i.table1description}, " +
$"t2id: {i.table2id}, t2desc: {i.table2description}, " +
$"t3id: {i.table3id}, t3desc: {i.table3description}");
}
console.writeline(string.empty);
var grouped = from x in leftjoin
group x by x.table3description
into group1
select new
{
label = group1.key,
count = group1.count()
};
console.writeline("left join grouped:");
foreach (var i in grouped)
{
console.writeline($"label: {i.label}, count: {i.count}");
}
console.readline();
}
}
running the program yields the following output:
left join:
t1id: 1, t1desc: a, t2id: 1, t2desc: e, t3id: 1, t3desc: h
t1id: 2, t1desc: b, t2id: 2, t2desc: f, t3id: , t3desc:
t1id: 3, t1desc: c, t2id: , t2desc: , t3id: , t3desc:
t1id: 4, t1desc: d, t2id: 4, t2desc: g, t3id: 4, t3desc: h
left join grouped:
label: h, count: 2
label: , count: 2
hope this helps!
Source: stackoverflow.com
Related Query
- Converting a LINQ Inner join with Left Join
- LINQ Lambda Left join with an Inner join
- Inner left join with linq returns too many results
- INNER Join and LEFT OUTER JOIN in EF-Core with Linq
- LINQ Inner Join on Substring with length of left side
- Need help with converting SQL to LINQ - LEFT JOIN with Count
- Converting an SQL with Left Join , Group and Sum to a Linq Query
- Multiple Left Join and Inner Join with LinQ
- Converting SQL with LEFT JOIN to Linq (Method Syntax)
- LINQ 3 Inner Joins with 1 Left Outer Join
- Converting Oracle SQL query with left outer join to Linq
- LINQ to SQL - Left Outer Join with multiple join conditions
- Convert SQL to Linq left join with null
- How do I most elegantly express left join with aggregate SQL as LINQ query
- Linq - Left outer join with dot notation
- Left join on two Lists and maintain one property from the right with Linq
- How to left join multiple tables with LINQ
- Linq with Left Join on SubQuery containing Count
- Left outer join using LINQ -- understanding the code
- Linq left outer join with custom comparator
- JOIN and LEFT JOIN equivalent in LINQ with Method Syntax
- LINQ left join with only the row having maximum value of a column
- NHibernate 3 LINQ inner join issue with three jumps: NotSupportedException
- LINQ Method Syntax with INNER and OUTER Join
- Using LINQ left join with multiple conditions and subquery
- SQL to LINQ with multiple join, count and left join
- LInq left join with multiple condition in on clause
- Left join with OrderBy in LINQ
- LINQ Left Outer Join combined with Let
- Left outer Join with LINQ
More Query from same tag
- What would this sql query (w count and group by) look like when translated to linq?
- Reading xml file: parsing from element attributes using linq
- Compare DateTime in EF Core Linq Query
- SQL query with subquery equivalent for Linq-to-SQL
- SQL query in LINQ
- Dynamic LINQ GroupBy Multiple Columns
- How to select a date interval in IQueryable<>?
- Merge two arrays in a Key => Value
- Select 2 or more columns on DataTable using LINQ
- Assistance with LINQ query spanning multiple tables with different relationships
- LINQ filter string with value string
- Complex Subtotals needed for in house report
- c# sorting a list by the attributes of it's elements
- Remove last occurrence of string in list
- Mongodb C# query nested document
- Convert List.Contains to Expression Tree
- TransactionScope vs Transaction in LINQ to SQL
- Conditional selection in LINQ (select instead if empty)
- "error CS0103: the name 'w' does not exist in the current context" in LINQ lambda expression
- How to query a timestamp in SQL Server 2008 with Linq-to-Entities?
- Can I Use a Property in a LINQ Where Clause?
- Table not getting updated while using LinQ
- EF linq Find in list where objectA.prop1 is any of objectB.prop2
- Clean way to reduce many TimeSpans into fewer, average TimeSpans?
- How to use LINQ to order within groups
- In C#, how can I sort a collection of objects by multiple fields of that object?
- How to query this XML in LINQ?
- Linq Count is returning different results
- Avoiding Redundancy using LINQ Enumerable All
- Convert SQL query to linq group by