如何修改存储过程dbo.spStaysSearch添加参数承接EntityFramework侧过滤逻辑

1. 调整存储过程支持过滤参数 #

给原有存储过程添加所有过滤规则对应的可选参数（默认值设为NULL，不传对应参数时不会触发该条件过滤），同时在最终查询的WHERE子句中添加对应的判断逻辑即可，修改后的完整存储过程代码如下：

CREATE PROCEDURE [dbo].[spStaysSearch] 
    @OccupantId INT = NULL,
    @OccupantFileAs NVARCHAR(500) = NULL,
    @BuildingName NVARCHAR(200) = NULL,
    @CaseManager NVARCHAR(200) = NULL,
    @IntakeDateStart DATETIME = NULL,
    @IntakeDateEnd DATETIME = NULL,
    @ExitDateStart DATETIME = NULL,
    @ExitDateEnd DATETIME = NULL,
    @IsActive BIT = NULL
AS
BEGIN
    SELECT 
        tblOccupantStays.StayID, 
        COUNT(tblOccupantStays.OccupantStayID) AS CountOfOccupantStayID
    INTO 
        #OccupantStays_CountOfChildren
    FROM 
        tblOccupantStays 
    INNER JOIN 
        tblOccupant ON tblOccupantStays.OccupantID = tblOccupant.OccupantID
    WHERE 
        tblOccupant.OccupantType LIKE 'Child'
    GROUP BY 
        tblOccupantStays.StayID;

    SELECT 
        tblOccupant.OccupantID, tblOccupant.OccupantType
    INTO 
        #OccupantsAdults
    FROM 
        tblOccupant
    WHERE 
        tblOccupant.OccupantType = 'Adult';

    SELECT 
        tblStayBillingHx.StayID, 
        MAX(tblStayBillingHx.BillSentDate) AS MaxOfBillSentDate
    INTO 
        #StaysMaxBillSentDate
    FROM 
        tblStayBillingHx 
    GROUP BY 
        tblStayBillingHx.StayID;
 
    SELECT 
        tblStays.*, tblOccupant.OccupantID, 
        tblOccupant.FileAs AS OccupantFileAs, 
        IIF(tblStays.BuildingName LIKE 'Main Shelter',
               tblOccupant.OCFSMainNumber,
               tblOccupant.OCFSNorthNumber) AS StayOCFSNumber, 
        COALESCE([CountOfOccupantStayID], 0) AS CountOfChildren, 
        tblCaseManager.FileAs AS CaseManager,
        #StaysMaxBillSentDate.MaxOfBillSentDate
    FROM 
        (((((tblStays 
    LEFT JOIN 
        tblOccupantStays ON tblStays.StayID = tblOccupantStays.StayID) 
    LEFT JOIN 
        tblOccupant ON tblOccupantStays.OccupantID = tblOccupant.OccupantID) 
    LEFT JOIN 
        #OccupantStays_CountOfChildren ON tblStays.StayID = #OccupantStays_CountOfChildren.StayID) 
    LEFT JOIN 
        #OccupantsAdults ON tblOccupant.OccupantID = #OccupantsAdults.OccupantID)
    LEFT JOIN 
        tblCaseManager ON tblStays.CaseManagerID = tblCaseManager.CaseManagerID) 
    LEFT JOIN 
        #StaysMaxBillSentDate ON tblStays.StayID = #StaysMaxBillSentDate.StayID
    WHERE
        (@OccupantId IS NULL OR tblOccupant.OccupantID = @OccupantId)
        AND (@OccupantFileAs IS NULL OR tblOccupant.FileAs = @OccupantFileAs)
        AND (@BuildingName IS NULL OR tblStays.BuildingName = @BuildingName)
        AND (@CaseManager IS NULL OR tblCaseManager.FileAs = @CaseManager)
        AND (@IntakeDateStart IS NULL OR tblStays.StartDate >= @IntakeDateStart)
        AND (@IntakeDateEnd IS NULL OR tblStays.StartDate <= @IntakeDateEnd)
        AND (@ExitDateStart IS NULL OR tblStays.EndDate >= @ExitDateStart)
        AND (@ExitDateEnd IS NULL OR tblStays.EndDate <= @ExitDateEnd)
        AND (@IsActive IS NULL OR tblStays.IsActive = @IsActive)
    ORDER BY 
        tblStays.StartDate, tblOccupant.FileAs;
END

注：上面参数的类型和长度可以根据你实际表结构的字段定义调整。

2. 修改C#调用代码 #

原来的代码是先全量拉取存储过程返回结果，再在内存中做过滤，性能差。现在直接把查询参数传给存储过程，在数据库层面完成过滤，修改后的代码如下：

private IQueryable<spStaysSearch> getSearchData(StaySearchViewModel model)
{
    var parameters = new List<SqlParameter>();

    if (model.OccupantId.HasValue)
        parameters.Add(new SqlParameter("@OccupantId", model.OccupantId.Value));

    if (!string.IsNullOrWhiteSpace(model.OccupantFileAs))
        parameters.Add(new SqlParameter("@OccupantFileAs", model.OccupantFileAs));

    if (!string.IsNullOrWhiteSpace(model.BuildingName))
        parameters.Add(new SqlParameter("@BuildingName", model.BuildingName));

    if (!string.IsNullOrWhiteSpace(model.CaseManager))
        parameters.Add(new SqlParameter("@CaseManager", model.CaseManager));

    if (model.IntakeDateStart.HasValue)
        parameters.Add(new SqlParameter("@IntakeDateStart", model.IntakeDateStart.Value));

    if (model.IntakeDateEnd.HasValue)
        parameters.Add(new SqlParameter("@IntakeDateEnd", model.IntakeDateEnd.Value));

    if (model.ExitDateStart.HasValue)
        parameters.Add(new SqlParameter("@ExitDateStart", model.ExitDateStart.Value));

    if (model.ExitDateEnd.HasValue)
        parameters.Add(new SqlParameter("@ExitDateEnd", model.ExitDateEnd.Value));

    if (model.IsActive.HasValue)
        parameters.Add(new SqlParameter("@IsActive", model.IsActive.Value));

    // 拼接执行语句，未传入的参数会自动使用存储过程定义的NULL默认值
    string execSql = "EXEC dbo.spStaysSearch " + string.Join(", ", parameters.Select(p => p.ParameterName));
    return db.SpStaySearches.FromSqlRaw(execSql, parameters.ToArray()).AsQueryable();
}

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