CODEWITHSUNDEEP
c#sql-serverentity-frameworkdbcontext
Matt Mills
Matt Mills

Reputation: 8822

Can I instruct EF DbContext to use "real" parameter names?

I'm using an EF (6, if it matters) DbContext subclass, and trying to implement an interceptor to set or update auditing data when an entity is written to the database.

My interceptor class:

public class EntitySaveInterceptor : BaseDbCommandInterceptor
{
    private const string CreatedAt = "@CreatedAt";
    private const string ModifiedAt = "@ModifiedAt";

    //called for update, but not insert
    public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        var parameters = command.Parameters
            .OfType<DbParameter>()
            .ToDictionary(p => p.ParameterName, p => p);
        if (parameters.ContainsKey(ModifiedAt))
        {
            parameters[ModifiedAt].Value = SystemTime.UtcNow();
        }
    }

    //called for insert
    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        var parameters = command.Parameters
            .OfType<DbParameter>()
            .ToDictionary(p => p.ParameterName, p => p);
        if (parameters.ContainsKey(CreatedAt))
        {
            parameters[CreatedAt].Value = SystemTime.UtcNow();
        }
        if (parameters.ContainsKey(ModifiedAt))
        {
            parameters[ModifiedAt].Value = SystemTime.UtcNow();
        }
    }
}

The problem I'm encountering is that these commands don't contain "@CreatedAt" or "@ModifiedAt" parameters. Instead, the value for the CreatedAt column is passed in a parameter named @3 (indicating the zero-indexed position in the values list), and the value for the ModifiedAt column (a DateTime? value) is hard-coded as NULL in the generated SQL when its value is null.

I found this answer, but a cursory examination of the SqlCommand properties reveals that BindByName is an ODP.Net specific concept.

It's obvious that I can inspect the command text at this point, match the column names in the INSERT statement with the parameter names and NULLs in the VALUES clause, and update the command.Parameters collection to match the new SQL. But, if possible, I'd prefer to have my DbContext use parameter names that match up with the column names.

So - can I instruct my DbContext, or something upon which it relies, to use parameter names based on property or column names when it is creating DbCommand objects?

Upvotes: 3

Views: 343

Answers (1)

Moho
Moho

Reputation: 16553

An alternative would be to override DbContext.SaveChanges and set property values there. The below is an example of a type-agnostic (though not necessarily the most efficient) application of the current date/time to any added entity with a CreatedAt property:

    public override int SaveChanges()
    {
        foreach( var entry in this.ChangeTracker.Entries() )
        {
            if( entry.State == EntityState.Added && 
                entry.CurrentValues.PropertyNames.Contains( "CreatedAt" ) )
            {
                entry.CurrentValues[ "CreatedAt" ] = DateTime.Now;
            }
        }

        return base.SaveChanges();
    }

Upvotes: 2

Related Questions