C# 如何在 LINQ lambda 中的多个表之间执行连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9720225/
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
How to perform Join between multiple tables in LINQ lambda
提问by CiccioMiami
I am trying to perform a Join between multiple tablesin LINQ. I have the following classes:
我正在尝试在 LINQ 中的多个表之间执行连接。我有以下课程：
Product {Id, ProdName, ProdQty}
Category {Id, CatName}
ProductCategory{ProdId, CatId} //association table
And I use the following code (where product, categoryand productcategoryare instances of the above classes):
我使用以下代码（其中product,category和productcategory是上述类的实例）：
var query = product.Join(productcategory, p => p.Id, pc => pc.ProdID, (p, pc) => new {product = p, productcategory = pc})
.Join(category, ppc => ppc.productcategory.CatId, c => c.Id, (ppc, c) => new { productproductcategory = ppc, category = c});
With this code I obtain an object from the following class:
使用此代码，我从以下类中获取了一个对象：
QueryClass { productproductcategory, category}
Where producproductcategory is of type:
其中 productproductcategory 是以下类型：
ProductProductCategoryClass {product, productcategory}
I do not understand where the joined "table" is, I was expecting a single classthat contains all the properties from the involved classes.
我不明白的地方的加盟“表”是，我期待一个单独的类，它包含从所涉及的类的所有属性。
My aim is to populate another object with some properties resulting from the query:
我的目标是用查询产生的一些属性填充另一个对象：
CategorizedProducts catProducts = query.Select(m => new { m.ProdId = ???, m.CatId = ???, //other assignments });
how can I achieve this goal?
我怎样才能实现这个目标？
采纳答案by devgeezer
For joins, I strongly prefer query-syntax for all the details that are happily hidden (not the least of which are the transparent identifiers involved with the intermediate projections along the way that are apparent in the dot-syntax equivalent). However, you asked regarding Lambdas which I think you have everything you need - you just need to put it all together.
对于连接，我非常喜欢查询语法来处理所有隐藏起来的细节（其中最重要的是涉及中间投影的透明标识符，这些标识符在点语法等效中很明显）。但是，您询问了有关 Lambda 的问题，我认为您拥有所需的一切 - 您只需要将它们组合在一起即可。
var categorizedProducts = product
.Join(productcategory, p => p.Id, pc => pc.ProdId, (p, pc) => new { p, pc })
.Join(category, ppc => ppc.pc.CatId, c => c.Id, (ppc, c) => new { ppc, c })
.Select(m => new {
ProdId = m.ppc.p.Id, // or m.ppc.pc.ProdId
CatId = m.c.CatId
// other assignments
});
If you need to, you can save the join into a local variable and reuse it later, however lacking other details to the contrary, I see no reason to introduce the local variable.
如果需要，您可以将连接保存到局部变量中并在以后重用，但是由于缺乏其他细节，我认为没有理由引入局部变量。
Also, you could throw the Selectinto the last lambda of the second Join(again, provided there are no other operations that depend on the join results) which would give:
此外，您可以将 theSelect放入第二个的最后一个 lambda Join（同样，前提是没有其他依赖于连接结果的操作），这将给出：
var categorizedProducts = product
.Join(productcategory, p => p.Id, pc => pc.ProdId, (p, pc) => new { p, pc })
.Join(category, ppc => ppc.pc.CatId, c => c.Id, (ppc, c) => new {
ProdId = ppc.p.Id, // or ppc.pc.ProdId
CatId = c.CatId
// other assignments
});
...and making a last attempt to sell you on query syntax, this would look like this:
...并最后尝试向您推销查询语法，这将如下所示：
var categorizedProducts =
from p in product
join pc in productcategory on p.Id equals pc.ProdId
join c in category on pc.CatId equals c.Id
select new {
ProdId = p.Id, // or pc.ProdId
CatId = c.CatId
// other assignments
};
Your hands may be tied on whether query-syntax is available. I know some shops have such mandates - often based on the notion that query-syntax is somewhat more limited than dot-syntax. There are other reasons, like "why should I learn a second syntax if I can do everything and more in dot-syntax?" As this last part shows - there are details that query-syntax hides that can make it well worth embracing with the improvement to readability it brings: all those intermediate projections and identifiers you have to cook-up are happily not front-and-center-stage in the query-syntax version - they are background fluff. Off my soap-box now - anyhow, thanks for the question. :)
您的手可能会被查询语法是否可用所束缚。我知道有些商店有这样的要求 - 通常基于查询语法比点语法更有限的概念。还有其他原因，比如“如果我可以在点语法中做更多事情，我为什么还要学习第二个语法？” 正如最后一部分所示 - 查询语法隐藏的一些细节可以使它非常值得接受它带来的可读性改进：所有那些你必须制作的中间投影和标识符很高兴不是前端和中心 -查询语法版本中的阶段 - 它们是背景绒毛。现在离开我的肥皂盒 - 无论如何，谢谢你的问题。:)
回答by Jon Skeet
What you've seen is what you get - and it's exactly what you asked for, here:
你所看到的就是你得到的——这正是你所要求的，在这里：
(ppc, c) => new { productproductcategory = ppc, category = c}
That's a lambda expression returning an anonymous type with those two properties.
这是一个 lambda 表达式，返回具有这两个属性的匿名类型。
In your CategorizedProducts, you just need to go via those properties:
在您的 CategorizedProducts 中，您只需要通过这些属性：
CategorizedProducts catProducts = query.Select(
m => new {
ProdId = m.productproductcategory.product.Id,
CatId = m.category.CatId,
// other assignments
});
回答by Basheer AL-MOMANI
take look at this sample code from my project
从我的项目中查看此示例代码
public static IList<Letter> GetDepartmentLettersLinq(int departmentId)
{
IEnumerable<Letter> allDepartmentLetters =
from allLetter in LetterService.GetAllLetters()
join allUser in UserService.GetAllUsers() on allLetter.EmployeeID equals allUser.ID into usersGroup
from user in usersGroup.DefaultIfEmpty()// here is the tricky part
join allDepartment in DepartmentService.GetAllDepartments() on user.DepartmentID equals allDepartment.ID
where allDepartment.ID == departmentId
select allLetter;
return allDepartmentLetters.ToArray();
}
in this code I joined 3 tables and I spited join condition from where clause
在这段代码中，我加入了 3 个表，并且从 where 子句中设置了连接条件
note: the Services classes are just warped(encapsulate) the database operations
注意：服务类只是扭曲（封装）数据库操作
回答by saktiprasad swain
public ActionResult Index()
{
List<CustomerOrder_Result> obj = new List<CustomerOrder_Result>();
var orderlist = (from a in db.OrderMasters
join b in db.Customers on a.CustomerId equals b.Id
join c in db.CustomerAddresses on b.Id equals c.CustomerId
where a.Status == "Pending"
select new
{
Customername = b.Customername,
Phone = b.Phone,
OrderId = a.OrderId,
OrderDate = a.OrderDate,
NoOfItems = a.NoOfItems,
Order_amt = a.Order_amt,
dis_amt = a.Dis_amt,
net_amt = a.Net_amt,
status=a.Status,
address = c.address,
City = c.City,
State = c.State,
Pin = c.Pin
}) ;
foreach (var item in orderlist)
{
CustomerOrder_Result clr = new CustomerOrder_Result();
clr.Customername=item.Customername;
clr.Phone = item.Phone;
clr.OrderId = item.OrderId;
clr.OrderDate = item.OrderDate;
clr.NoOfItems = item.NoOfItems;
clr.Order_amt = item.Order_amt;
clr.net_amt = item.net_amt;
clr.address = item.address;
clr.City = item.City;
clr.State = item.State;
clr.Pin = item.Pin;
clr.status = item.status;
obj.Add(clr);
}
回答by Alex Martinez
var query = from a in d.tbl_Usuarios
from b in d.tblComidaPreferidas
from c in d.tblLugarNacimientoes
select new
{
_nombre = a.Nombre,
_comida = b.ComidaPreferida,
_lNacimiento = c.Ciudad
};
foreach (var i in query)
{
Console.WriteLine($"{i._nombre } le gusta {i._comida} y nació en {i._lNacimiento}");
}
回答by iDeveloper
it has been a while but my answer may help someone:
已经有一段时间了，但我的回答可能对某人有所帮助：
if you already defined the relation properly you can use this:
如果你已经正确定义了关系，你可以使用这个：
var res = query.Products.Select(m => new
{
productID = product.Id,
categoryID = m.ProductCategory.Select(s => s.Category.ID).ToList(),
}).ToList();
