Auditing with Entity Framework and repository pattern

There is always a need to keep track of changes in the production database. We may want to create a log or maintain a history of these changes. There are multiple ways of doing this. One way is to have triggers on a table that creates these records along with who made the change and when was it made.

I was going to implement this with Entity Framework. I came across this good post about it. It starts with DDL to create an audit table, then the entity, and how to make use of the change tracker. The DbEntityEntry in the change tracker gives us the old and the new value of an entity. It shows how to do the logging by overriding the SaveChanges method on the DbContext.

If you are using the repository pattern, it won’t work as it is. When you add or attach an entity, the changes get detected. As a result, in the GetAuditRecordsForChange method, the original state and the current state of an entity will be the same and it will not create any logs. The details of how detect changes in EF works can be found here.

In order to resolve this, we need to delay the changes to be detected until the save method is called. The way to do this is by turning off the AutoDetectChangesEnabled to false before a method that triggers the detect changes. Here’s how it looks like :

 public virtual void Add(T entity)
 {
    DataContext.Configuration.AutoDetectChangesEnabled = false;
    DbSet.Add(entity);
 }

Now everything works fine and logs are properly created.

There is always a need to keep track of changes in the production database. We may want to create a log or maintain a history of these changes. There are multiple ways of doing this. One way is to have triggers on a table that creates these records along with…

Read More

MVC4 SQL migrations step by step

In my earlier post , I showed how to use code first with the server where the app cant create a database.

Now, we are looking to migrate our schema on to the server. It would be nice if it we can get a nice DDL script for this that we can walk through the test envrironments to production. Our DBA friends will definitely allow this.

Let's see how to do this step by step.

Step 1: Open the Package Manager Console (Tools=> Library Package Manager => Package Manager Console)

Step 2: run Update-Package EntityFramework
This updates the EF to 4.3.1 version.

Step 3: Restart vs after the update (important!)

Step 4: run Enable-Migrations -EnableAutomaticMigrations

It generates Migrations folder with Configuration.cs class. It looks like:

namespace MvcApplication4.Migrations
{ using System; using System.Data.Entity; using System.Data.Entity.Migrations; using System.Linq;

internal sealed class Configuration : DbMigrationsConfiguration<MvcApplication4.Models.CompStoreDBContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(MvcApplication4.Models.CompStoreDBContext context)
    {
        //  This method will be called after migrating to the latest version.

        //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
        //  to avoid creating duplicate seed data. E.g.
        //
        //    context.People.AddOrUpdate(
        //      p => p.FullName,
        //      new Person { FullName = "Andrew Peters" },
        //      new Person { FullName = "Brice Lambson" },
        //      new Person { FullName = "Rowan Miller" }
        //    );
        //
    }
}

}

Step 5: Build the project (important!) as we have added new stuff in the project. If we dont build, the up and down method are empty. It will get clearer after next step

Step 6: run Add-Migration "InitialCreate"
It creates a file with a time stamp such as 201205261630323_InitialCreate.cs. This file looks like below:

namespace MvcApplication4.Migrations
{ using System.Data.Entity.Migrations;

public partial class InitialCreate : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "Computers",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    Descr = c.String(),
                    Type = c.String(),
                })
            .PrimaryKey(t => t.Id);

    }

    public override void Down()
    {
        DropTable("Computers");
    }
}

}

This file has Up and down methods. The up method is for applying the change and the down is there if we need to roll back. It also helps in managing the destructive db changes such as changing the datatype of decimal column to int.

Step 7: run Update-Database -Verbose
This is going to work only if you have ddl permissions on the db. If not, the next step shows a way to create a DDL script. Note : You cant create another migration untill the previous ones are applied.

Step 8: run Update-Database -Script -SourceMigration:$InitialDatabase -TargetMigration:"InitialOne"It generates a nice looking script for our model that we can take to production.

Hope this helps.. Again, dont forget to build after the Enabling the migration otherwise up and down methods get created empty!..

Update:

I used VS 2010 for this demo. If you use VS 2012, building the project after the enabling the migrations is not required.

In my earlier post , I showed how to use code first with the server where the app cant create a database. Now, we are looking to migrate our schema on to the server. It would be nice if it we can get a nice DDL script for this that we…

Read More

Code first with no create DB permissions on the server

The hosting services or the production database servers do not give create db rights to the app for a lot of reasons. So, when our code first MVC app gets deployed it does not work as it tries to create a database.

There are a lot of posts about code first with existing database. They often get too much into class structure for the models and how looks like on db. The solution of this is too burried or not there at all. So I decided to post about only this issue.

In order to handle this, we need to make it use the existing database and we also need the schema.

Here's how I accomplished it:

I used a computer store as an example. As it sells computers(do we even use that word), I created a model as

public class Computer { [Required] public int Id { get; set; }

 public string Name { get; set; }

 [AllowHtml]
 [DataType(DataType.MultilineText)]
 public string Descr { get; set; }

 public string Type { get; set; }

 public Decimal Price { get; set; }
}

Then I created a controller for the with a new db context as CompStore. The db context is the key here and it looks like:

public class CompStoreDBContext : DbContext {

public CompStoreDBContext(): base("name=DefaultConnection")
{

}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

}

 public DbSet Computers { get; set;  }

}

Notice the constructor. It inherits from the base and the connection string name is passed to it.

If there's something special we need to such as define table names on in DB, we can use OnModelCreating event. Here, I left it alone.

Now, it uses the database passed in the connection string but it still doesnt have the schema. Sometimes we dont even have DDL rights.

No issues MVC4 migrations made this all easy. I will show it in nextpost.

The hosting services or the production database servers do not give create db rights to the app for a lot of reasons. So, when our code first MVC app gets deployed it does not work as it tries to create a database. There are a lot of posts about code…

Read More