EF Core代码优先场景下如何引用多对多关联表构建LINQ查询

因为EF Core在隐式配置多对多关系时（未显式定义联结实体FeatureMember），不会将自动生成的联结表暴露为DbSet，所以不能直接在LINQ中引用该表，而是要通过实体的导航属性来构建关联查询。

前提确认 #

确保你的实体类已正确定义导航属性：

• Member类包含ICollection<Feature> Features
• Feature类包含ICollection<Member> Members
• Member类包含MailAddress MailAddress
• MailAddress类包含Geography Geography

方法1：从Feature出发构建查询 #

这种方式更直观，直接筛选目标Feature，再关联到对应的Member及其关联数据：

public async Task<IEnumerable<MapViewModel>> GetMembersByFeatureIds(int[] selectedFeatureIds)
{
    using var context = new MappingContext();

    var result = await context.Feature
        .Where(f => selectedFeatureIds.Contains(f.FeatureId))
        .SelectMany(f => f.Members, (f, m) => new { f, m }) // 关联多对多的Member
        .Select(x => new 
        {
            x.m.MemberMailingName,
            x.m.MailAddress.AddressStreetNumber,
            x.m.MailAddress.AddressStreetName,
            x.m.MailAddress.AddressCity,
            x.m.MailAddress.AddressState,
            x.m.MailAddress.AddressZip,
            x.m.MailAddress.Geography.Latitude,
            x.m.MailAddress.Geography.Longitude,
            x.f.FeatureName
        })
        .OrderBy(x => x.FeatureName) // 对应SQL的ORDER BY f.FeatureId，也可改为FeatureId
        .ToListAsync();

    // 转换为你的视图模型（如果需要）
    return result.Select(item => new MapViewModel
    {
        MemberName = item.MemberMailingName,
        StreetNumber = item.AddressStreetNumber,
        StreetName = item.AddressStreetName,
        City = item.AddressCity,
        State = item.AddressState,
        Zip = item.AddressZip,
        Latitude = item.Latitude,
        Longitude = item.Longitude,
        FeatureName = item.FeatureName
    });
}

方法2：从Member出发构建查询 #

也可以从Member开始，通过导航属性过滤关联的Feature：

public async Task<IEnumerable<MapViewModel>> GetMembersByFeatureIds(int[] selectedFeatureIds)
{
    using var context = new MappingContext();

    var result = await context.Member
        .Where(m => m.Features.Any(f => selectedFeatureIds.Contains(f.FeatureId)))
        .Select(m => new 
        {
            m.MemberMailingName,
            m.MailAddress.AddressStreetNumber,
            m.MailAddress.AddressStreetName,
            m.MailAddress.AddressCity,
            m.MailAddress.AddressState,
            m.MailAddress.AddressZip,
            m.MailAddress.Geography.Latitude,
            m.MailAddress.Geography.Longitude,
            FeatureNames = m.Features.Where(f => selectedFeatureIds.Contains(f.FeatureId)).Select(f => f.FeatureName)
        })
        .SelectMany(x => x.FeatureNames, (x, featureName) => new MapViewModel
        {
            MemberName = x.MemberMailingName,
            StreetNumber = x.AddressStreetNumber,
            StreetName = x.AddressStreetName,
            City = x.AddressCity,
            State = x.AddressState,
            Zip = x.AddressZip,
            Latitude = x.Latitude,
            Longitude = x.Longitude,
            FeatureName = featureName
        })
        .OrderBy(vm => vm.FeatureName)
        .ToListAsync();

    return result;
}

关键说明 #

• 两种方法都会生成与你提供的SQL逻辑一致的查询语句，EF Core会自动处理多对多的联结表关联。
• 上述Select方式只查询需要的字段，比预加载导航属性的Include更高效。
• 注意处理导航属性为null的情况（比如Member没有绑定MailAddress），可添加Where过滤或使用空值运算符?.规避异常。

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