DateCreated or Modified Column - Entity Framework

Question

After I read one question in attached link, I got a sense of how to set DateCreated and DateModified columns in Entity Framework and use it in my application. In the old SQL way though, the trigger way is more popular because is more secure from DBA point of view.

So any advice on which way is the best practice? should it be set in entity framework for the purpose of application integrity? or should use trigger as it make more sense from data security point of view? Or is there a way to compose trigger in entity framework? Thanks.

EF CodeFirst: Rails-style created and modified columns

BTW, even though it doesn't matter much, I am building this app using ASP.NET MVC C#.

Answer 1

Entity Framework 6 has interceptors which can be used to set created and modified. I wrote an article how to do it: http://marisks.net/2016/02/27/entity-framework-soft-delete-and-automatic-created-modified-dates/

Answer 2

I agree with marc_s - much safer to have the trigger(s) in the database. In my company's databases, I require each field to have a Date_Modified, Date_Created field, and I even have a utility function to automatically create the necessary triggers.

When using with Entity Framework, I found I needed to use the [DatabaseGenerated] annotation with my POCO classes:

[Column(TypeName = "datetime2")]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime? Date_Modified { get; set; }

[Column(TypeName = "datetime2")]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime? Date_Created { get; set; }

I was attempting to use stored procedure mapping on an entity, and EF was creating @Date_Modified, @Date_Created parameters on my insert/update sprocs getting the error

Procedure or function has too many arguments specified.

Most of the examples show using [NotMapped], which will allow select/insert to work, but then those fields will not show up when that entity is loaded!

Alternately you can just make sure any sprocs contain the @Date_Modified, @Date_Created parameters, but this goes against the design of using triggers in the first place.

Answer 3

Opinion: Triggers are like hidden behaviour, unless you go looking for them you usually won't realise they are there. I also like to keep the DB as 'dumb' as possible when using EF, since I'm using EF so my team wont need to maintain SQL code.

For my solution (mix of ASP.NET WebForms and MVC in C# with Business Logic in another project that also contains the DataContext):

I recently had a similar issue, and although for my situation it was more complex (DatabaseFirst, so required a custom TT file), the solution is mostly the same.

I created an interface:

public interface ITrackableEntity
{
    DateTime CreatedDateTime { get; set; }
    int CreatedUserID { get; set; }
    DateTime ModifiedDateTime { get; set; }
    int ModifiedUserID { get; set; }
}

Then I just implemented that interface on any entities I needed to (because my solution was DatabaseFirst, I updated the TT file to check if the table had those four columns, and if so added the interface to the output).

UPDATE: here's my changes to the TT file, where I updated the EntityClassOpening() method:

public string EntityClassOpening(EntityType entity)
{
    var trackableEntityPropNames = new string[] { "CreatedUserID", "CreatedDateTime", "ModifiedUserID", "ModifiedDateTime" };
    var propNames = entity.Properties.Select(p => p.Name);
    var isTrackable = trackableEntityPropNames.All(s => propNames.Contains(s));
    var inherits = new List<string>();
    if (!String.IsNullOrEmpty(_typeMapper.GetTypeName(entity.BaseType)))
    {
        inherits.Add(_typeMapper.GetTypeName(entity.BaseType));
    }
    if (isTrackable)
    {
        inherits.Add("ITrackableEntity");
    }

    return string.Format(
        CultureInfo.InvariantCulture,
        "{0} {1}partial class {2}{3}",
        Accessibility.ForType(entity),
        _code.SpaceAfter(_code.AbstractOption(entity)),
        _code.Escape(entity),
        _code.StringBefore(" : ", String.Join(", ", inherits)));
}

The only thing left was to add the following to my partial DataContext class:

    public override int SaveChanges()
    {
        // fix trackable entities
        var trackables = ChangeTracker.Entries<ITrackableEntity>();

        if (trackables != null)
        {
            // added
            foreach (var item in trackables.Where(t => t.State == EntityState.Added))
            {
                item.Entity.CreatedDateTime = System.DateTime.Now;
                item.Entity.CreatedUserID = _userID;
                item.Entity.ModifiedDateTime = System.DateTime.Now;
                item.Entity.ModifiedUserID = _userID;
            }
            // modified
            foreach (var item in trackables.Where(t => t.State == EntityState.Modified))
            {
                item.Entity.ModifiedDateTime = System.DateTime.Now;
                item.Entity.ModifiedUserID = _userID;
            }
        }

        return base.SaveChanges();
    }

Note that I saved the current user ID in a private field on the DataContext class each time I created it.

Answer 4

As for DateCreated, I would just add a default constraint on that column set to SYSDATETIME() that takes effect when inserting a new row into the table.

For DateModified, personally, I would probably use triggers on those tables.

In my opinion, the trigger approach:

• makes it easier; I don't have to worry about and remember every time I save an entity to set that DateModified

• makes it "safer" in that it will also apply the DateModified if someone finds a way around my application to modify data in the database directly (using e.g. Access or Excel or something).