Entity Framework Code First Automatic Migration & Existing Tables

We have been smooth sailing with EF6 Code First migration, but recently had few issues to dealt with migrating existing db tables.

Basically we have the below requirements.

  1. All new tables we create use Code First migration.  We want to avoid migration any existing db tables. Also we still want the EF mappings to configure for the existing tables, and have those entities in DbContext so we can work with those tables.
  2. We want to use the automatic migration. – this seems to be working well within the team environment.
  3. We want the migration to work only in our Dev Env, i.e not in QA, UAT, or Prod Envs.

Below are couple of  related SO questions..

http://stackoverflow.com/questions/19964679/ef-5-code-migration-errors-there-is-already-an-object-named-in-the-datab

http://stackoverflow.com/questions/15303542/entity-framework-automatic-migrations-existing-database

The problem is that the EF migration does not appear to be seamlessly working with existing table structure.

When we run the automatic migration for the very first time, we want the existing table structure to be unchanged. But we also want the EF to be aware of the existing tables so we can perform operations on those tables.  It seems like we can only have one way or the other but not both.

Old and new Entities

(I have simplified the code so we can focus on the problem)

public class NewTable : BaseEntity
{
    public string Title { get; set; }
}

public class OldTable
{
     public int Id { get; set; }
     public string Name { get; set; }
}

BaseEntity contains some common properties such as ID, DateModified etc

Below is how the Mappings look like

    public class NewTableMap : EntityMap<NewTable>
    {
        public NewTableMap()
        {
            Property(x => x.Title).HasMaxLength(40);
        }
    }

    public class OldTableMap : EntityTypeConfiguration<OldTable>
    {
        public OldTableMap()
        {
            HasKey(t => t.Id);
            ToTable("OldTable");
            Property(x => x.Id).HasColumnName("fldId");
        }
    }

Note that I have explicitly specified the OldTable entity has been mapped to “OldTable” And the Id has the db column name “fldId”

DBContext has the standard operations including the model binding.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Configurations.Add(new OldTableMap());
     modelBuilder.Configurations.Add(new NewTableMap());

     base.OnModelCreating(modelBuilder);
}

The SQL Database would just have the old table

Since we starting from the scratch let’s enable the migration.

PM> Enable-Migrations -EnableAutomaticMigrations

The above command would create a new Migration Configuration with AutomaticMigration set to True.

We have not created any tables yet! So let’s run the migration command.

PM> Update-Database

We get the below error..
There is already an object named ‘OldTable’ in the database.

As you know there is our existing table in the db.
The script that EF creates had a CreateTable(“dbo.OldTable”), the migration throws the above error.

How can we tell the EF migration to ignore my OldTable??? Honestly don’t know the answer for this. This why the below work around may help you.

First create a specific migration.

PM> Add-Migration FirstMigration

This will create a migration script within the Migration folder. The script has 2 create table functions. One for the NewTable, one for the OldTable.


