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?
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 LINQContains
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:Where
@ids
would be a comma-separated string of branch ids.This query checks if there is a
UserAssignedBranch
record, having the specifieduserId
andagentId
, for each of the supplied branch ids.