时间戳

首页 > 解决方案 > 如何将参数传递到此存储过程

c# - 如何将参数传递到此存储过程

问题描述

我之前的一个问题是我尝试通过 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;
}

标签： c#asp.netentity-frameworkstored-proceduresentity-framework-core

解决方案

有几个问题需要解压

  1. 将参数添加到 SP 就像根据本指南声明它们一样简单：SQL 参数
  2. 此处描述了从 C# 向 SP 传递参数EF Raw SQL Queries
  3. 您的 SP 可以改进并替换为可组合查询，这意味着您不再需要传递参数

如何在您的 SP 中定义参数：

让我们现在只做前2个......

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 将 C# 参数传递给 SP

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

推荐阅读