dotnet / dotnet/efcore

Add context-based calculated parameters to CUD stored procedure mappings

Open
#33,373 0 comments 0 reactions 0 assignees View on GitHub
area-model-building area-save-changes area-sprocs customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

I have a class like this:

```cs
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:

```tsql
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` 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.

```cs
public class MyDbContext : DbContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity(entity => {
entity.InsertUsingStoredProcedure(sproc => {

// Example using an IServiceProvider factory function.
sproc.HasGeneratedParameter("OperationUserId", context => context.ServiceProvider
.GetRequiredService()
.HttpContext.User.Claims.FirstOrDefault(c => c.Type == "UserId")?.Value);

// Example using an initialized & then saved Dictionary
// that is refreshed with each call to `SaveChanges()`.
sproc.UsesScopedValues(context => {
context.ScopedValues["OperationUserId"] =
context.ServiceProvider.GetRequiredService()
.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);
});
});
}
}
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.