CODEWITHSUNDEEP
c#entity-frameworklinqjoinleft-join
Bhavesh
Bhavesh

Reputation: 1071

C# Entity Framework complex join

Trying to figure this out since last 5 hours but couldn't find any solution.

Basically I am trying to create an excel with ExcelPackage from below data

Table1
    ID    Name
    1      t1_1
    2      t1_2


Table2
    ID    Name
    1      t2_1
    2      t2_2


Table12
    ID    Table1   Table2   Value
    1      1         1       v11
    2      2         2       v12

from this trying to get any of the following 

Table1Name, t2_1, t2_2
t1_1,       v11 ,
t1_2,           , v12

is there a way in EF to get row in this format? Where I am stuck is for each record in Table1 I want to get all record of Table2 with Value (from Table12) null if empty without repeating Table1 record (kind of turning rows of Table12 into columns of Table1).

Thanks

Upvotes: 2

Views: 531

Answers (1)

Slava Utesinov
Slava Utesinov

Reputation: 13498

var info = (from t1 in Table1                
            from t2 in Table2
            join t120 in Table12 
            on new {Table1 = t1.ID, Table2 = t2.ID} equals new {t120.Table1, t120.Table2} into t12s
            from t12 in t12s.DefaultIfEmpty()
            group new { t2, t12 } by new { t1.ID, t1.Name } into sub
            select new 
            {
                sub.Key.Name,
                data = sub.Select(x => new { x.t2.Name, x.t12 == null ? null : x.t12.Value }).ToList()
            }).ToList();

foreach(var item in info)
{
    Console.Write($"{item.Name}\t");
    foreach(sub in item.data.OrderBy(x => x.Name))
        Console.Write($"{sub.Name}:{sub.Value}\t");
    Console.WriteLine("");
}

Upvotes: 2

Related Questions