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!


Related Query

More Query from same tag