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

entity-framework sql-server triggers

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

1
12
5/23/2017 12:00:27 PM

Accepted Answer

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.

24
4/28/2014 10:44:10 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow