Linq查询三个级别的表以产生总和 [英] Linq query across three levels of tables to generate sum

问题描述

我有一系列与主键/外键关联连接的表。我有一个交易表，每个交易引用一个且仅有一个产品。每种产品都可以存在于一个且只有一个类别中：

I have a series of tables that are joined with Primary Key/Foreign Key relationships. I have a table of Transactions, and each Transaction references one and only one Product. Each product can exist in one and only one Category:

Categories        Products           Transactions
-------------     -------------      -----------------
CategoryId        ProductId          TransactionId
CategoryName      CategoryId         ProductId
                  ProductName       TransactionAmount
  

我只是学习LinqToSql，并使用lambda表达式访问每个类别中的产品（x => x.Products）。但我想要做的是访问Transactions表，特别是按类别获得所有交易的总和。

I'm just learning LinqToSql, and I've used lambda expressions to access the Products in each category (x => x.Products). But what I'm trying to do is access the Transactions table, specifically to get a sum of all transactions by category.

我确信我可以通过创建手动加入我的Linq语句。但似乎我不应该这样做，因为我的所有表格在模型中都以PF / FK的形式加入。

I'm sure I could do this by creating manual Joins in my Linq statement. But it doesn't seem like I should have to do this, since all of my tables are joined as PF/FK in the model.

任何人都可以提供如何开始？

Can anyone offer an example of how to get started?

推荐答案

没有尝试编译它，我认为这会按照你想要的方式工作。它假定你在LINQ to SQL数据上下文中建立了关联。

Without trying to compile it, I think this will work the way you want. It assumes that you have the associations set up in the LINQ to SQL data context.

var transPerCategory = db.Categories
                         .Select( c => new {
                             Name = c.CategoryName,
                             Amount = c.Products
                                       .Sum( p => p.Transactions
                                                   .Sum( t => t.TransactionAmount )
                                           )
                          });

这篇关于Linq查询三个级别的表以产生总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！