如何在 LINQ lambda 中的多个表之间执行连接 [英] How to perform Join between multiple tables in LINQ lambda

问题描述

我正在尝试在 LINQ 中执行多个表之间的联接.我有以下课程:

I am trying to perform a Join between multiple tables in LINQ. I have the following classes:

Product {Id, ProdName, ProdQty}

Category {Id, CatName}

ProductCategory{ProdId, CatId} //association table

我使用以下代码(其中 product、category 和 productcategory 是上述类的实例):

And I use the following code (where product, category and productcategory are instances of the above classes):

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}

其中 productcategory 属于以下类型:

Where producproductcategory is of type:

ProductProductCategoryClass {product, productcategory}

我不明白连接的表"在哪里，我期待一个单个类，其中包含相关类的所有属性.

I do not understand where the joined "table" is, I was expecting a single class that 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?

推荐答案

对于连接，我强烈喜欢查询语法来处理所有隐藏的细节(其中最重要的是与中间投影相关的透明标识符)在点语法等价物中显而易见的方式).但是，您询问了 Lambda，我认为您拥有所需的一切 - 您只需要将它们组合在一起即可.

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.

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.

此外，您可以将 Select 放入第二个 Join 的最后一个 lambda(同样，前提是没有其他依赖于连接结果的操作)给:

Also, you could throw the Select into the last lambda of the second Join (again, provided there are no other operations that depend on the join results) which would give:

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. :)

这篇关于如何在 LINQ lambda 中的多个表之间执行连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！