在SQL的Linq中求和和分组? [英] Sum and Group By in Linq to SQL?

问题描述

Linq to SQL入门，因此可以原谅新手问题.我正在尝试在Linq to SQL(VB.NET)中重现以下(工作)查询:

Just getting started with Linq to SQL so forgive the newbie question. I'm trying to reproduce the following (working) query in Linq to SQL (VB.NET):

Select
    f.Title,
    TotalArea = Sum(c.Area)
From Firms f
Left Join Concessions c on c.FirmID = f.FirmID
Group By f.Title
Order by Sum(c.Area) DESC

(一个公司拥有许多特许权；一个特许权的面积为公顷.我想要一个列出所有特许权总面积最大的公司的清单.)

(A Firm has many Concessions; a Concession has an area in hectares. I want a list of Firms starting with the ones that have the greatest total area of all their concessions.)

我正在想像类似Linq to SQL的东西(伪代码)

I'm imagining something like this as the Linq to SQL equivalent (pseudo-code)

From f As Firm In Db.Firms _
Order By f.Concessions.Sum(Area)

...但是那是不对的.谁能指出我正确的方向?

... but that's not right. Can anyone point me in the right direction?

推荐答案

答案

这是与SQL等效的Linq正确的

Answer

Here's the correct Linq to SQL equivalent

From c In Concessions _
Join f In Firms on f.FirmID equals c.FirmID _
Group by f.Title _
Into TotalArea = sum(c.OfficialArea)  _
Order by TotalArea Descending _
Select Title, TotalArea

感谢@CMS向我指出 LinqPad -很棒的工具.您只需将其指向数据库，即可启动并运行.不仅包含数百个样本，而且您可以对包含的实时数据库运行它们.从提供的示例开始，我能够在短短的几分钟内到达上述查询.

Thanks to @CMS for pointing me to LinqPad - what a great tool. You just point it to your database and you're off and running. Not only are hundreds of samples included, but you can run them against included live databases. I was able to arrive at the above query in just a few minutes starting from the provided samples.

这篇关于在SQL的Linq中求和和分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！