Saving and retrieving CreatedBy User information with EF7, MVC6, ASP.NET Identity

Question

I am using the latest MVC6 and Entity Framework 7, however I am sure many techniques used in MVC5 and Entity Framework 6 could help answer my question also.

Almost all of the tables in my database have the following 4 fields for auditing: CreatedDate, CreatedBy, ModifiedDate, ModifiedBy.

I am trying to figure out which field from the built in IdentityUser (AspNetUsers table) I should be storing in the CreatedBy field when saving items to the database.

I started by trying to use 'Username' since it is easily accessible by calling User.Identity.Name and passing it down to the repository when saving. Here is how I configured EF using Fluent API to help with retrieving the User who created an item along with all of their fields:

builder.Entity<BlogPost>()
    .Property(bp => bp.CreatedBy)
    .HasMaxLength(256);

builder.Entity<BlogPost>()
    .HasOne(bp => bp.CreatedByUser)
    .WithMany()
    .HasForeignKey(bp => bp.CreatedBy)
    .HasPrincipalKey(u => u.UserName);

But then I noticed that Entity Framework yells when trying to add migrations and create the database because Username needs to be set as a primary key or it can't be used as a foreign key in another table.

Then I got to the point where I figured I would just use the actual Id which is a GUID. The problem with this technique is that the Id of the current logged in user isn't easily available when trying to save: 'User.Identity.Name' is all that is there.

Here are a couple of questions that I would like someone with EF and Auditing experience to try and answer for me:

1. Do people even use Audit fields in all of their tables anymore? I don't see many others asking questions about this and Microsoft definitely doesn't make it easy to work with their new Identity system and custom audit fields.

2. Should I be storing Username or Id in my CreatedBy field. Some say this might be preference but I really want to know what direction Microsoft might be pushing with the new Identity. The problem with storing Id is that it is hard to get it when saving and the problem with storing Username is that it isn't a primary key in AspNetUsers table.

3. I really would just like to know of a good pattern in general when using EF that handles auditing when saving, and retrieving the User and setting it as a Navigation property on my entities that need it when pulling records from the database.

Answer 1

I am trying to figure out which field from the built in IdentityUser (AspNetUsers table) I should be storing in the CreatedBy field when saving items to the database.

The user name of the person using the site (on Thread.CurrentPrincipal, accessed in ASP.Net via User.Identity.Name). Who else? That is the identity of the current authenticated user using your site, and it is what you should be putting in the audit tables.

Do people even use Audit fields in all of their tables anymore? I don't see many others asking questions about this and Microsoft definitely doesn't make it easy to work with their new Identity system and custom audit fields.

Yes, people do! All the time! If you are storing data that can be edited in any way by an end user (whether they are in your company or not), audit it. Always. I was going to say that all enterprises audit stuff to the extreme (and they do), but I even do that on my own personal projects. Metrics are extremely important!

And one important thing to remember is that just because people aren't asking about it on StackOverflow or some other site doesn't mean that it isn't prevalent and critical in our industry.

Should I be storing Username or Id in my CreatedBy field. Some say this might be preference but I really want to know what direction Microsoft might be pushing with the new Identity

Microsoft (and the team behind their Identity framework) are doing a great job with providing us a secure and robust security framework. Maybe they would recommend their approach to this problem, but their framework isn't really meant to address those nuances (which can and will differ from system to system). At the end of the day, pick whichever suits the schema of your database. I think most of the time that the Username would be appropriate to store (if it is unique within your system). After all, they both represent the same information (unless your usernames are not unique, which begs further questions).

I really would just like to know of a good pattern in general when using EF that handles auditing when saving, and retrieving the User and setting it as a navigation property on my entities that need it when pulling records from the database.

It is not, and most likely never will be EF's concern to help you with something like this. Sorry, that's just the way it is. Each application is unique, and EF (or any other ORM) can't be expected to meet everyone's needs.

I realize all of this doesn't really provide you with concrete answers, but I had to drop some advice.