DateCreated or Modified Column - Entity Framework or using triggers on SQL Server

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#.

Timothy Walters · Accepted Answer · 2014-04-28T22:44:10.890

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.

this is a well detailed answered, Thank you Tim. I am convienced by your way and it is good to maintain this logic in application layer. Do you think it is possible or worthwhile for EF team to add default constrains and trigger support into DataAnnotation or FluentAPI? — anIBMer, May 15 '13 at 22:35
I would welcome the EF team adding that, it would make things easier for simple rules. That said, there are so often exceptions to the rules, so knowing how to take full control of the process is a valuable skill to learn. — Timothy Walters, May 16 '13 at 00:38
"System.DateTime.Now" - you shouldn't use that IMO. It's taking the current server datetime (whatever that timezone is). It's better to go with a UTC datetime. — Michael Clark, Jul 03 '17 at 11:38
For me, the problem is that I don't always control what is modified in the entities, so if I don't read in the database first to take the creation date and creation user and put it in the new object, it might me overridden. — Nicolas Belley, Sep 08 '17 at 11:36

score 6 · Answer 2 · answered May 15 '13 at 04:42

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).

I am using code first, so a trigger can be created in DB-Migration. Though to add a default constraint to a column, i don't think i can do that via DBMigration or Data Annotation, can i? — anIBMer, May 15 '13 at 10:11

score 4 · Answer 3 · answered Feb 27 '16 at 07:53

4

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/

answered Feb 27 '16 at 07:53

marisks

1,812
1
17
33

score 2 · Answer 4 · edited Mar 01 '16 at 17:31

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.

DateCreated or Modified Column - Entity Framework or using triggers on SQL Server

4 Answers4

Linked