Multi Table join and group by in Linq

Question

I have 4 tables without any FK of others like this:

Table1: Id, Number, Date, Price

Table2: Id, Number, Date, Price, Table1Number, Table1Date

Table3: Id, Number, Date, Price, Table2Number, Table2Date

Table4: Id, Number, Date, Price, Table3Number, Table3Date

I want this output:

Table1.Number, Table1.Date, Table1.Price,

Table2.Number, Table2.Date, Table2.Price (Sum Price)

Table3.Number, Table3.Date, Table3.Price (Sum Price)

Table4.Number, Table4.Date, Table4.Price (Sum Price)

table1-table2 are One-One relation but table2-table3 and table3-table4 are One-Many.

I Confused to join them in Linq and get sum price of any table!

Please show some models and what you have tried. SO has a great Documentation section with examples of linq queries including joins — Gilad Green
Are the SUMs in Tables 2,3,4 subtotals from the other tables that came before them or are they separate? — Tom Hines

Yoruba Yoruba · Accepted Answer · 2017-04-24T18:17:50

This is maybe not so helpful. What you can do is to try the queries out with LinqPad. With this code I was able to get a 4 tables and get the total of the different tables. The total of Table1 and Table2 are just the price of 1 record. The total of Table3 is the sum of all records which belongs to Table1 and Table2. There are 2 totals for Table4 because Table3 have relations with 2 Tables4.

If you give more detail maybe I can help you futher.

    void Main()
   {

    var tables4a = new List<Table4>(){new Table4{id=1, price=40}, new Table4{id=2, price=41}};
    var tables4b = new List<Table4>(){new Table4{id=1, price=50}, new Table4{id=2, price=51}};
    var tables3 = new List<Table3>(){new Table3{id=1, price=20, tables4= tables4a}, new Table3{id=2, price=21, tables4 = tables4b}};
    var tables2 = new List<Table2>() {new Table2(){id=1, price=5, tables3= tables3, table1= new Table1(){
    id = 1, price = 11
    }}};


    var Total123 = tables2.Where(x=>x.id ==1)
    .SelectMany(x=>x.tables3, (table2,b)=>new{
    table2
    })
    .SelectMany(x=>x.table2.tables3, (table_2,table_3)=>new{
    table_2,
    table_3
    })
    .Select(v=> new {
    SumTable1= v.table_2.table2.table1.price,
    SumTable2= v.table_2.table2.price,
    SumTable3 =v.table_2.table2.tables3.Sum(x=>x.price),
    SumTable4= v.table_3.tables4.Sum(x=>x.price),
        })
        .Distinct()
    ;

    Total123.Dump();
}


public class Table1
{
    public int id {get;set;}
    public int price {get; set;}
}

public class Table2
{
    public int id {get;set;}
    public int price {get; set;}
    public Table1 table1 {get; set;}
    public List<Table3> tables3 {get; set;}
}

public class Table3
{
    public int id {get;set;}
    public int price {get; set;}
    public List<Table4> tables4 {get; set;}
}

public class Table4
{
    public int id {get;set;}
    public int price {get; set;}
}

Multi Table join and group by in Linq

1 Answers