Build stored procedure from EF Core developed SQL

Question

I have a EF Core 8 query written in LINQ which is like below and produces the expected result. Below, the branchIds is list of string.

bool hasAccess = await _dbContext.Set<UserAssignedBranch>()
                   .Where(w => w.AgentId == agentId &&
                               branchIds.All(a => a == w.BranchId) &&
                               w.AppUserId == userId)
                   .AnyAsync();

Once this is executed I get the expected output.

I want to translate this to a stored procedure. So I opened the SQL Server Profiler and got the translated SQL syntax from EF Core which looks like this:

exec sp_executesql N'SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [UserAssignedBranch] AS [u]
        WHERE [u].[AgentId] = @__agentId_0 AND NOT EXISTS (
            SELECT 1
            FROM OPENJSON(@__branchIds_1) WITH ([value] uniqueidentifier ''$'') AS [b]
            WHERE [b].[value] <> [u].[BranchId]) AND [u].[AppUserId] = @__userId_2) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END',N'@__agentId_0 uniqueidentifier,@__branchIds_1 nvarchar(4000),@__userId_2 uniqueidentifier',@__agentId_0='FB4426D2-F237-4F7B-FA9C-08DC2CABC414',@__branchIds_1=N'["684a6a00-0a73-4c23-f525-08dc2cabc427","5a29f7fe-3fc0-42cb-a946-e3b73e09ca13"]',@__userId_2='DBEF4707-F497-43DF-9D27-08DC2CABC51B'

I attempted to write the stored procedure from this, which resulted in this code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_CheckBranchAccess] 
    @UserId    NVARCHAR(100),
    @AgentId   NVARCHAR(100),
    @BranchIds NVARCHAR(MAX) = N'[]'
AS
BEGIN
    DECLARE @Result BIT;

    SELECT @Result = CASE
        WHEN EXISTS (
            SELECT 1
            FROM [UserAssignedBranch] AS [u]
            WHERE [u].[AgentId] = @AgentId AND NOT EXISTS (
                SELECT 1
                FROM OPENJSON(@BranchIds) WITH ([value] UNIQUEIDENTIFIER '$') AS [b]
                WHERE [b].[value] = [u].[BranchId]
            ) AND [u].[AppUserId] = @UserId
        ) THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
    END;

    SELECT @Result AS [Result];
END

This is how I call the stored procedure from C#:

var strbranchIds = branchIds.Select(s => s.ToString()).ToList();

List<SqlParameter> parms = new()
    {
        new SqlParameter { ParameterName = "@AgentId", Value = agentId.ToString() },
        new SqlParameter { ParameterName = "@UserId", Value = userId.ToString() },
        new SqlParameter { ParameterName = "@BranchIds", Value = string.Join(',',strbranchIds ?? new List<string>()) }
    };

var hasClaim = await _dbContext.Set<SP_CheckBranchAccess>()
                               .FromSqlRaw("EXEC SP_CheckBranchAccess @AgentId, @UserId, @BranchIds", parms.ToArray())
                               .ToListAsync(cancellationToken: token);

But in this case, I'm not getting the expected result as from LINQ-to-SQL. Can anybody help me to fix this?

Answer 1

In general, it's not a good idea to copy an EF-generated SQL query as-is into a stored procedure. Entity Framework really does a decent job in generating SQL —and it's getting better at it all the time— but you should at least check if it can be improved. Which is true here.

This trick with OPENJSON was added in EF-core 8.0 to solve problems with LINQ Contains queries (i.e. query plan pollution and lack of scalability), but if someone would create a stored procedure from scratch, aiming at getting records based on known id values, it would be very unlikely that they would come up with this solution.

My first choice would be to use STRING_SPLIT. Then it's less easy to supply an incorrectly formatted parameter:

CREATE PROCEDURE [dbo].[HasAccessToAllBranches]
    @userId    NVARCHAR(100),
    @agentId   NVARCHAR(100),
    @branchIds NVARCHAR(max)
AS
BEGIN
    WITH vals AS
    (
        SELECT value FROM string_split(@branchIds, N',')
    )
    SELECT CONVERT(BIT, CASE WHEN EXISTS
    (
        SELECT * FROM vals v
        WHERE NOT EXISTS 
        (
            SELECT *
            FROM [UserAssignedBranch] AS [u]
            WHERE [u].[BranchId] = v.value
            AND [u].[AppUserId] = @userId
            AND [u].[AgentId] = @agentId
        )
    ) THEN 0 ELSE 1 END);
END
GO

Where @ids would be a comma-separated string of branch ids.

This query checks if there is a UserAssignedBranch record, having the specified userId and agentId, for each of the supplied branch ids.

Answer 2

Don't use .Set<T>.FromSqlRaw as this intends to return and populate an entity definition from the SQL. In your case you really just want a scalar value for True/False. Instead, try _dbContext.Database.ExecuteSqlCommand with the desired parameters, including the OUTPUT parameter for the result. One question would be why do you want to execute this as a Stored Proc rather than just using the Linq expression?