LINQ to SQL如何实现过滤子集合且不加载冗余数据

你遇到的报错是因为LINQ to SQL不允许在IQueryable查询链路中直接构造DBML映射的实体类型，这类实体构造操作只能在内存中执行。以下是两种可实现需求、且不会加载冗余Regulators数据的方案：

前置准备 #

首先实现ToEntitySet扩展方法，用于把集合转为LINQ to SQL要求的EntitySet类型：

public static EntitySet<T> ToEntitySet<T>(this IEnumerable<T> source) where T : class
{
    var entitySet = new EntitySet<T>();
    entitySet.AddRange(source);
    return entitySet;
}

核心逻辑是先通过IQueryable投影到匿名类，所有过滤逻辑都在SQL层面执行，拿到内存结果后再转换为实体类，全程不会加载不符合条件的Regulators数据。

// 提前转大写避免重复计算，也可根据业务改用ToUpperInvariant
var upperFilter = filterText.ToUpper();

var regOrgs = DataContext.RegulatorOrganizationViews
    // 先筛选符合条件的机构
    .Where(ro => ro.regulatorOrgName.ToUpper().Contains(upperFilter)
        || ro.RegulatorsViews.Any(r => r.regulatorName.ToUpper().Contains(upperFilter)))
    // 投影到匿名类，此步骤会翻译成SQL在数据库端执行
    .Select(ro => new 
    {
        ro.regulatorOrgId,
        ro.regulatorOrgName,
        FilteredRegulators = ro.RegulatorsViews
            .Where(r => r.regulatorName.ToUpper().Contains(upperFilter))
            .Select(r => new { r.regulatorId, r.regulatorName })
    })
    // 切换到内存执行后续实体构造操作
    .AsEnumerable()
    // 转换为目标实体类
    .Select(ro => new RegulatorsOrganizationView
    {
        regulatorOrgId = ro.regulatorOrgId,
        regulatorOrgName = ro.regulatorOrgName,
        regulatorsViews = ro.FilteredRegulators
            .Select(r => new RegulatorsView
            {
                regulatorId = r.regulatorId,
                regulatorName = r.regulatorName
            })
            .ToEntitySet()
    })
    .ToList();

如果机构和监管员数据量非常大，可以分两次精准查询减少数据传输开销：

var upperFilter = filterText.ToUpper();

// 第一步：查询所有符合过滤条件的监管员，记录关联的机构ID
var matchedRegulators = DataContext.RegulatorsViews
    .Where(r => r.regulatorName.ToUpper().Contains(upperFilter))
    .Select(r => new { r.regulatorId, r.regulatorName, r.regulatorOrgId })
    .ToList();
// 提取符合条件的监管员关联的机构ID列表
var matchedOrgIds = matchedRegulators.Select(r => r.regulatorOrgId).Distinct().ToList();

// 第二步：查询符合条件的机构
var regOrgs = DataContext.RegulatorOrganizationViews
    .Where(ro => ro.regulatorOrgName.ToUpper().Contains(upperFilter)
        || matchedOrgIds.Contains(ro.regulatorOrgId))
    .AsEnumerable()
    // 为机构匹配对应监管员
    .Select(ro => new RegulatorsOrganizationView
    {
        regulatorOrgId = ro.regulatorOrgId,
        regulatorOrgName = ro.regulatorOrgName,
        regulatorsViews = matchedRegulators
            .Where(r => r.regulatorOrgId == ro.regulatorOrgId)
            .Select(r => new RegulatorsView
            {
                regulatorId = r.regulatorId,
                regulatorName = r.regulatorName
            })
            .ToEntitySet()
    })
    .ToList();

两种方案的所有过滤逻辑都会被翻译成SQL执行，完全不会加载冗余的监管员数据，满足你的性能需求。

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