Entity Framework与SQL Server存储过程动态筛选实现及替代方案咨询

一、Entity Framework中实现动态筛选的C#方法 #

你可以利用EF的延迟执行特性，逐步构建查询条件，只有最终调用ToList()时才会生成SQL并执行，完美匹配原存储过程的动态筛选逻辑。同时注意修正原C#代码中的关联错误（原存储过程是关联s.HistiryID = shd.SaleID，原代码误写为关联row2.ShDetailID）：

public List<object> GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
                                         DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
                                         int? CustomerID = null)
{
    using (EfM.Model1 db = new Model1()) // 使用using确保上下文自动释放
    {
        // 构建基础查询（IQueryable延迟执行，此时未生成SQL）
        var query = from row1 in db.tblSalesHistories
                    join row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.SaleID
                    join row3 in db.tblCities on row1.SaleCityID equals row3.CityID
                    join row4 in db.tblCustomers on row1.CustomerID equals row4.CsID
                    join row5 in db.tblStaffs on row1.SalesPersonID equals row5.StfID
                    join row6 in db.tblGoods on row2.GoodsID equals row6.GoodsID
                    select new
                    {
                        row1.HistiryID,
                        row2.ShDetailID,
                        row1.DateOfSale,
                        row1.SaleCityID,
                        row1.SalesPersonID,
                        row1.CustomerID,
                        row5.StfName,
                        row5.StfSname,
                        row6.GoodsName,
                        row4.CsName,
                        row4.CsSname,
                        row3.CityName,
                        row2.GoodsQuantity,
                        row2.UnitPrice,
                        row2.Discount,
                        row2.Tax,
                        row2.OtherCosts,
                        row2.TotalPriceBefore,
                        row2.TotalPriceAfterCosts
                    };

        // 根据参数动态添加筛选条件
        if (HistoryID.HasValue)
            query = query.Where(x => x.HistiryID == HistoryID.Value);
        if (ShDetailID.HasValue)
            query = query.Where(x => x.ShDetailID == ShDetailID.Value);
        if (CustomerID.HasValue)
            query = query.Where(x => x.CustomerID == CustomerID.Value);
        if (DateOfSaleFrom.HasValue)
            query = query.Where(x => x.DateOfSale >= DateOfSaleFrom.Value);
        if (DateOfSaleTo.HasValue)
            query = query.Where(x => x.DateOfSale <= DateOfSaleTo.Value);

        // 执行查询并返回结果
        return query.ToList<object>();
    }
}

二、无需动态查询的列表筛选方案 #

1. 参数化静态SQL存储过程 #

放弃动态拼接SQL，直接写包含所有可能条件的静态查询，用IS NULL判断是否忽略参数，调试和维护更简单：

