Reputation: 45692
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.
};
Upvotes: 9
Views: 48309
Reputation: 1064114
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...
Upvotes: 5
Reputation: 103575
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
}
Upvotes: 0
Reputation: 37850
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
Upvotes: -2