BulkInsert ignoring session context value set before insert

[bptillman]

Description

In our code base we have a table that has a trigger on it that checks for a value set in the session context and skips the trigger logic, otherwise it proceeds with executing the trigger logic like so:

ALTER   TRIGGER [dbo].[table1_versioning] ON [dbo].[table1]
AFTER UPDATE, DELETE, INSERT
AS
BEGIN
IF CAST(SESSION_CONTEXT(N'DisableHistoryTriggers') AS BIT) = 1 RETURN

DECLARE @Now DATETIME2 = GETUTCDATE();

UPDATE x
SET
	x.[EffectiveFromDate] = @Now,

When we perform a Bulk Insert to this table in code, we first make a call to set that session context value so that the trigger should return and not proceed with its remaining logic, but when executing the code below, we are seeing that the triggers logic after the return statement ends up being executed since afterwards when looking in the database, we see the EffectiveFromDate being set to the UTC Now Date, instead of the date we set it to before the BulkInsert.

foreach (var entityToInsert in entitiesToInsert)
{
	entityToInsert.EffectiveFromDate = new DateTime(2020, 1, 1);
}

EntityFrameworkManager.ContextFactory = _ => context;

context.Database.ExecuteSqlRaw("EXEC sp_set_session_context 'DisableHistoryTriggers', @value;", new SqlParameter("@value", 1));

var test = context.Database.SqlQueryRaw<int?>($"SELECT SESSION_CONTEXT(N'DisableHistoryTriggers');").ToList().First();
test.ShouldBe(1); //this passes

context.BulkInsert(entitiesToInsert, option =>
{
	option.IncludeGraph = includeGraph;
});

context.Database.ExecuteSqlRaw("EXEC sp_set_session_context 'DisableHistoryTriggers', NULL;");
context.CloseTransaction();

test = context.Database.SqlQueryRaw<int?>($"SELECT SESSION_CONTEXT(N'DisableHistoryTriggers');");
test.ShouldBeNull(); //passes

foreach (var entityToInsert in entitiesToInsert)
{
	entityToInsert.EffectiveFromDate.ShouldBe(new DateTime(2020, 1, 1)); //this fails
}

This all leads me to believe that a different session context is used for the BulkInsert, despite specifying to use the same existing context with the ContextFactory.

I have also tried using the PreBulkInsert event to try and set the session context value there, but it did not appear to make a difference:

                EntityFrameworkManager.PreBulkInsert = (ctx, obj) =>
                {
                    ctx.Database.ExecuteSqlRaw("EXEC sp_set_session_context 'DisableHistoryTriggers', @value;",
                        new SqlParameter("@value", 1));
                };

Further technical details

• EF version: [EF Core v7.0.16]
• EF Extensions version: [EFE Core v7.102.2.4]
• Database Server version: [SQL Server 2019]
• Database Provider version (NuGet): [Microsoft.Data.SqlClient v5.1.5]