LINQ joining two tables

I have two tables say A and B. A cols are GUID, someintVar, someMoreIntvar B col are GUID, someItemNO, SomeItemDesc

Now for one GUID I will have only one row in Table A. But I can have multiple rows for the same GUID. Now I want to query the database based on GUID and select values in a class. This class will have a list that will hold different rows coming from the second table. How can I do it?

Right Now I am getting many items in the result based on how many rows are there in the second table for that GUID.

var itemColl = from p in db.A
               join item in db.B on p.CardID equals item.CardID
               where p.CardID == "some GUID"
               select new 
               {
                   p.CardID,
                   p.secondCol,
                   p.ThirdCol,
                   item.ItemNo // How to add them in a collection or list.
               };

From stackoverflow

• Unested, but how about re-writing it a bit:

  var itemColl = from p in db.A
                 where p.CardID == "some GUID"
                 select new {
                     p.CardID,
                     p.secondCol,
                     p.ThirdCol,
                     Items = db.B.Where(b=>b.CardID==p.CardID)
                        //.Select(b=>b.ItemNo) [see comments]
                 }
  

  Alternatively, you could perhaps group...

  Tanmoy : Thanks it is working. But just one thing. Its returning a list but not of type List. How can I convert it? Actually I am having a class with a List property. I need to select this class object.

  Tanmoy : It is returning System.Collections.Generic.List I need it to have List

  John Boker : just remove the .Select(b=>b.ItemNo) and the type will be List

  Marc Gravell : Sorry, I based that on the "item.ItemNo" in the final Select - just remove the Select

  Tanmoy : Got it. :) Thanks a lot

• Assuming this is happening in the NEW or LOAD method of your class...this is how I would do it...

  dim myAItem AS A = (from x in db.As WHERE x.CardID == MyGUIDValue).SelectSingleOrDefault
  
  ' Assign Variables Here
  Me.AValue1 = myAItem.FromDbValue1
  
  dim itemColl = (from b in db.Bs on b == MyGUIDValue).ToList 
  
  me.ItemList = New List(of MySubClass)
  For each bItem as B in itemColl
     dim item as New MySubClass
     'Assign Variables Here, ex: item.Value1 = bItem.MyDbValue1
     me.ItemList.Add(item)
  Next
  

• Assuming you have a foreign-key relationship set up between A and B on GUID. (And if you don't you db schema is broken and needs to be fixed)

  var itemColl = from p in db.A
                 where p.CardID == "some GUID"
                 select new 
                 {
                     p.CardID,
                     p.secondCol,
                     p.ThirdCol,
                     Items = p.Bs
                 }