4

I am working on a Code-First project, and I need database to handle DateCreated and DateModified.

The application is running on IIS Express with LocalDB on dev machine, and will be using SQL Server 2012 on deployment server with IIS 7.5.

I have the following model:

public class Person : IdentityUser {

  [Required]
  public string Name { get; set; }

  public Date DateOfBirth { get; set; }

  public string Address { get; set; }

  [DatabaseGeneratedOption.Identity]
  public Date DateCreated { get; set; }

  [DatabaseGeneratedOption.Identity]
  public Date DateModified { get; set; }

}

Please lay out exact steps to configure DB to handle the transaction meta dates, like what needs to be set in model and if there are any actions to be taken in DB context configurator. I was searching for something like: "All you need to know about ASP.NET MVC date handling", but couldn't much touching that aspect.

Thanks in advance.

Annie
  • 3,090
  • 9
  • 36
  • 74

3 Answers3

5

I will think about this from the Entity-framework Point of view.

You basically need to do the following :

1- I will define an interface like ITrackable interface, and make my models implement that interface if these models need track the DateCreated and DateModified porperties.

2- Somehow your model know whether it is am Added/Modified entities because this will decide which property to set (Both for Added entity and just DateModified for Modified entities).

3- With your DbContext in Entity-Framework Add an extension method that you need to call when you try to save your entities through SaveChanges or SaveChangesAsync and this method will loop through the tracked entities and set the DateCreated and DateModified properties according to the entity's state.

So your model will look like this:

public class Person : IdentityUser, ITrackable
{

    [Required]
    public string Name { get; set; }

    public Date DateOfBirth { get; set; }

    public string Address { get; set; }

    public DateTime DateCreated { get; set; }

    public DateTime DateModified { get; set; }
}

Where ITrackable looks like

public interface ITrackable
{
    public DateTime DateCreated { get; set; }

    public DateTime DateModified { get; set; }
}

and the extension method will be something like this:

internal static class ContextHelper
{
    internal static void SyncObjectsStatePreCommit(this DbContext dbContext)
    {
        foreach (var dbEntityEntry in dbContext.ChangeTracker.Entries())
        {

            // do any other stuff you want.
            // ..
            // ..

            // work with ITrackable entities
            var trackableObject = dbEntityEntry.Entity as ITrackable;

            // we need to set/update trackable properties
            if (trackableObject == null)
            {
                continue;
            }

            var dateTime = DateTime.Now;

            // set createddate only for added entities
            if (entityState.ObjectState == ObjectState.Added)
            {
                trackableObject.CreatedDate = dateTime;
            }

            // set LastUpdatedDate for any case other than Unchanged
            if (entityState.ObjectState != ObjectState.Unchanged)
            {
                trackableObject.ModifiedDate = dateTime;
            }
        }
    }
}

Now in your dbContext Save method for example you need to call this method to setup all these properties.

public override Task<int> SaveChangesAsync()
{
    this.SyncObjectsStatePreCommit();
    return base.SaveChangesAsync();
}

Hope that helps.

Omar.Alani
  • 4,050
  • 2
  • 20
  • 31
  • Hi thanks for the elaborate answer. This is saving the dates programmatically. My intention is to let SQL Sever take care of updating those date fields and making use of `[DatabaseGeneratedOption.Identity]`. – Annie Dec 01 '14 at 07:05
  • @Annie `Identity` is used for an `autoincremented` unique int value (so you can't use it for a Date column). And there can be only one per table. – Raphaël Althaus Dec 01 '14 at 08:06
  • 1
    If you want to do it from the database level then you have to do it through triggers, however you still can't use identity columns for this. It's up to you to do it from the code or using triggers, however people prefer doing it from code, as you have more control for example if you want to add modifiedby or createdby columns which will be just adding those new fields to the above code side by side with the other properties. – Omar.Alani Dec 01 '14 at 08:27
  • Thank you Omar and Raphael. Now it makes sense and I will try to make it `ITrackable`. However, I have found this answer also useful: http://stackoverflow.com/a/25506833/1712065. – Annie Dec 01 '14 at 14:27
  • Where would the extension method go? – Joseph Casey May 06 '15 at 18:09
  • 1
    @JosephCasey either define it as an extension method and create a static extension class which has the method above defined in it, OR you define the method as a private method to your DbContext class which means you need to change the code above to remove "this DbContext dbContext" parameter and access the ChangeTracker.Entries() directly since it will be available to your method. – Omar.Alani May 07 '15 at 00:26
2

I found the above answer a bit confusing, however i think a more straight forward solution would be to override the SaveChanges method as described in this blog

This solution also uses the ITrackable interface and when SaveChanges is fired it checks whether an Entity implements this interface:

public override System.Threading.Tasks.Task<int> SaveChangesAsync()
{
    foreach (var auditableEntity in ChangeTracker.Entries<ITrackableEntity>())
    {
        if (auditableEntity.State == EntityState.Added ||
            auditableEntity.State == EntityState.Modified)
        {
            // implementation may change based on the useage scenario, this
            // sample is for forma authentication.
            string currentUser = HttpContext.Current.User.Identity.GetUserId();
            DateTime currentDate = SiteHelper.GetCurrentDate();

            // modify updated date and updated by column for 
            // adds of updates.
            auditableEntity.Entity.ModifiedDateTime = currentDate;
            auditableEntity.Entity.ModifiedUserId = currentUser;

            // pupulate created date and created by columns for
            // newly added record.
            if (auditableEntity.State == EntityState.Added)
            {
                auditableEntity.Entity.CreatedDateTime = currentDate;
                auditableEntity.Entity.CreatedUserId = currentUser;
            }
            else
            {
                // we also want to make sure that code is not inadvertly
                // modifying created date and created by columns 
                auditableEntity.Property(p => p.CreatedDateTime).IsModified = false;
                auditableEntity.Property(p => p.CreatedUserId).IsModified = false;
            }
        }
    }
    return base.SaveChangesAsync();
}
JustLearning
  • 3,164
  • 3
  • 35
  • 52
0

I believe that Entity Framework documentation explicitly states that:

Warning

Unlike with default values or computed columns, we are not specifying how the values are to be generated; that depends on the database provider being used. Database providers may automatically set up value generation for some property types, but others may require you to manually set up how the value is generated. For example, on SQL Server, when a GUID property is configured as value generated on add, the provider automatically performs value generation client-side, using an algorithm to generate optimal sequential GUID values. However, specifying Value Generated On Add on a Date Time property will have no effect (see the section below for Date Time value generation). Similarly, byte properties that are configured as generated on add or update and marked as concurrency tokens are set up with the row version data type, so that values are automatically generated in the database. However, specifying Value Generated On Add has no effect.

This is on page 284 of the docs when downloaded (assuming yours do not duplicate some parts). The docs go on to say:

Note

Depending on the database provider being used, values may be generated client side by EF or in the database. If the value is generated by the database, then EF may assign a temporary value when you add the entity to the context; this temporary value will then be replaced by the database generated value during Save Changes. For more information, see the docs on temporary values.

Anyhow, RTFM!!

Phume
  • 55
  • 11