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

Question

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?

Do you really care about the *parameters names* or do you want *set or update auditing data when an entity is written to the database.* The second can be done without the first. — Erik Philips, Nov 10 '14 at 23:52
@ErikPhilips I'd much prefer to get the parameter names, because then I can do whatever. The auditing data is simply the first use case. — Matt Mills, Nov 11 '14 at 15:25

score 2 · Answer 1 · answered Nov 11 '14 at 15:35

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();
    }

Thanks for the alternative. +1 – Matt Mills Nov 11 '14 at 15:40 — Matt Mills, Nov 11 '14 at 15:40

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

1 Answers1