如何将参数传递到这个存储过程

Question

在上一个问题中，我尝试通过EntityFramework调用存储过程，然后对结果进行过滤。

我如何将我的过滤器逻辑移动到dbo.spStaysSearch中，以便我可以修改存储过程以接受参数?

CREATE PROCEDURE [dbo].[spStaysSearch] 
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
ORDER BY 
tblStays.StartDate, tblOccupant.FileAs;
END

当前从c#中调用

private IQueryable<spStaysSearch> getSearchData(StaySearchViewModel model)
{
var records = db.SpStaySearches.FromSqlRaw("dbo.spStaysSearch").ToList().AsQueryable();
if (model.OccupantId.HasValue)
records = records.Where(x => x.OccupantId == model.OccupantId);
if (!string.IsNullOrWhiteSpace(model.OccupantFileAs))
records = records.Where(x => x.OccupantFileAs == model.OccupantFileAs);
if (!string.IsNullOrWhiteSpace(model.BuildingName))
records = records.Where(x => x.BuildingName == model.BuildingName);
if (!string.IsNullOrWhiteSpace(model.CaseManager))
records = records.Where(x => x.CaseManager == model.CaseManager);
if (!string.IsNullOrWhiteSpace(model.BuildingName))
records = records.Where(x => x.BuildingName == model.BuildingName);
if (model.IntakeDateStart.HasValue && model.IntakeDateEnd.HasValue)
{
records = records.Where(x => x.StartDate >= model.IntakeDateStart && x.StartDate <= model.IntakeDateEnd);
}
else
{
if (model.IntakeDateStart.HasValue)
records = records.Where(x => x.StartDate >= model.IntakeDateStart);
if (model.IntakeDateEnd.HasValue)
records = records.Where(x => x.StartDate <= model.IntakeDateEnd);
}
if (model.ExitDateStart.HasValue && model.ExitDateEnd.HasValue)
{
records = records.Where(x => x.EndDate >= model.ExitDateStart && x.EndDate <= model.ExitDateEnd);
}
else
{
if (model.ExitDateStart.HasValue)
records = records.Where(x => x.EndDate >= model.ExitDateStart);
if (model.ExitDateEnd.HasValue)
records = records.Where(x => x.EndDate <= model.ExitDateEnd);
}
if (model.IsActive.HasValue)
records = records.Where(x => x.IsActive == model.IsActive);
return records;
}

Answer 1

有几个问题需要解包

1. 向SP添加参数就像按照本指南声明它们一样简单:
2. 从c#向SP传递参数的描述在这里EF Raw SQL Queries
3. 您的SP可以改进并替换为可组合的查询，这意味着您不再需要通过所有传递参数

如何定义SP中的参数:

我们现在只做前两个…

CREATE PROCEDURE [dbo].[spStaysSearch] 
@OccupantId INT,
@OccupantFileAs VARCHAR(10)
AS
BEGIN
...

但是你需要在你的查询中使用这些参数，一种方法是在你的select语句中添加这样的过滤子句:

WHERE (@OccupantId IS NULL OR tblOccupant.OccupantID = @OccupantId)
AND (@OccupantFileAs IS NULL OR tblOccupant.FileAs = @OccupantFileAs)

如何通过EF向SP传递c#参数

var records = db.SpStaySearches.FromSqlRaw("EXECUTE dbo.spStaysSearch @OccupantId, @OccupantFileAs")
,new SqlParameter("OccupantId", model.OccupantId)
,new SqlParameter("OccupantFileAs", model.OccupantFileAs)
.ToList()
.AsQueryable();

完全替换SP

在这种情况下，使用存储过程没有任何好处，SP不执行任何逻辑操作，它仅仅是一个非常简单的查询的包装器。有两种方法可以替换这个存储过程调用:

1. 用实际的原始SQL替换SP:

   注意:要实现可组合，我们不能使用cte，它必须是一个有效的SQL表达式，可以作为嵌套或内联查询

   调用。

   var sql = @"
   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 (
   SELECT lkpOccStays.StayID
   , COUNT(tblOccupantStays.OccupantStayID) AS CountOfOccupantStayID
   FROM tblOccupantStays lkpOccStays 
   INNER JOIN tblOccupant lkpChild ON lkpOccStays.OccupantID = lkpChild.OccupantID
   WHERE lkpChild.OccupantType LIKE 'Child'
   GROUP BY lkpOccStays.StayID
   ) OccupantStays_CountOfChildren ON tblStays.StayID = OccupantStays_CountOfChildren.StayID
   LEFT JOIN tblCaseManager ON tblStays.CaseManagerID = tblCaseManager.CaseManagerID
   LEFT JOIN (SELECT tblStayBillingHx.StayID
   , MAX(tblStayBillingHx.BillSentDate) AS MaxOfBillSentDate
   FROM tblStayBillingHx 
   GROUP BY tblStayBillingHx.StayID
   ) StaysMaxBillSentDate ON tblStays.StayID = StaysMaxBillSentDate.StayID       
   ";
   var records = db.SpStaySearches.FromSqlRaw(sql);
   

2. 完全可以在Linq中完成:

   很多这里做了一些假设，你的EF模式没有提供，所以一些约定被假设，这个特定的查询可能不匹配你的模式，但它应该足够接近，以理解概念。

   var records = db.Stays.SelectMany(s => s.Occupants.Select(o => new spStaysSearch {
   StayId = s.StayId,
   ... (other stays properties)
   OccupantId = o.OccupantId,
   OccupantFileAs = o.FileAs,
   StayOCFSNumber = s.BuildingName == "Main Shelter" ? o.OCFSMainNumber : o.OCFSNorthNumber,
   CountOfChildren = s.Occupants.Count(child => child.OccupantType == "Child"),
   CaseManager = s.CaseManager.FileAs,
   MaxOfBillSentDate = s.BillingHistory.Max(h => h.BillSentDate)
   }));