C# LINQ 连接两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/611074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ joining two tables
提问by Tanmoy
I have two tables say A and B. A cols are GUID, someintVar, someMoreIntvar B col are GUID, someItemNO, SomeItemDesc
我有两个表说 A 和 B。 A 列是 GUID、someintVar、someMoreIntvar B 列是 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?
现在，对于一个 GUID，我将在表 A 中只有一行。但是对于同一个 GUID，我可以有多行。现在我想根据 GUID 查询数据库并选择类中的值。这个类将有一个列表，该列表将包含来自第二个表的不同行。我该怎么做？
Right Now I am getting many items in the result based on how many rows are there in the second table for that GUID.
现在，根据该 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.
};
采纳答案by Marc Gravell
Unested, but how about re-writing it a bit:
Unested，但如何重新编写它：
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...
或者，您也许可以分组...
回答by Stephen Wrighton
Assuming this is happening in the NEW or LOAD method of your class...this is how I would do it...
假设这发生在您班级的 NEW 或 LOAD 方法中……这就是我要做的……
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
回答by James Curran
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)
假设您在 GUID 上的 A 和 B 之间设置了外键关系。（如果你不这样做，你的数据库架构就会损坏，需要修复）
var itemColl = from p in db.A
where p.CardID == "some GUID"
select new
{
p.CardID,
p.secondCol,
p.ThirdCol,
Items = p.Bs
}
