Add context-based calculated parameters to CUD stored procedure mappings

[willnationsdev]

I have a class like this:

public class SiteRequest
{
    public int Id { get; set; }
    public Uri Uri { get; set; }
    public string Title { get; set; }
    public DateTime CreatedOn { get; set; }
}

I then have a stored procedure that we use to insert a new SiteRequest that looks like this:

CREATE PROCEDURE [dbo].[uspInsertSiteRequest]
    @OperationUserId UNIQUEIDENTIFIER, -- not part of entity
    @Uri VARCHAR(256)
    @Title NVARCHAR(800)
AS
BEGIN
    -- etc.
END

The @OperationUserId is passed in so that the stored procedure can internally perform authorization checks and generate audit logs. I do not have the ability to edit the stored procedure.

Currently, I directly call the stored procedure and pass in arguments manually. Afaik, if I were to try and adapt this to EF Core before .NET 7, I would have to override SaveChanges or use a SavingChanges interceptor to manually detect changes, run the procedure, and update local entities / their state.

With .NET 7 adding mapped stored procedures, I thought I'd try doing it. However, the StoredProcedureBuilder<T> type does not provide any means of registering parameters that are sourced externally, i.e. that are not fields/properties of the entity itself.

From the docs, it seems as though I would have to register a shadow property for this "OperationUserId" value and then set the shadow property within the change tracker during a SavingChanges event for each SiteRequest instance that is being inserted, that way I can masquerade as if the values are coming from the entity itself.

Ideally, I would be able to just define a parameter value that draws from either the DbContext itself or even better the IServiceProvider used by the DbContext (though I'm sure there'd be a more performant way to cache & reuse the generated value(s) across many procedure executions derived from a single SaveChanges call). I'm not positive of the best solution, but these are just some ideas.

public class MyDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SiteRequest>(entity => {
            entity.InsertUsingStoredProcedure(sproc => {
        
                // Example using an IServiceProvider factory function.
                sproc.HasGeneratedParameter("OperationUserId", context => context.ServiceProvider
                    .GetRequiredService<IHttpContextAccessor>()
                        .HttpContext.User.Claims.FirstOrDefault(c => c.Type == "UserId")?.Value);
        
                // Example using an initialized & then saved Dictionary<string, object>
                // that is refreshed with each call to `SaveChanges()`.
                sproc.UsesScopedValues(context => {
                    context.ScopedValues["OperationUserId"] =
                        context.ServiceProvider.GetRequiredService<IHttpContextAccessor>()
                        .HttpContext.User.Claims.FirstOrDefault(c => c.Type == "UserId")?.Value;
                });
                sproc.HasGeneratedParameter("OperationUserId", context => context.ScopedValues["OperationUserId"]);
        
                // Register remaining positional arguments.        
                sproc.HasParameter(sr => sr.Uri);
                sproc.HasParameter(sr => sr.Title);
                
                // Register db-generated values acquired from result columns.
                sproc.HasResultColumn(sr => sr.Id);
                sproc.HasResultColumn(sr => sr.CreatedOn);
            });
        });
    }
}