Convert C# lambda query to SQL Server stored procedure

enyaitl3 于 2023-10-15 发布在 C#

关注(0)|答案(2)|浏览(119)

I was hoping to get some help in converting the following query to a SQL Server stored procedure.

string[] Includes = new string[] { "IntakeData" };

    var r = _repo.Filter<ResultData>
                  (a => (a.TrackingIdentifier == trackingNum || 
                         a.IntakeData.Requestor_CaseID == trackingNum) 
                        && a.ResponseType == "RESPONSE"
                        && a.Status == "OK" 
                        && a.Destination == rs, Includes)
                      .OrderByDescending(d => d.CreatedDate)
                      .Select(c => new { c.ResultStatus, c.Destination, c.IntakeData })
                 .FirstOrDefault();

Below is the query I extracted from SQL profiler, but the SQL query generated is far too complex and many unnecessary null checks are generated as a result. So, we made the decision to simplify it by using a similar stored procedure.

exec sp_executesql N'SELECT TOP (1) 
    [Project1].[C1] AS [C1], 
    [Project1].[ResultStatus] AS [ResultStatus], 
    [Project1].[Destination] AS [Destination], 
    [Project1].[ID] AS [ID], 
    [Project1].[TrackingIdentifier] AS [TrackingIdentifier], 
    [Project1].[ClientTrackingIdentifier] AS [ClientTrackingIdentifier], 
    [Project1].[RequestorEmail] AS [RequestorEmail], 
    [Project1].[CandidateEmail] AS [CandidateEmail], 
    [Project1].[ConsumerIdentifier] AS [ConsumerIdentifier], 
    [Project1].[LocationIdentifier] AS [LocationIdentifier], 
    [Project1].[CandidateID] AS [CandidateID], 
    [Project1].[TotalCount] AS [TotalCount], 
    [Project1].[KitIdentifier] AS [KitIdentifier], 
    [Project1].[QueryStatus] AS [QueryStatus], 
    [Project1].[QueryData] AS [QueryData], 
    [Project1].[CreatedDate1] AS [CreatedDate], 
    [Project1].[CorporateEntityID] AS [CorporateEntityID], 
    [Project1].[Requestor_CaseID] AS [Requestor_CaseID], 
    [Project1].[CandidateSMS] AS [CandidateSMS], 
    [Project1].[NotificationType] AS [NotificationType], 
    [Project1].[REDTAILOrderCreationStatus] AS [REDTAILOrderCreationStatus], 
    [Project1].[REDTAILOrderCreationErrorMessage] AS [REDTAILOrderCreationErrorMessage], 
    [Project1].[APIInviteData] AS [APIInviteData], 
    [Project1].[CRN] AS [CRN]
    FROM ( SELECT 
        [Filter1].[ResultStatus] AS [ResultStatus], 
        [Filter1].[Destination] AS [Destination], 
        [Filter1].[CreatedDate1] AS [CreatedDate], 
        [Extent3].[ID] AS [ID], 
        [Extent3].[TrackingIdentifier] AS [TrackingIdentifier], 
        [Extent3].[ClientTrackingIdentifier] AS [ClientTrackingIdentifier], 
        [Extent3].[RequestorEmail] AS [RequestorEmail], 
        [Extent3].[CandidateEmail] AS [CandidateEmail], 
        [Extent3].[ConsumerIdentifier] AS [ConsumerIdentifier], 
        [Extent3].[LocationIdentifier] AS [LocationIdentifier], 
        [Extent3].[CandidateID] AS [CandidateID], 
        [Extent3].[TotalCount] AS [TotalCount], 
        [Extent3].[KitIdentifier] AS [KitIdentifier], 
        [Extent3].[QueryStatus] AS [QueryStatus], 
        [Extent3].[QueryData] AS [QueryData], 
        [Extent3].[CreatedDate] AS [CreatedDate1], 
        [Extent3].[CorporateEntityID] AS [CorporateEntityID], 
        [Extent3].[Requestor_CaseID] AS [Requestor_CaseID], 
        [Extent3].[CandidateSMS] AS [CandidateSMS], 
        [Extent3].[NotificationType] AS [NotificationType], 
        [Extent3].[REDTAILOrderCreationStatus] AS [REDTAILOrderCreationStatus], 
        [Extent3].[REDTAILOrderCreationErrorMessage] AS [REDTAILOrderCreationErrorMessage], 
        [Extent3].[APIInviteData] AS [APIInviteData], 
        [Extent3].[CRN] AS [CRN], 
        1 AS [C1]
        FROM   (SELECT [Extent1].[IntakeDataID] AS [IntakeDataID], [Extent1].[TrackingIdentifier] AS [TrackingIdentifier1], [Extent1].[ResultStatus] AS [ResultStatus], [Extent1].[Destination] AS [Destination], [Extent1].[CreatedDate] AS [CreatedDate1], [Extent2].[Requestor_CaseID] AS [Requestor_CaseID]
            FROM  [dbo].[ResultData] AS [Extent1]
            LEFT OUTER JOIN [dbo].[IntakeData] AS [Extent2] ON [Extent1].[IntakeDataID] = [Extent2].[ID]
            WHERE (''RESPONSE'' = [Extent1].[ResponseType]) AND ([Extent1].[ResponseType] IS NOT NULL) AND (''OK'' = [Extent1].[Status]) AND ([Extent1].[Status] IS NOT NULL) ) AS [Filter1]
        LEFT OUTER JOIN [dbo].[IntakeData] AS [Extent3] ON [Filter1].[IntakeDataID] = [Extent3].[ID]
        WHERE ((([Filter1].[TrackingIdentifier1] = @p__linq__0) AND ( NOT ([Filter1].[TrackingIdentifier1] IS NULL OR @p__linq__0 IS NULL))) OR (([Filter1].[TrackingIdentifier1] IS NULL) AND (@p__linq__0 IS NULL)) OR (([Filter1].[Requestor_CaseID] = @p__linq__1) AND ( NOT ([Filter1].[Requestor_CaseID] IS NULL OR @p__linq__1 IS NULL))) OR (([Filter1].[Requestor_CaseID] IS NULL) AND (@p__linq__1 IS NULL))) AND ((([Filter1].[Destination] = @p__linq__2) AND ( NOT ([Filter1].[Destination] IS NULL OR @p__linq__2 IS NULL))) OR (([Filter1].[Destination] IS NULL) AND (@p__linq__2 IS NULL)))
    )  AS [Project1]
    ORDER BY [Project1].[CreatedDate] DESC',N'@p__linq__0 varchar(8000),@p__linq__1 varchar(8000),@p__linq__2 varchar(8000)',@p__linq__0='06ONMJ99SN',@p__linq__1='06ONMJ99SN',@p__linq__2='ACCIO'

