Linq Group Join Items into List

Question

It's a master detail scenario, where each TABLE1 has multiple rows from TABLE2 assigned and I want to do something lihe this:

From a In TABLE1
Group Join c In TABLE2 On c.ID2 Equals a.ID Into Group
Select New ViewModel1 With {
    .COLUMN1 = a.COLUMN1,
    .COLUMN2 = a.COLUMN2,
    .SUBTABLE = New ViewModel2 With {
        .SUBCOLUMN1 = c.SUBCOLUMN1,
        .SUBCOLUMN2 = c.SUBCOLUMN2,
    }
}

Is this somehow possible?

yuezheng.hu · Answer 1 · 2014-08-13T14:53:31.047

Perhaps the following can help you:

class Program
{
    public class A
    {
        public int ID { get; set; }
        public string COLUMN1 { get; set; }
        public string COLUMN2 { get; set; }
    }

    public class B
    {
        public int ID { get; set; }
        public int AID { get; set; }
        public string SUBCOLUMN1 { get; set; }
        public string SUBCOLUMN2 { get; set; }
    }

    static void Main(string[] args)
    {
        var listA = new List<A>{
            new A() { ID = 1, COLUMN1="COLUMN11",COLUMN2 = "COLUMN12"}, 
            new A() { ID = 2 ,COLUMN1="COLUMN21",COLUMN2 = "COLUMN22"}
        };

        var listB = new List<B>()
        {
            new B(){ID=1,AID = 1 },
            new B(){ID=2,AID = 1},
            new B(){ID=3,AID = 1},
            new B(){ID=4,AID = 2},
            new B(){ID=5,AID = 2}
        };

        //Group Join As Method Chain：
        var queryAsMethodChain = listA.GroupJoin(listB, a => a.ID, b => b.AID, (a, t) => new
        {
            ID = a.ID,
            COLUMN1 = a.COLUMN1,
            COLUMN2 = a.COLUMN2,
            SUBTABLE = t.Select(tt => new { tt.SUBCOLUMN1, tt.SUBCOLUMN2 })
        });


        //Group Join As Standard Method
        var queryAsStandardMethod = from a in listA
                                  join b in listB
                                  on a.ID equals b.AID into t
                                  select new
                                  {
                                      ID = a.ID,
                                      COLUMN1 = a.COLUMN1,
                                      COLUMN2 = a.COLUMN2,
                                      SUBTABLE = t.Select(tt => new { tt.SUBCOLUMN1, tt.SUBCOLUMN2 })
                                  };

    }

Taugenichts · Accepted Answer · 2014-08-13T14:54:03.153

0

Do you mean this:

        var foobars = from foo in foolist
                      join bar in barlist on foo.Fooo equals bar.FooBar into t
                      select new
                      {
                          foo.Baar,
                          barbar = from bar in t
                                   select new { bar.FooBar, bar.BarFoo }
                      };

That would be (approximately) how you do the query you described.

Sorry, I had to reread the question a few times to get that it was being mapped to the first element.

edited Aug 13 '14 at 14:54

answered Aug 13 '14 at 14:42

Taugenichts

1,307
12
19

The subtable contains multiple rows assigned to its parent and therefore i need the barbar in you example to hold all those childs. – John Aug 13 '14 at 15:00
Yeah, I realized that later. How it is now, barbar is an IEnummerable of an anonymous type. If you need it to be a list, or any other format, I could do that for you too. You could also just assign a variable to t (it's also an IEnummerable of bar's type). – Taugenichts Aug 13 '14 at 15:06

Linq Group Join Items into List

2 Answers2