LINQ To SQL “Group By”

Question

I wonder if someone can help me. I want to replicate the following SQL query using LINQ in VB.Net.I'm a little unclear on how to do subqueries / aggregates.

Thanks

SELECT  *
FROM    Server S
    INNER JOIN ServerHDD H
            ON S.Server_ID = H.Server_ID
    INNER JOIN (SELECT      MAX(ServerHDD_ID) AS ServerHDD_ID
                            FROM    ServerHDD
                            GROUP BY Server_ID, Letter) Filter
            ON H.ServerHDD_ID = Filter.ServerHDD_ID
ORDER BY S.Hostname, H.Letter

Got this as below in C# => need VB.Net Conversion please.

from S in SERVER
join H in SERVERHDD on S.Server_ID equals H.Server_ID
join FILTER in 
    (from s in SERVERHDD group s 
            by new {s.Server_ID, s.Letter} 
            into groupedServerHDD select new 
                    {
                            SERVERHDD_ID = groupedServer.Sum(gS=>gS.ServerHDD_ID)
                    }
    )
     on H.ServerHDD_ID equals FILTER.SERVERHDD_ID 
     orderby S.Hostname, H.Letter
     select S

Answer 1

There are a couple of LINQ learning tools out there that are pretty cool.

This one is my favourite... LinqPad. But you might also want to check out Linqer.

You should be able to paste your code into one of them apps and it will show you how its converted. Hope they come in handy :)

Answer 2

This is my most favorite page regarding this topic. I love LINQ to SQL (and wish they intended to continue support for it over Entity Framework...) http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx. On this page you will find all the answers to your querying needs. It is hard to format the query here without something to test it against!

Your inner joins go away with the simple join syntax of LtS. You can either say .Max() on your inner select or Max(pseudo functoid here) like this:

From p2 In g _
Where p2.UnitPrice = g.Max(Function(p3) p3.UnitPrice) _
Select p2