将MS Access查询转换为VB.NET LINQ时的子查询连接字段问题求助

我之前在处理类似Access转LINQ的需求时，也碰到过匿名类型字段访问受限的情况，结合你的场景，问题主要出在匿名类型的访问限制或者左连接的LINQ实现方式上，下面给你两种可行的解决方案：

方案一：在同一作用域内使用匿名类型（快速实现） #

如果你的myMuniCty和主查询在同一个方法里，只需要确保正确实现左连接，并且准确引用匿名类型的属性即可。注意VB.NET中LINQ的左连接需要用Group Join + Into + DefaultIfEmpty()来模拟，而不是直接用Join：

' 保留你已写好的myMuniCty查询，注意补充原MuniCounty查询里的Premises字段
Dim myMuniCty = (From muns In dc.MUNs 
                 Join ctys In dc.Counties On muns.COUNTYCD Equals ctys.CountyCd 
                 Select New With {
                     muns.MUNIDNUMBER, 
                     muns.Premcode, 
                     muns.COUNTYCD, 
                     muns.Village, 
                     muns.TOWN, 
                     muns.CITY, 
                     muns.ZipCode, 
                     ctys.County, 
                     ctys.State, 
                     ctys.Zone, 
                     ctys.MTRate, 
                     ctys.AddTax, 
                     ctys.MTAZone,
                     muns.Premises
                 })

' 主查询实现原Access的连接逻辑
Dim finalQuery = From apps In dc.Applications
                 ' 内连接Applications和Property
                 Join props In dc.Property On apps.APPLICATIONSIDNUMBER Equals props.APPLICATIONSIDNUMBER
                 ' 左连接myMuniCty（模拟Access的LEFT JOIN）
                 Group Join muni In myMuniCty On props.PremIDNumber Equals muni.MUNIDNUMBER Into muniMatches = Group
                 From muni In muniMatches.DefaultIfEmpty()
                 ' 选择需要的字段，注意处理左连接可能的null值
                 Select New With {
                     apps.APPLICATIONSIDNUMBER,
                     apps.TITLENO,
                     props.PremIDNumber,
                     props.Proptype,
                     props.Streetno,
                     props.Range,
                     props.Street,
                     props.Address2,
                     props.ZipCode,
                     props.Subdiv,
                     props.Unit,
                     props.Interest,
                     props.ReissueAD,
                     props.ReissueCD,
                     props.ReissueBL,
                     props.Grantor,
                     props.PDeedDay,
                     props.PDeedRday,
                     props.PriorDBk,
                     props.PriorIns,
                     props.PriorInsDate,
                     props.PriorInsTN,
                     props.SurveyNo,
                     props.PerRP,
                     props.MultiProp,
                     props.Comments,
                     props.Display,
                     ' 处理左连接的字段，用If判断避免空引用
                     .Premises = If(muni IsNot Nothing, muni.Premises, Nothing),
                     .Village = If(muni IsNot Nothing, muni.Village, Nothing),
                     .TOWN = If(muni IsNot Nothing, muni.TOWN, Nothing),
                     .CITY = If(muni IsNot Nothing, muni.CITY, Nothing),
                     .County = If(muni IsNot Nothing, muni.County, Nothing),
                     .COUNTYCD = If(muni IsNot Nothing, muni.COUNTYCD, Nothing),
                     .Zone = If(muni IsNot Nothing, muni.Zone, Nothing),
                     ' 原查询的State来自Applications.Statecode
                     .State = apps.Statecode
                 }

如果还是找不到myMuniCty的字段，检查以下两点：

• 确保myMuniCty和主查询在同一个方法/作用域内（匿名类型默认是Internal访问级别，跨作用域无法访问属性）
• 检查属性名拼写是否和myMuniCty中推断的一致（比如MUNIDNUMBER的大小写，VB.NET虽然不区分大小写，但最好和原字段名保持一致）

方案二：使用命名实体类（更健壮，适合跨作用域） #

如果需要在不同方法或类之间复用myMuniCty的结果，匿名类型就不太方便了，这时候可以创建一个实体类来承载查询结果：

' 定义实体类，字段类型根据你的数据库实际类型调整
Public Class MuniCountyDto
    Public Property MUNIDNUMBER As Integer
    Public Property Premcode As String
    Public Property COUNTYCD As String
    Public Property Village As String
    Public Property TOWN As String
    Public Property CITY As String
    Public Property ZipCode As String
    Public Property County As String
    Public Property State As String
    Public Property Zone As String
    Public Property MTRate As Decimal
    Public Property AddTax As Decimal
    Public Property MTAZone As String
    Public Property Premises As String
End Class

然后修改myMuniCty查询，返回这个类的实例：

Dim myMuniCty = (From muns In dc.MUNs 
                 Join ctys In dc.Counties On muns.COUNTYCD Equals ctys.CountyCd 
                 Select New MuniCountyDto With {
                     .MUNIDNUMBER = muns.MUNIDNUMBER,
                     .Premcode = muns.Premcode,
                     .COUNTYCD = muns.COUNTYCD,
                     .Village = muns.Village,
                     .TOWN = muns.TOWN,
                     .CITY = muns.CITY,
                     .ZipCode = muns.ZipCode,
                     .County = ctys.County,
                     .State = ctys.State,
                     .Zone = ctys.Zone,
                     .MTRate = ctys.MTRate,
                     .AddTax = ctys.AddTax,
                     .MTAZone = ctys.MTAZone,
                     .Premises = muns.Premises
                 }).ToList() ' 可以ToList()提前加载，也可以保持延迟执行

之后主查询就可以明确访问MuniCountyDto的所有属性，不会出现找不到字段的问题：

Dim finalQuery = From apps In dc.Applications
                 Join props In dc.Property On apps.APPLICATIONSIDNUMBER Equals props.APPLICATIONSIDNUMBER
                 Group Join muni In myMuniCty On props.PremIDNumber Equals muni.MUNIDNUMBER Into muniMatches = Group
                 From muni In muniMatches.DefaultIfEmpty()
                 Select New With {
                     ' ... 前面的字段和方案一一致
                     .Premises = If(muni IsNot Nothing, muni.Premises, Nothing),
                     .Village = If(muni IsNot Nothing, muni.Village, Nothing),
                     ' ... 其他MuniCounty字段
                     .State = apps.Statecode
                 }

这个方案的优势是类型安全，代码可读性更高，也方便后续维护和扩展。

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