public override void Up()
{
     CreateTable(
                "dbo.NewTable",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Title = c.String(maxLength: 40),
                        IsActive = c.Boolean(nullable: false),
                        DateModified = c.DateTime(nullable: false,
                                    precision: 7, storeType: "datetime2"),
                        DateCreated = c.DateTime(nullable: false,
                                    precision: 7, storeType: "datetime2"),
                        UserId = c.Guid(nullable: false),
                        MarkAsDeleted = c.Boolean(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

  CreateTable(
                "dbo.OldTable",
                c => new
                    {
                        fldId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.fldId);

}

If we were to run this migration (as above) we would get exactly the same error. So let’s modify the above migration script, for example by removing the CreateTable(“dbo.NewTable”,()… DropTable(“dbo.OldTable”); We leave the scripts for creation of new Tables as it is.

Now if you run the

PM> Update-Database
Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201407120749125_FirstMigration].
Applying explicit migration: 201407120749125_FirstMigration.
Running Seed method.

Now if you check the database, the NewTable has been created and the existing table has not been changed.

Handling practical problems

It is very likely that someone else in your team has a different model changes to your model changes. If that’s the case, you also want to make sure that you don’t commit your initial migration script. You can treat it as your private migration script. This also assumes that you have non-shared database for development.

You team would also have to follow the same process. You can add this migration file to the exclusion list of the source control so the file won’t get committed to the repository.

Now you can make changes to the model as you would normally do and running the automatic migration would apply those changes accordingly.
It will never attempt to create your existing table as the automatic migration is now based on the modified FirstMigration. Subsequent migrations would use the last successful migration.

Just be cautious that in your next automatic migration…
Changing an entity which already mapped to an existing table would also make changes to the existing table.
If you use Ignore() method i.e ( Ignore(x => x.Name)) on the existing entity, it will also exclude that property from the existing table.
If you Ignore() method on the entire existing table during model binding i.e modelBuilder.Ignore(); it will just remove your exiting table. If this happens, simply remove the .Ignore and run the migration.

 

VS2010 and ReSharper performances

Below are some tips to improve the performances of ReSharper and VS2010 IDE.

Sometimes it is not just ReSharper causing the overall VS to depreciate the performances, but lot of other factors contribute to this as well. Also think about getting familiar with ReShaper well. Even  though ReSharper causes for a performance decrease, you still code way faster with it. Try to use code snippets, short cut key and being more productive increases the development speed.

Below are few tips to help you maximise the performance of your IDE/ReSharper. Please note that enabling some of the below items you are trading the productivity for the speed of your IDE. If none of the below tips aren’t assisting your problem, then consider upgrading hardware as a last resort.

VS2010

Uninstall Extensions
Disable the un-used extension that you don’t need. Navigate to VS Tools-> Extension Manager -> Disable or Uninstall extension that aren’t used.

VS Hot Fixes
Ensure you have updated performance related hot fixes from Microsoft Visual Studio http://connect.microsoft.com/VisualStudio/Downloads There are evidence that people have downloaded some of hot fixes got performance boost in their development environment.

Project Unload
Unload projects you currently not working on. The less projects means, less source files, less read and writes, hence faster processing speed. You can unload the project that you are not working on.

Remove Navigation Bar
If you are using ReSharper, you don’t need VS2010 to view list of methods and fields at the top of the file (CTRL+ Alt +F does this nicely). Go to Tools | Options | Text Editor | C# and uncheck Navigation bar.

Turn off Track Changes
Go to Tools | Options | Text Editor and uncheck Track changes. This will reduce overhead and speeds up IDE response.

Turn off Track Active items
This will turn off jumping in the explorer whenever you select different files in different projects. Go to Tools | Options | Projects and Solutions and uncheck Track Active Item in Solution Explorer. This will ensure that if you are moving across files in different projects, left pane will still be steady instead of jumping around.

Turn off AutoToolboxPopulate
There is an option in VS 2005 that will cause VS to automatically populate the toolbox with any controls you compile as part of your solution. This is a useful feature when developing controls since it updates them when you build, but it can cause VS to end up taking a long time in some circumstances. To disable this option, select the Tools | Options | Windows Forms Designer and then set AutoToolboxPopulate to False. If you are not using turn off this feature so you get better response time

Turn Off splash screen
When you launch VS2010, it first loads a splash screen. You can disable this and improve the startup time.
Change the  Target path of your VS2010 shortcut to
“C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe” /nosplash

Pay attention to Warnings and Errors
Not a big deal but this also contributes to VS compilation. The point here is, having a lot of messages serialized to output causes slowdown, so pay attention to first sign of your warnings and fix it.

IntelliTrace (Ultimate Only)
Most of you don’t use VS2010 Ultimate Edition, but if you do and if you realise your IDE is performing slow, try disabling IntellTrace.
Tools | Options | IntelliTrace | General. 

Build and Run
When you press F5 to debug an application Visual Studio first compiles the projects, then runs your project and attaches the debugger. You can cut down on the number of projects that Visual Studio builds during this process by checking the Tools | Options |“Only Build startup projects and dependencies on Run” checkbox in Tools\Options. This increased performance of large projects.

Turn off the Startup Page
Tools -> Options -> Environment -> Startup.
You could configure this to “load last loaded solution”, and or remove the startup channel.

Keep your projects nice and clean
Permanently remove unwanted files, assembly references, and project files from the solution. If you need them back for some reason they are in the TFS. Add-Ins like ReShaper constantly compile the code in background, so lesser is the better.

Hardware acceleration
There are some articles on the web which indicates that visual studio doesn’t run well on machines without a graphic card that supports DirectX 9 or with bugs in the Graphic Card Driver. This is due to the new UI which is build on WPF Visual Studio which uses hardware accelerations. You can try to disable it, for better performance. 

Tools|Options|Environment |General Uncheck the “Automatically adjust visual experience based on client performance” checkbox.
For more information please refer to the below link.
http://support.microsoft.com/kb/2023207

ReSharper

As with VS, you could easily turn off the feature you don’t need on daily basis.

– Navigate to ReSharper | Options, select Settings under “Code Inspection”. Ensure Analyse errors in whole solution is turned off.
– Navigate to ReSharper | Options, select Inspection Severity  under “Code Inspection”. There are lot of static analysis features you could turn off. Simply select from the drop down “Do not show”.
– Try disabling ReSharper IntelliSence and use VS IntelliSense instead.
– There were some performance issues with ReShaper 5. As per JetBrains threads, those issues have been rectified. Please ensure you have the latest ReSharper installed.

Un-Installing Ghost Doc

While this might be so obvious to many of you, I found it quite difficult to un-install an older version of Ghost Doc. Don’t get me wrong I care about this as this is one of my favourite tools.

Simply navigate to
C:\Program Files (x86)\SubMain\GhostDoc\
and run
(may be you would not need to navigate to this folder – have not tried yet..)

msiexec /x {134A5765-D59B-4160-8C70-B84BF9F53DF9}

Why you should consider using xUnit

Recently I have been using the xUnit Testing framework and I would like to share few of its nice features with you. It is simple, easily extensible and aligned with practices such as TDD (Test Driven Development) and BDD (Behaviour Driven Development).

Test attributes

xUnit does not require an attribute for a test class. It looks for all test methods in all public classes in the assembly. The [Fact] attribute defines whether the public method should be included as a Test method or not.

If you are already familiar with NUnit or MBUnit, you probably know the [Setup] attribute. If you use MSTest then you also probably know the [TestInitialize] attribute. These attributes allow you to specify the code to be run before executing each test. xUnit doesn’t have this attribute and you can simply use the  parameterless constructor as a direct replacement.

Similarly, xUnit does not specify attributes such as [TestCleanup] or [Teadown], and instead you would use the IDisposable.Dipose() method to clean up the resource(s) when required.

Personally, I see this as a much cleaner way to write Unit Tests since there is less code, less maintenance therefore less refactoring.

From xUnit team…

 “The xUnit.net team feels that per-test setup and teardown creates difficult-to-follow and debug testing code, often causing unnecessary code to run before every single test is run.

Ignore Tests

One of the nice features of xUnit is that the [Skip] attribute also accepts an argument so you can specify the reason for your exclusion for the test method. All other major Unit Testing frameworks allow you to use an [Ignore] attribute, but you cannot specify the reason of why it has been ignored/skipped. 

[Fact(Skip = "This test require refactoring.")]
public void AssertAreEqual()
{
//Your test goes here.
}

Asserts

Most xUnit Assert methods don’t have the message as an argument to the Assert methods. xUnit team believes the Asserts API should be descriptive enough except the True/False Asserts. For example Assert.Equal(“Pat”, value, “Value should have been Pat”) doesn’t add much value.

 

Data driven or parameterized Unit Tests

xUnit also supports data driven and parameterized Unit Tests. For example you can configure your test inputs within the attribute and specify them as an argument to your test method. You can specify parameters to be injected to the test method by specifying the [Theory] and [InlineData]. xUnit support variety of data providers including [ExcelData],  [SqlServerData], and [OleDbData].

[Theory]
[InlineData("hello", 5)]
public void TestTraditional(string value, int expectedLength)
{
    Assert.Equal(expectedLength, value.Length);
}

Please note that you must use the xunit.extensions.dll  in order to use data driven or parameterized Unit Tests.

 

Extending xUnit framework

One of the great things about using xUnit is that it is heavily extensible. You may extend the Test Class, Test Method, or even Asserts. Below is an example of how you would create a Trace attribute to trace your Unit Tests.

 public class BeforeAfterTests
 {
     [Fact, Tracing]
     public void AssertAreEqual()
     {
         Console.WriteLine("I'm inside the test!");
     }
}


public class TracingAttribute : BeforeAfterTestAttribute
{
     public override void Before(MethodInfo methodUnderTest)
     {
        Console.WriteLine("Before : {0}.{1}", methodUnderTest.DeclaringType.FullName, methodUnderTest.Name);
     }

     public override void After(MethodInfo methodUnderTest)
     {
         Console.WriteLine("After : {0}.{1}", methodUnderTest.DeclaringType.FullName, methodUnderTest.Name);
     }
}

 

VS Integration

For a better VS.Net integration there are some other test runners you can use.  You may use TestDriven.NET, or Telerik’s Just Code Unit Test runner within Visual Studio to run xUnit Test methods. If you are ReSharper user – xUnitContrib project has plugin you can use for ReSharper support.

Summary

If you are TDD developer and would love to experiment and extend Unit Tests, I would certainly recommend that you should try xUnit. The attribute names can be unfamiliar in the first go, but the amount of benefits you get here is quite noticeable comparing to other testing frameworks.  Developing your application and writing Unit Test at the same time is not easy as sometimes you need to ensure you save time by reducing unwanted code and spending less time for debugging and refactoring tasks. xUnit provides a nice way to overcome these issues and I highly recommend it.

Below are some links to get you started with xUnit.

Download xUnit

http://xunit.codeplex.com/

Download xUnit contrib

http://xunitcontrib.codeplex.com/

Integrating xUnit to Team Build process

http://jonnekats.wordpress.com/2009/05/07/integrate-xunit-tests-into-your-daily-team-build/

 

Downgrading your VS projects from VS 2010 to VS 2008

I know this not what we want to do or what we like to do. We all like to do the upgrade. However there are instances where you have a VS solution file that you created in VS 2010, and when you try to open it in a machine where only VS2008 installed, you cannot open it. I had this problem today and it is quite easy to resolve this issue.

Before you do anything backup your files. Open your solution file in your favourite text editor and change the below Format version to 10.00 and # visual studio version to 2008

image1

Below tool ToolsVersion to 3.5 if you have .NET 3.5 installed.

image2

That’s it save your file and you can open your project and the solution in VS2008. . I hope this tip will be handy for you.

VS2010 Undo Close Documents

I thought this would be quite a nice feature to share with you all.

Prerequisites

You need to download Power Commands for Visual Studio 2010. It is free.

What’s this all about?
You might already know this, if not this is about re opening the closed documents.
There are two ways to this.

1. Re opening the most recent closed document.
image3

If you close one or many opened documents within VS, you can re open the last closed document by navigating to Edit -> Undo Close. Alternatively you can use Ctrl + Shift + Z

2. Undo Closed Window.

You can also see your last closed documents in “Undo Close Window”. You can open this window by just navigating to View  -> Other Windows – > Undo Closed Windows

image4