Any help here to simplify the generated SQL query to a smaller stored procedure would be greatly appreciated.

Thanks

sql-server

来源：https://stackoverflow.com/questions/77136754/convert-c-sharp-lambda-query-to-sql-server-stored-procedure

2条答案

按热度按时间

tquggr8v1#

Try following procedure. The stored procedure simplifies the query and encapsulates it, making it more maintainable and reusable.:

CREATE PROCEDURE GetFilteredResultData
    @TrackingIdentifier NVARCHAR(8000),
    @Requestor_CaseID NVARCHAR(8000),
    @Destination NVARCHAR(8000)
AS
BEGIN
    SELECT TOP 1
        rd.ResultStatus,
        rd.Destination,
        rd.ID,
        rd.TrackingIdentifier,
        rd.ClientTrackingIdentifier,
        rd.RequestorEmail,
        rd.CandidateEmail,
        rd.ConsumerIdentifier,
        rd.LocationIdentifier,
        rd.CandidateID,
        rd.TotalCount,
        rd.KitIdentifier,
        rd.QueryStatus,
        rd.QueryData,
        rd.CreatedDate AS CreatedDate1,
        rd.CorporateEntityID,
        rd.Requestor_CaseID,
        rd.CandidateSMS,
        rd.NotificationType,
        rd.REDTAILOrderCreationStatus,
        rd.REDTAILOrderCreationErrorMessage,
        rd.APIInviteData,
        rd.CRN
    FROM ResultData rd
    LEFT JOIN IntakeData id ON rd.IntakeDataID = id.ID
    WHERE (rd.TrackingIdentifier = @TrackingIdentifier OR id.Requestor_CaseID = @Requestor_CaseID)
        AND rd.ResponseType = 'RESPONSE'
        AND rd.Status = 'OK'
        AND rd.Destination = @Destination
    ORDER BY rd.CreatedDate DESC;