CREATE PROCEDURE dbo.GetSalesWithDetails_Static
    (@HistoryID INT = NULL,
     @ShDetailID INT = NULL,
     @DateOfSaleFrom DateTime = NULL,
     @DateOfSaleTo DateTime = NULL,
     @CustomerID INT = NULL)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  s.HistoryID, 
            shd.ShDetailID, 
            s.DateOfSale, 
            s.SaleCityID, 
            s.SalesPersonID, 
            s.CustomerID, 
            sf.StfName,
            sf.StfSname, 
            gd.GoodsName, 
            cu.CsName, 
            cu.CsSname, 
            c.CityName,
            shd.GoodsQuantity, 
            shd.UnitPrice, 
            shd.Discount, 
            shd.Tax, 
            shd.OtherCosts, 
            shd.TotalPriceBefore, 
            shd.TotalPriceAfterCosts
    FROM        dbo.tblCity AS c 
    INNER JOIN  dbo.tblSalesHistory AS s ON c.CityID = s.SaleCityID 
    INNER JOIN  dbo.tblCustomers AS cu ON s.CustomerID = cu.CsID 
    INNER JOIN  dbo.tblSalesHistoryDetails AS shd ON s.HistiryID = shd.SaleID 
    INNER JOIN  dbo.tblStaffs AS sf ON s.SalesPersonID = sf.StfID 
    INNER JOIN  dbo.tblGoods AS gd ON shd.GoodsID = gd.GoodsID -- 修正原存储过程的CROSS JOIN错误，避免笛卡尔积
    WHERE 
        (@HistoryID IS NULL OR s.HistoryID = @HistoryID)
        AND (@ShDetailID IS NULL OR shd.ShDetailID = @ShDetailID)
        AND (@CustomerID IS NULL OR s.CustomerID = @CustomerID)
        AND (@DateOfSaleFrom IS NULL OR s.DateOfSale >= @DateOfSaleFrom)
        AND (@DateOfSaleTo IS NULL OR s.DateOfSale <= @DateOfSaleTo)
    WITH RESULT SETS
    ((
        SaleHistoryID INT Not Null, 
        ShDetailID INT Not Null, 
        DateOfSale DateTime Not Null, 
        SaleCityID INT Not Null, 
        SalesPersonID INT Not Null, 
        CustomerID INT Not Null, 
        StfName Nvarchar(150) Not Null, 
        StfSname Nvarchar(150) Not Null, 
        GoodsName Nvarchar(150) Not Null, 
        CsName Nvarchar(150) Not Null, 
        CsSname Nvarchar(150) Not Null, 
        CityName Nvarchar(150) Not Null, 
        GoodsQuantity Decimal(18,2) not null, 
        UnitPrice Decimal(18,2) not null, 
        Discount Decimal(18,2) not null, 
        Tax Decimal(18,2) not null, 
        OtherCosts Decimal(18,2) not null, 
        TotalPriceBefore Decimal(18,2) not null, 
        TotalPriceAfterCosts Decimal(18,2) not null
    ))
END

2. 表值函数（TVF）替代存储过程 #

表值函数逻辑清晰，支持参数化查询，还能直接在EF中映射为DbSet调用：

CREATE FUNCTION dbo.GetSalesWithDetails_TVF
(
    @HistoryID INT = NULL,
    @ShDetailID INT = NULL,
    @DateOfSaleFrom DateTime = NULL,
    @DateOfSaleTo DateTime = NULL,
    @CustomerID INT = NULL
)
RETURNS TABLE
AS
RETURN
(
    SELECT  s.HistoryID, 
            shd.ShDetailID, 
            s.DateOfSale, 
            s.SaleCityID, 
            s.SalesPersonID, 
            s.CustomerID, 
            sf.StfName,
            sf.StfSname, 
            gd.GoodsName, 
            cu.CsName, 
            cu.CsSname, 
            c.CityName,
            shd.GoodsQuantity, 
            shd.UnitPrice, 
            shd.Discount, 
            shd.Tax, 
            shd.OtherCosts, 
            shd.TotalPriceBefore, 
            shd.TotalPriceAfterCosts
    FROM        dbo.tblCity AS c 
    INNER JOIN  dbo.tblSalesHistory AS s ON c.CityID = s.SaleCityID 
    INNER JOIN  dbo.tblCustomers AS cu ON s.CustomerID = cu.CsID 
    INNER JOIN  dbo.tblSalesHistoryDetails AS shd ON s.HistiryID = shd.SaleID 
    INNER JOIN  dbo.tblStaffs AS sf ON s.SalesPersonID = sf.StfID 
    INNER JOIN  dbo.tblGoods AS gd ON shd.GoodsID = gd.GoodsID
    WHERE 
        (@HistoryID IS NULL OR s.HistoryID = @HistoryID)
        AND (@ShDetailID IS NULL OR shd.ShDetailID = @ShDetailID)
        AND (@CustomerID IS NULL OR s.CustomerID = @CustomerID)
        AND (@DateOfSaleFrom IS NULL OR s.DateOfSale >= @DateOfSaleFrom)
        AND (@DateOfSaleTo IS NULL OR s.DateOfSale <= @DateOfSaleTo)
)

EF中调用方式：

var results = db.GetSalesWithDetails_TVF(HistoryID, ShDetailID, DateOfSaleFrom, DateOfSaleTo, CustomerID).ToList();

3. EF原生LINQ动态构建（即第一部分的C#方法） #

通过逐步添加Where条件构建查询，让EF自动生成参数化SQL，完全避免手动拼接SQL，调试时可通过query.ToString()查看生成的SQL，排查问题更方便。

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