在实体框架和SQL Server存储过程中创建动态过滤器的最佳方法

snz8szmq 于 2022-09-18 发布在 Java

关注(0)|答案(3)|浏览(102)

我们有一个动态筛选的过程，它基于输入参数动态生成筛选器，并且在输出中，如果有记录，则根据结果集显示它。

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

    DECLARE @Sql Nvarchar(Max),
            @Paramlist Nvarchar(Max),
            @AllColumn Nvarchar(max)

    SET @Sql = N'
            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 CROSS JOIN
                        dbo.tblGoods AS gd 
            where 1 = 1 '

IF NOT @HistoryID IS NULL SET @Sql+= N' AND HistoryID = @HistoryID '
IF NOT @HistoryID IS NULL SET @Sql+= N' AND ShDetailID = @ShDetailID '
IF NOT @CustomerID IS NULL SET @Sql+= N' AND CustomerID = @CustomerID '
IF NOT @DateOfSaleFrom IS NULL SET @Sql+= N' AND DateOfSale >= @DateOfSaleFrom '
IF NOT @DateOfSaleTo IS NULL SET @Sql+= N' AND DateOfSale <= @DateOfSaleTo '

Set @Paramlist = '@HistoryID INT = NULL,
                  @ShDetailID INT = NULL,   
                  @DateOfSaleFrom DateTime = NULL,
                  @DateOfSaleTo DateTime = NULL,
                  @CustomerID INT = NULL'

Exec sp_executesql @Sql ,@Paramlist,
        @HistoryID,
        @ShDetailID,
        @DateOfSaleFrom,
        @DateOfSaleTo,
        @CustomerID
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

我创建了与实体框架中的过程相同的结构，但在创建带有WHERE条件的筛选器时遇到了问题。例如，它希望像要使用的过程和C#方法这样的条件能够自动生成过滤器。

public object GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
                                             DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
                                             int? CustomerID = null)
{
    EfM.Model1 db = new Model1();

    var Query = (from row1 in db.tblSalesHistories
                 join row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.ShDetailID
                 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
                         }
                         ).ToList();
    return Query;
}

我面临的下一件事是，在过程中的动态查询方法中，当过程复杂而繁重时，修复过程的错误并开发或编辑它非常困难和耗时。有没有其他方法可以在不使用动态查询的情况下过滤列表？

sql-server

来源：https://stackoverflow.com/questions/73751861/best-way-to-create-dynamic-filter-in-entity-framework-and-sql-server-stored-proc

3条答案

按热度按时间

41ik7eoe1#

您可以通过检查筛选器是否存在来完成此操作，如果存在，则应用它。

CREATE PROCEDURE dbo.GetSalesWithDetails
(
@HistoryID INT = NULL,
@ShDetailID INT = NULL,
@DateOfSaleFrom DateTime = NULL,
@DateOfSaleTo DateTime = NULL,
@CustomerID INT = NULL
)
AS

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 CROSS JOIN
            dbo.tblGoods AS gd 
where 
    (@HistoryID IS NULL OR HistoryID = @HistoryID)AND
    (@HistoryID IS NULL OR ShDetailID = @ShDetailID)AND
    (@CustomerID IS NULL OR CustomerID = @CustomerID)AND
    (@DateOfSaleFrom IS NULL OR DateOfSale >= @DateOfSaleFrom)AND
    (@DateOfSaleTo IS NULL OR DateOfSale <= @DateOfSaleTo)

