Linq双仓储合并查询排查及简化方案请求（附代码模型）

需求：合并FavouriteProject与Project两个仓储的查询，使用Linq实现单请求查询。
现状：现有代码可运行但无输出，需排查错误。

用户提供的原始代码、实体模型及过滤器定义：

var result =  _favouriteProjectRepository.Entity()
                                         .Where(x => x.UserId == userId)
                                         .Select(x => new FavouriteProject
                                                      {
                                                          ProjectId = x.ProjectId,
                                                      }).ToList();    

var resultset = await _projectrepository.Entity()
                                  .Where(x => (filters.FiscalYear == null ||               
                                               filters.FiscalYear.Contains(Convert.ToInt32(x.ContractNo.ToString().Substring(0, 2)))) && 
                                              (filters.IsFavourite == false && 
                                               result.Contains(x.Id).ToString()))
                                  .Select(x => new Tests
                                               {
                                                   Id = x.Id,
                                                   Name = x.Name,
                                                   AdvertisementStatusId = x.AdvertisementStatusId,
                                                   CreatedOn = x.CreatedOn,
                                                   ContractNo = x.ContractN0,
                                               })
                                  .OrderBy(x => x.Name)
                                  .ToListAsync();

// 实体模型
public class FavouriteProject
{
    public int ProjectId { get; set; }
    public string UserId { get; set; }
    public virtual Project Project { get; set; }
}

public class Tests
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ContractNo { get; set; }
    public int? AdvertisementStatusId { get; set; }
    public DateTime? CreatedOn { get; set; }
    public virtual ICollection<FavouriteCompany> FavouriteCompany { get; set; }
}

// 过滤器
public class ProjectFilters
{
    public List<int> FiscalYear { get; set; }
    public bool IsFavourite { get; set; }
}

// Project模型
public class Project
{
    public int Id { get; set; }
    public string ProcurmentNo { get; set; }
    public int? ContractNo { get; set; }
    public int? ProjectSizeId { get; set; }
    public int? SelectionProcedureId { get; set; }
    public int? ResponseRequestedId { get; set; }
    public int? AdvertisementStatusId { get; set; }
    public int? NoOfPages { get; set; }
    public string PreQualificationRequirements { get; set; }
    public string Description { get; set; }
    public string Name { get; set; }
    public string MultilineDescription { get; set; }
    public string BridgDesignWorkType { get; set; }
    public string DbeMbeRequirements { get; set; }
    public string SpecialNote { get; set; }
    public decimal? BudgetAmount { get; set; }
    public decimal? EstimatedContractAmount { get; set; }
    public decimal? MaxumumContractAmount { get; set; }
    public bool? IsMultipleContracts { get; set; }
    public string ProposedScopeLoc { get; set; }
    public string BondingRequirements { get; set; }
    public string TechQuestionsAddressedTo { get; set; }
    public int Status { get; set; }
    public int? DistrictId { get; set; }
    public DateTime? CreatedOn { get; set; }
    public string ResponseEmail { get; set; }

    public string ViewRFP { get; set; }
    public string AdditionalInformation { get; set; }
    public string LowBidDesign { get; set; }
    public string AdminNote { get; set; }
    public string MeetingLocationNotes { get; set; }
    public string FdotKey { get; set; }
    public bool IsUpdatesLocked { get; set; }
    public bool? BdiProjectIndicator { get; set; }
    public string LiabilityInsurance { get; set; }
    public string ProjectThreshold { get; set; }
    public int NumberOfContracts { get; set; }
    public int? RelatedProjectCount { get; set; }

    public string AdvertisementSpecialNotes { get; set; }
    public string FMNSpecialNotes { get; set; }

    public virtual ProjectSize ProjectSize { get; set; }
    public virtual SelectionProcedure SelectionProcedure { get; set; }
    public virtual ResponseRequested ResponseRequested { get; set; }
    public virtual AdvertisementStatus AdvertisementStatus { get; set; }
    public virtual District District { get; set; }
    public virtual ICollection<ProjectEvent> ProjectEvents { get; set; }
    public virtual ICollection<FinancialManagementNumber> FinancialManagementNumbers { get; set; }
    public virtual ICollection<ProjectWorkGroup> ProjectWorkGroups { get; set; }
    public virtual ICollection<ScrapedLink> ScrapedLinks { get; set; }
    public virtual ICollection<ProjectStandardNote> ProjectStandardNotes { get; set; }
    public virtual ICollection<FtpFileProject> FtpFileProjects { get; set; }
    public virtual ICollection<CompanyProject> CompanyProjects { get; set; }
    public virtual ICollection<ContactProject> ContactProjects { get; set; }
    public virtual ICollection<ProjectUnderUtilizedWorkGroup> ProjectUnderUtilizedWorkGroups { get; set; }
    public virtual ICollection<WorkProgram> WorkPrograms { get; set; }