END

When you want to retrieve the data using this query, you can call the GetFilteredResultData stored procedure and pass the appropriate parameters.

赞(0）回复(0）举报 2023-10-15

7rtdyuoh2#

This is just a super fast reformat and removing aliases and a lot of unneeded parenthesis and square brackets and null checking and obviously NOT tested.

Still to do:

Setup the parameters to call this
Change the horrid parameter names
Test both this cte version and a sub-select version as performance may vary

Kind of ugly code:

CREATE PROCEDURE GetFilteredResults (
       @p__linq__0 varchar(8000) = '06ONMJ99SN',
       @p__linq__1 varchar(8000) = '06ONMJ99SN',
       @p__linq__2 varchar(8000) = 'ACCIO'
   ) AS
   BEGIN
       WITH cteFT AS (
           SELECT 
               RD.IntakeDataID,
               RD.TrackingIdentifier AS TrackingIdentifier1,
               RD.ResultStatus,
               RD.Destination,
               RD.CreatedDate AS CreatedDate1,
               ITD.Requestor_CaseID 
           FROM dbo.ResultData AS RD
           LEFT OUTER JOIN dbo.IntakeData AS ITD ON RD.IntakeDataID = ITD.ID
           WHERE 
               RD.ResponseType = 'RESPONSE'
               AND RD.[Status] = 'OK'
       ), cteP AS (
           SELECT 
               FT.ResultStatus ,
               FT.Destination ,
               FT.CreatedDate1 AS CreatedDate,
               ITD3.ID ,
               ITD3.TrackingIdentifier ,
               ITD3.ClientTrackingIdentifier ,
               ITD3.RequestorEmail A,
               ITD3.CandidateEmail ,
               ITD3.ConsumerIdentifier ,
               ITD3.LocationIdentifier,
               ITD3.CandidateID ,
               ITD3.TotalCount ,
               ITD3.KitIdentifier ,
               ITD3.QueryStatus ,
               ITD3.QueryData ,
               ITD3.CreatedDate AS CreatedDate1,
               ITD3.CorporateEntityID ,
               ITD3.Requestor_CaseID ,
               ITD3.CandidateSMS ,
               ITD3.NotificationType ,
               ITD3.REDTAILOrderCreationStatus,
               ITD3.REDTAILOrderCreationErrorMessage ,
               ITD3.APIInviteData ,
               ITD3.CRN,
               1 AS C1
           FROM cteFT AS FT
           LEFT OUTER JOIN dbo.IntakeData AS ITD3 ON FT.IntakeDataID = ITD3.ID
           WHERE 
               (( FT.TrackingIdentifier1 = @p__linq__0 AND FT.TrackingIdentifier1 IS NOT NULL )
                   OR ( FT.TrackingIdentifier1 IS NULL AND @p__linq__0 IS NULL )
                   OR ( FT.Requestor_CaseID = @p__linq__1 AND FT.Requestor_CaseID IS NOT NULL )
                   OR ( FT.Requestor_CaseID IS NULL AND @p__linq__1 IS NULL ) )
           AND (( FT.Destination IS NOT NULL AND FT.Destination = @p__linq__2 )
                   OR ( FT.Destination IS NULL AND @p__linq__2 IS NULL ) )
       )
       SELECT TOP (1)
           C1,
           ResultStatus,
           Destination,
           ID,
           TrackingIdentifier,
           ClientTrackingIdentifier,
           RequestorEmail,
           CandidateEmail,
           ConsumerIdentifier,
           LocationIdentifier,
           CandidateID,
           TotalCount,
           KitIdentifier,
           QueryStatus,
           QueryData,
           CreatedDate,
           CorporateEntityID,
           Requestor_CaseID,
           CandidateSMS,
           NotificationType,
           REDTAILOrderCreationStatus,
           REDTAILOrderCreationErrorMessage,
           APIInviteData,
           CRN
       FROM cteP
       ORDER BY CreatedDate DESC;
   END

赞(0）回复(0）举报 2023-10-15

我来回答

Convert C# lambda query to SQL Server stored procedure

2条答案

相关问题

热门标签

最新问答