如何在VB.NET的LINQ查询中实现Access子查询的Count(*)统计

Got it, let's get that subquery count logic added to your LINQ statement. Your original Access query uses a correlated subquery to count matching records in VendorRpts, and we can replicate that directly in VB.NET LINQ with a nested query inside the Select clause.

Here's the complete LINQ code that matches your original SQL logic:

Dim myApps = (From Apps In dc.APPLICATIONs 
              Where Apps.APPLICATIONSIDNUMBER = AIN 
              Select Apps.SS, 
                     Apps.TITLENO, 
                     PrefVendorCount = (From vr In dc.VendorRpts 
                                        Where vr.Delivery = 5 
                                        AndAlso vr.OrderDate IsNot Nothing 
                                        AndAlso vr.applicationsidnumber = AIN 
                                        AndAlso vr.VendServCode <> VendSCID 
                                        Select vr).Count())

Let me break down how this maps to your original query:

• The nested From vr In dc.VendorRpts ... block replaces your Access subquery select count(*) from [VendorRpts] ...
• vr.Delivery = 5 directly matches Delivery=5
• vr.OrderDate IsNot Nothing is VB.NET's way of writing not(OrderDate is null)
• vr.applicationsidnumber = AIN aligns with applicationsidnumber=AIN (note: if AIN should reference the outer Apps.APPLICATIONSIDNUMBER instead of a standalone variable, swap this to vr.applicationsidnumber = Apps.APPLICATIONSIDNUMBER — this would make it a true correlated subquery tied to each row in APPLICATIONs)
• vr.VendServCode <> VendSCID translates to not(VendServCode=VendSCID )

When you execute this LINQ query, your ORM (like LINQ to SQL or Entity Framework) will generate SQL that's functionally identical to your original Access query, so you'll get the same PrefVendorCount values you're expecting.

内容的提问来源于stack exchange，提问作者Edil