    [NotMapped]
    public virtual ICollection<ScrapedProjectModel> ScrapedProjects { get; set; }

   // public virtual FavouriteProject FavouriteProject { get; set; }
}

错误排查 #

• 条件逻辑混乱：filters.IsFavourite == false && result.Contains(x.Id).ToString() 将布尔值转成字符串后参与逻辑判断，完全不符合需求逻辑，且result是FavouriteProject对象集合，直接用x.Id（int类型）判断包含会因为类型不匹配永远返回false。
• 空引用风险：x.ContractNo.ToString().Substring(0,2) 未判断ContractNo是否为null，会直接抛出空引用异常。
• 属性拼写错误：ContractN0（数字0）应为ContractNo（字母o），导致属性映射失败。

优化后的单请求Linq查询方案 #

通过Linq关联查询直接在数据库层面完成所有筛选，避免多次数据库请求，同时修复所有错误：

方案一：使用LeftJoin关联双表 #

var query = _projectrepository.Entity()
    // 关联收藏表，标记当前用户的收藏状态
    .LeftJoin(_favouriteProjectRepository.Entity(),
        project => project.Id,
        fav => fav.ProjectId,
        (project, fav) => new 
        { 
            Project = project, 
            IsUserFavourite = fav != null && fav.UserId == userId 
        })
    // 应用筛选条件
    .Where(x => 
        // 财政年度筛选：处理空值，且只在ContractNo有值时匹配
        (filters.FiscalYear == null || filters.FiscalYear.Any(fy => 
            x.Project.ContractNo.HasValue && 
            x.Project.ContractNo.ToString().StartsWith(fy.ToString().PadLeft(2, '0')))) &&
        // 收藏状态筛选：IsFavourite为true时只保留收藏项目，false时保留所有
        (!filters.IsFavourite || x.IsUserFavourite)
    )
    // 映射到Tests模型
    .Select(x => new Tests
    {
        Id = x.Project.Id,
        Name = x.Project.Name,
        AdvertisementStatusId = x.Project.AdvertisementStatusId,
        CreatedOn = x.Project.CreatedOn,
        ContractNo = x.Project.ContractNo // 修正拼写错误
    })
    .OrderBy(x => x.Name);

var resultset = await query.ToListAsync();

方案二：利用导航属性（需启用Project的收藏导航） #

先取消Project模型中// public virtual FavouriteProject FavouriteProject { get; set; }的注释，或者改为集合类型（支持多用户收藏）：

public virtual ICollection<FavouriteProject> FavouriteProjects { get; set; }

然后使用导航属性简化查询：

var query = _projectrepository.Entity()
    .Where(x => 
        // 财政年度筛选
        (filters.FiscalYear == null || filters.FiscalYear.Any(fy => 
            x.ContractNo.HasValue && 
            x.ContractNo.ToString().StartsWith(fy.ToString().PadLeft(2, '0')))) &&
        // 收藏状态筛选
        (!filters.IsFavourite || x.FavouriteProjects.Any(fav => fav.UserId == userId))
    )
    .Select(x => new Tests
    {
        Id = x.Id,
        Name = x.Name,
        AdvertisementStatusId = x.AdvertisementStatusId,
        CreatedOn = x.CreatedOn,
        ContractNo = x.ContractNo
    })
    .OrderBy(x => x.Name);

var resultset = await query.ToListAsync();

关键优化点 #

1. 单请求完成所有操作：所有筛选逻辑在数据库层面执行，避免先查收藏列表再二次过滤的冗余请求。
2. 空值安全处理：先判断ContractNo是否有值，再进行字符串操作，避免空引用异常。
3. 逻辑修正：根据IsFavourite的布尔值，正确切换“只查收藏”或“查全部”的逻辑。
4. 健壮的匹配规则：用StartsWith匹配财政年度，并补前导0适配两位数格式。

内容的提问来源于stack exchange，提问作者Manisaikumar .ECE2022gna