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

Question

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

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;
}

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

Answer 1

您可以通过检查过滤器是否存在来做到这一点，如果存在，则应用它。

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)

Answer 2

按如下方式更改程序：

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;
}

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

Answer 3

根据@Larnu的建议，我在@AliNajafZadeh发送的查询末尾添加了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