C#中如何选择.Include与.Join实现双表数据查询？

问题描述 #

我有两张表Exception_T和SupplierByException_T，需要基于表关联获取目标数据。查询逻辑是先设置基础过滤条件，再根据可选条件动态拼接Where子句。试了三种方法都没得到预期结果：

• 使用.Include：当Exception_T有记录但关联的供应商表无记录时，仍会返回该Exception记录，而不是0条；
• 使用.Join：一是存在上述相同问题，二会返回重复记录；
• 使用LINQ的.GroupBy：直接抛出无法转换LINQ表达式的异常。

相关代码 #

SQL实体类 #

public class Exception 
{
    [Key]
    public int ExceptionId { get; set; }
    [Required, MaxLength(10), Unicode(false)]
    public string Location { get; set; }
    [Required]
    public long StockNumber { get; set; }
    [Required, MaxLength(17), Column(TypeName = "char(17)")]
    public string Vin { get; set; }
    public decimal TotalCarliCost { get; set; }
    public decimal TotalCostSubmitted { get; set; }
    public DateTimeOffset? EarliestPaymentDate { get; set; }
    public bool? HasPartLineItems { get; set; }
    public bool? HasSubletItems { get; set; }
    [Required]
    public List<SupplierByException> Suppliers { get; set; }
}

public class SupplierByException 
{
    [Key] 
    public int SupplierByExceptionId { get; set; }
    [Required]
    public long StockNumber { get; set; }
    [Required, MaxLength(500), Unicode(false)]
    public string SupplierName { get; set; }
    public bool? TiedToPart { get; set; }
    public bool? TiedToSublet { get; set; }
    public int ExceptionId { get; set; }
}

Repo.cs #

private IQueryable<Exception> GenerateBaseQuery(string location, string stockNumber = "", string exceptionType = "", string supplierName = "")
{
    // // Option-1
    // var query = _dbContext.Exception.AsNoTracking()
    //     .Include(x => x.Suppliers)
    //     .Where(_ => _.Location.Equals(location));
    
    // // Option-2
    // var query = from exc in _dbContext.Exception.AsNoTracking()
    //     join sup in _dbContext.SupplierByException.AsNoTracking() 
    //         on exc.ExceptionId equals sup.ExceptionId
    //     where exc.Location.Equals(location)
    //     select exc;
    // // query = query.Include(x => x.Suppliers);
    
    // Once above base query is created, using optional where clauses like below.
    if (!string.IsNullOrWhiteSpace(stockNumber))
    {
        var convertedStockNumber = long.Parse(stockNumber);
        query = query.Where(e => e.StockNumber.Equals(convertedStockNumber));
    }
    
    if (!string.IsNullOrWhiteSpace(exceptionType))
    {
        if (exceptionType.ToLowerInvariant()
            .Equals(ExceptionTypeConstants.ExceptionType.Parts.ToLowerInvariant()))
        {
            query = query.Where(e => e.HasPartLineItems.Equals(true));
        }
        
        if (exceptionType.ToLowerInvariant()
            .Equals(ExceptionTypeConstants.ExceptionType.Sublets.ToLowerInvariant()))
        {
            query = query.Where(e => e.HasSubletItems.Equals(true));
        }
        
        if (exceptionType.ToLowerInvariant()
            .Equals(ExceptionTypeConstants.ExceptionType.All.ToLowerInvariant()))
        {
            query = query.Where(e => e.HasPartLineItems.Equals(true) || e.HasSubletItems.Equals(true));
        }
    }
    
    if (!string.IsNullOrWhiteSpace(supplierName))
    {
        query = query.Include(o => o.Suppliers.Where(s => s.SupplierName == supplierName));
    }

    return query;
}

解决方案 #

核心思路 #

要实现仅返回存在关联供应商记录的Exception，同时支持动态过滤，需要：

1. 用e.Suppliers.Any()过滤无关联供应商的主表记录；
2. 供应商名称过滤需同时过滤主表（确保主表存在该供应商）和关联数据（按需加载符合条件的供应商）；
3. 避免Join导致的重复，利用EF导航属性自动处理关联逻辑。

修改后的代码 #

private IQueryable<Exception> GenerateBaseQuery(string location, string stockNumber = "", string exceptionType = "", string supplierName = "")
{
    // 基础查询：过滤指定Location，且必须存在关联供应商
    var query = _dbContext.Exception.AsNoTracking()
        .Where(e => e.Location == location && e.Suppliers.Any());

    // 动态过滤库存编号：增加解析校验避免异常
    if (!string.IsNullOrWhiteSpace(stockNumber) && long.TryParse(stockNumber, out var convertedStockNumber))
    {
        query = query.Where(e => e.StockNumber == convertedStockNumber);
    }

    // 动态过滤异常类型：用else if避免多条件误触发
    if (!string.IsNullOrWhiteSpace(exceptionType))
    {
        var lowerType = exceptionType.ToLowerInvariant();
        var partsType = ExceptionTypeConstants.ExceptionType.Parts.ToLowerInvariant();
        var subletsType = ExceptionTypeConstants.ExceptionType.Sublets.ToLowerInvariant();
        var allType = ExceptionTypeConstants.ExceptionType.All.ToLowerInvariant();

        if (lowerType == partsType)
        {
            query = query.Where(e => e.HasPartLineItems == true);
        }
        else if (lowerType == subletsType)
        {
            query = query.Where(e => e.HasSubletItems == true);
        }
        else if (lowerType == allType)
        {
            query = query.Where(e => e.HasPartLineItems == true || e.HasSubletItems == true);
        }
    }

    // 动态过滤供应商名称
    if (!string.IsNullOrWhiteSpace(supplierName))
    {
        // 先过滤主表：确保当前Exception有指定名称的供应商
        query = query.Where(e => e.Suppliers.Any(s => s.SupplierName.Equals(supplierName, StringComparison.OrdinalIgnoreCase)));
        // 再加载符合条件的关联供应商数据
        query = query.Include(e => e.Suppliers.Where(s => s.SupplierName.Equals(supplierName, StringComparison.OrdinalIgnoreCase)));
    }
    else
    {
        // 无供应商过滤时，加载所有关联供应商
        query = query.Include(e => e.Suppliers);
    }

    return query;
}

关键修改说明 #

• 基础查询加入e.Suppliers.Any()，直接排除没有关联供应商的Exception记录；
• 供应商名称过滤时，先用Any()过滤主表，再用Include的Where加载目标供应商数据，避免主表返回不符合条件的记录；
• 替换Equals为==简化代码，增加long.TryParse防止输入非法库存编号时抛出异常；
• 异常类型判断改用else if，避免多个条件同时命中的问题；
• 用StringComparison.OrdinalIgnoreCase做不区分大小写匹配，比ToLowerInvariant()更高效且避免文化差异问题。

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