LINQ To SQL“分组依据" [英] LINQ To SQL "Group By"

问题描述

我想知道是否有人可以帮助我.我想在VB.Net中使用LINQ复制以下SQL查询.我对如何执行子查询/聚集尚不清楚.

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.

谢谢

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

在C#中按如下所示进行操作=> 请进行VB.Net转换.

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

推荐答案

这是我最喜欢的与此主题相关的页面.我喜欢LINQ to SQL(并希望他们打算继续通过Entity Framework支持它...)

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!

您的内部联接不再使用LtS的简单联接语法.您可以在内部select上说.Max()，也可以说Max(这里是伪函数):

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

这篇关于LINQ To SQL“分组依据"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！