赞(0）回复(0）举报 2022-09-18

fkvaft9z2#

按如下方式更改您的程序：

CREATE PROCEDURE dbo.GetSalesWithDetails
(
@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 CROSS JOIN
                        dbo.tblGoods AS gd 
where 1 = 1
AND (@HistoryID is null OR HistoryID = @HistoryID)
AND (@HistoryID IS NULL OR ShDetailID = @ShDetailID)
AND (@CustomerID IS NULL OR CustomerID = @CustomerID)
AND (@DateOfSaleFrom IS NULL OR DateOfSale >= @DateOfSaleFrom)
AND (@DateOfSaleTo IS NULL OR DateOfSale <= @DateOfSaleTo )

END

并将您的EF方法更改如下：

public object GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
                                             DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
                                             int? CustomerID = null)
        {
            EfM.Model1 db = new Model1();
            var Query = (from row1 in db.tblSalesHistories
                         join
                         row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.ShDetailID
                         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
                         where 
                         (
                         (HistoryID == null || row1.HistiryID == HistoryID)
                         &&
                         (ShDetailID == null || row2.ShDetailID == ShDetailID)
                         &&
                         (DateOfSaleFrom == null || row1.DateOfSale >= DateOfSaleFrom)
                         &&
                         (DateOfSaleTo == null || row1.DateOfSale <= DateOfSaleTo)
                         &&
                         (CustomerID == null || row1.CustomerID == CustomerID)
                         )

                         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
                         }
                         ).ToList();
            return Query;
        }

最好是按如下方式创建类，而不是对象类型的返回值，并按如下方式更改您的方法：

public class ClassSaleResults
{
    public int HistiryID { set; get; }
    public int ShDetailID { set; get; }
    public DateTime DateOfSale { set; get; }
    public int SaleCityID { set; get; }
    public int SalesPersonID { set; get; }
    public int CustomerID { set; get; }
    public string StfName { set; get; }
    public string StfSname { set; get; }
    public string GoodsName { set; get; }
    public string CsName { set; get; }
    public string CsSname { set; get; }
    public string CityName { set; get; }
    public decimal GoodsQuantity { set; get; }
    public decimal UnitPrice { set; get; }
    public decimal Discount { set; get; }
    public decimal Tax { set; get; }
    public decimal OtherCosts { set; get; }
    public decimal TotalPriceBefore { set; get; }
    public decimal TotalPriceAfterCosts { set; get; }
}

 public List<ClassSaleResults> GetSalesDetailsHistory(int? HistoryID = null, int? ShDetailID = null,
                                             DateTime? DateOfSaleFrom = null, DateTime? DateOfSaleTo = null,
                                             int? CustomerID = null)
        {
            EfM.Model1 db = new Model1();
            List<ClassSaleResults> Query = (from row1 in db.tblSalesHistories
                         join
                         row2 in db.tblSalesHistoryDetails on row1.HistiryID equals row2.ShDetailID
                         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
                         where 
                         (
                         (HistoryID == null || row1.HistiryID == HistoryID)
                         &&
                         (ShDetailID == null || row2.ShDetailID == ShDetailID)
                         &&
                         (DateOfSaleFrom == null || row1.DateOfSale >= DateOfSaleFrom)
                         &&
                         (DateOfSaleTo == null || row1.DateOfSale <= DateOfSaleTo)
                         &&
                         (CustomerID == null || row1.CustomerID == CustomerID)
                         )

                         select new ClassSaleResults()
                         {
                             HistiryID = row1.HistiryID,
                             ShDetailID = row2.ShDetailID,
                             DateOfSale = row1.DateOfSale,
                             SaleCityID = row1.SaleCityID,
                             SalesPersonID = row1.SalesPersonID,
                             CustomerID = row1.CustomerID,
                             StfName = row5.StfName,
                             StfSname = row5.StfSname,
                             GoodsName = row6.GoodsName,
                             CsName = row4.CsName,
                             CsSname = row4.CsSname,
                             CityName = row3.CityName,
                             GoodsQuantity = row2.GoodsQuantity,
                             UnitPrice = row2.UnitPrice,
                             Discount = row2.Discount,
                             Tax = row2.Tax,
                             OtherCosts = row2.OtherCosts,
                             TotalPriceBefore = row2.TotalPriceBefore,
                             TotalPriceAfterCosts = row2.TotalPriceAfterCosts
                         }
                         ).ToList<ClassSaleResults>();
            return Query;
        }

使用此方法，您可以访问列表和类的结构和更多详细信息。

赞(0）回复(0）举报 2022-09-18

nlejzf6q3#

根据@Larnu的建议，我在@AliNajafZadeh Sent Query的末尾添加了OPTION (RECOMPILE)，如下所示：

CREATE PROCEDURE dbo.GetSalesWithDetails
(
@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 CROSS JOIN
                        dbo.tblGoods AS gd 
where 1 = 1
AND (@HistoryID is null OR HistoryID = @HistoryID)
AND (@HistoryID IS NULL OR ShDetailID = @ShDetailID)
AND (@CustomerID IS NULL OR CustomerID = @CustomerID)
AND (@DateOfSaleFrom IS NULL OR DateOfSale >= @DateOfSaleFrom)
AND (@DateOfSaleTo IS NULL OR DateOfSale <= @DateOfSaleTo )
OPTION (RECOMPILE)
END

赞(0）回复(0）举报 2022-09-18

我来回答

在实体框架和SQL Server存储过程中创建动态过滤器的最佳方法

3条答案

相关问题

热门标签

最新问答