Sum and Group By in Linq to SQL?

Question

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

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?

Answer 1

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

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.

Answer 2

Here you can find many examples about using aggregate functions and grouping, additionally I recommend you very much LinqPad, it's a great tool to test your queries on the fly and it's very good way to learn LINQ, it comes preloaded with 200 examples.