This is a post about code refactoring, the long journey of removing technical debts in the system I have built at work.

Since late 2015, I have been working on a system which is supposed to be extensible and scalable to support our branches in different countries. With the frequent change of user requirements and business strategies, mistakes and system-wide code refactoring are things that I can’t avoid.

Today, I’m going to list down some key lessons and techniques I learned in this one year plus of journey of clearing technical debts.

…we are not living in a frozen world of requirements and changes. So you can’t and shouldn’t avoid refactoring at all.

The risk in avoiding would definitely bring up messy codes, and tough maintenance of your software, you can leave it behind but somebody else would suffer.

— Nail Yuce, Author of the redbook “Collaborative Application Lifecycle Management”

Databases Organization

Originally, we have three branches in three countries. The branches are selling the same product, laptop. It’s business decision that these three branches should behave individually with different names so that customers generally can’t tell that these three branches are operated by one company.

Let’s say these three branches have names as such, Alpha, Beta, and Gamma. Instead of setting up different databases for the branches, I put the tables of these three branches in the same database for two reasons:

  1. Save cost;
  2. Easy to maintain because there will be only one database and one connection string.

These two points turn out to be invalid few months after I designed the system in such a way.

I’m using Azure SQL, actually separating databases won’t incur higher cost because of the introduction of Elastic Database Pool in Microsoft Azure. It’s also not easy to maintain because to put three business entities in one database, I have two ways to do it.

  1. Have a column in each table specifying the record is from/for which branch;
  2. Prefix the table names.

I chose the 2nd way. Prefix the table names. Hence, instead of a simple table name such as Suppliers, I now have three tables, APSupplier, BTSupplier, and GMSupplier, where AP, BT, and GM are the abbreviation of the branch names.

This design decision leads to the second problem that I am going to share with you next.

Problem of Prefixing Table Names

My senior once told me that experience was what made a software developer valuable. The “experience” here is not about technical experience because technology simply moves forward at a very fast pace, especially in web development.

The experience that makes a software developer valuable is more about system design and decision making in the software development process. For example, now I know prefixing table names in my case is a wrong move.

Experience helps in building a better system which is not “too simple and naive”.

There are actually a few obvious reasons of not prefixing.

For those who are using Entity Framework and love intellisense feature in Visual Studio IDE, they will know my pain. When I’m going to search for Supplier table, I have to type the two-letter branch abbreviation first then search for the Supplier table. Hence in the last one year, our team spent lots of man hours going through all these AP, BT, and GM things. Imaging the company starts to grow to 20 countries, we will then have AP, BT, GM, DT (for Delta), ES (for Epsilon), etc.

To make things worse, remember that three branches are actually just selling the laptops with the similar business models? So what would we get when we use inheritance for our models? Many meaningless empty sub-classes.

public abstract class BaseSupplier
    public int Id { get; set; }
    public string Name { get; set; }
    public string PersonInCharge { get; set; }
    public string Email { get; set; }
    public bool Status { get; set; }

public class APSupplier : BaseSupplier { }

public class BTSupplier : BaseSupplier { }

public class GMSupplier : BaseSupplier { }

So if we have branches in 20 countries (which is merely 10% of the total number of countries in the world), then our software developers’ life is going to be miserable because they need to maintain all these meaningless empty classes.

Factory Design Pattern and Template Methods

However, the design above actually at the same time also makes our system to be flexible. Now, imagine that one of the branches requests for a system change which requires addition of columns in its own Supplier table, we can simply change the corresponding sub-class without affecting the rest.

Design Patterns are good if we know how to use them properly. (Image Source: Rewrite)

This leads us to the Factory Design Pattern. Factory Design Pattern allows us to standardize the system design for each of the branch in the same system while at the same time allowing for individual branch to define their own business models.

public abstract class SupplierFactory
    public static SupplierFactory GetInstance(string portal)
        return Activator.CreateInstance(Type.GetType($"Lib.Factories.Supplier.{portal}SupplierFactory")) as SupplierFactory;

    protected abstract BaseSupplier CreateInstanceOfSupplier();

    protected abstract void InsertSupplierToDatabase(BaseSupplier newSupplier);

    public abstract IQueryable RetrieveSuppliers();

    public async Task AddNewSupplierAsync(SupplierManageViewModel manageVM)
        var newSupplier = CreateInstanceOfSupplier();
        newSupplier.Name = manageVM.Name;
        newSupplier.PersonInCharge = manageVM.PersonInCharge;
        newSupplier.Email= manageVM.Email;
        newSupplier.Status = manageVM.Status;


For each of the branch, then I define their own SupplierFactory which inherits from this abstract class SupplierFactory.

public class AlphaSupplierFactory : SupplierFactory
    private AlphaDbContext db = new AlphaDbContext();

    public override IQueryable RetrieveSuppliers()
        return db.APSuppliers;

    protected override void InsertSupplierToDatabase(BaseSupplier newSupplier)


As shown in the code above, firstly, I no longer use the abbreviation for the prefix of the class name. Yup, having abbreviation hurts.

Secondly, I have also split the big database to different smaller databases which store each branch’s info separately.

The standardization of the workflow is done using Template Method such as AddNewSupplier method shown above. The creation of new supplier will be using the same workflow for all branches.


public static SupplierFactory GetInstance(string portal)
    return Activator.CreateInstance(Type.GetType($"Lib.Factories.Supplier.{portal}SupplierFactory")) as SupplierFactory;

For those who wonder what I am doing with the Activator.CreateInstance in the GetInstance method, I use it to create an instance of the specified type that type’s default constuctor with portal acts as an indicator on which sub-class the code should pick using reflection with the Type.GetType method. So the values for portal will be Alpha, Beta, Gamma, etc. in my case.

This unfortunately adds one more burden to our development team to pay attention to the naming convention of the classes.

Fluent API: ToTable

All these unnecessary complexity is finally coming to an end after my team found out how to make use of the Fluent API. Fluent API provides several important methods to configure entities which help to override Code First conventions. ToTable method is one of them.

ToTable helps mapping entity to the actual table name. Hence, now we can fix our naming issues in our databases with the codes below in each of the branch’s database context class.

protected override void OnModelCreating(DbModelBuilder modelBuilder)

    ... // mappings for other tables

With this change, we can furthermore standardize the behavior and workflow of the system for each of our branch. However, since we only start to apply this after we have expanded our business to about 10 countries, there will be tons of changes waiting for us if we are going to apply Fluent API to refactor our codes.

Technical Debts

I made a few mistakes in the beginning of system design because of lacking of experience building extensible and scalable systems and lack of sufficient time and spec given to do proper system design.

This naming issue is just one of the debts we are going to clear in the future. Throughout the one year plus of system development, the team also started to realize many other ways to refactor our codes to make it more robust.

As a young team in a non-software-development environment, we need to be keen on self-learning but at the same time understand that we can’t know everything. We will keep on fighting to solve the crucial problems in the system and at the same time improving the system to better fit the changing business requirements.

I will discuss more about my journey of code refactoring in the future posts. Coming soon!

Reading my old codes sometimes makes me slap myself. (Image Source: Rewrite)

Entity Framework and Database

By using Entity Framework, we can save a lot of time on writing SQL ourselves because Entity Framework, a Microsoft-supported ORM for .NET, is able to generate the SQL for us.

I started to use ADO .NET when I was building .NET web applications in my first job. I learnt about how to call stored procedures with ADO .NET. I witnessed how my colleague wrote a 400-line SQL to complete a task which we normally will choose to do it in C#. I also realized the pain of forgetting to update the stored procedure when the C# code is already different.

After that, my friend introduced me Entity Framework when I was working on my first ASP .NET MVC project. Since then, I have been using Entity Framework because it enables me to deliver my web applications faster without writing (and debugging) any SQL myself. I read a very interesting article comparing between Entity Framework and ADO .NET. The author also acknowledged that the performance of Entity Framework was slower than hand-coded ADO .NET. He emphasized that, however, Entity Framework did maximize his productivity.

How I react when I read a 400-line stored procedure submitted by my colleague.
How I react when I read a 400-line stored procedure submitted by my colleague.

What Is Happening in Database with Entity Framework?

The SQL generated by Entity Framework is believed to be pretty good. However, it’s still nice to be aware of what SQL is being generated. For example, I have the following code to retrieve Singapore weather info.

using (var db = new ApplicationDbContext())
    var forecastRecords = db.SingaporeWeathers.ToList();

In Visual Studio, I can just mouse-over “SingaporeWeather” to get the following query.

    [Extent1].[RecordID] AS [RecordID], 
    [Extent1].[LocationID] AS [LocationID], 
    [Extent1].[WeatherDescription] AS [WeatherDescription], 
    [Extent1].[Temperature] AS [Temperature], 
    [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[SingaporeWeathers] AS [Extent1]

If I have the following code which retrieves only records having temperature greater than 37, then I can use ToString().

using (var db = new ApplicationDbContext())
    var query = from sw in db.SingaporeWeathers where sw.Temperature > 37 select sw;
     [Extent1].[RecordID] AS [RecordID],
     [Extent1].[LocationID] AS [LocationID],
     [Extent1].[WeatherDescription] AS [WeatherDescription]
     [Extent1].[Temperature] AS [Temperature],
     [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[SingaporeWeathers] AS [Extent1]
WHERE [Extent1].[Temperature] > cast(37 as decimal(18))

I am using DBContect API, so I can just use ToString(). Alternatively, you can also use ToTraceString(), which is a method of ObjectQuery, to get the generated SQL.

SQL Logging in Entity Framework 6

It is a great news for developer when Entity Framework is announced to have SQL Logging feature added For example, to write database logs to a file, I just need to do as follows.

using (var db = new ApplicationDbContext())
    var logFile = new StreamWriter("C:\\temp\\log.txt");
    db.Database.Log = logFile.Write;
    var forecastRecords = db.SingaporeWeathers.Where(x => x.Temperature > 37).ToList();

Then in the log file, I can see logs as follows.

Closed connection at 6/6/2015 10:59:32 PM +08:00
Opened connection at 6/6/2015 10:59:32 PM +08:00
    [Project1].[C1] AS [C1], 
    [Project1].[MigrationId] AS [MigrationId], 
    [Project1].[Model] AS [Model], 
    [Project1].[ProductVersion] AS [ProductVersion]
    [Extent1].[MigrationId] AS [MigrationId], 
    [Extent1].[Model] AS [Model], 
    [Extent1].[ProductVersion] AS [ProductVersion], 
    1 AS [C1]
    FROM [dbo].[__MigrationHistory] AS [Extent1]
    WHERE [Extent1].[ContextKey] = @p__linq__0
) AS [Project1]
ORDER BY [Project1].[MigrationId] DESC
-- p__linq__0: 'MyWeb.Migrations.Configuration' (Type = String, Size = 4000)
-- Executing at 6/6/2015 10:59:32 PM +08:00
-- Completed in 70 ms with result: SqlDataReader

Closed connection at 6/6/2015 10:59:32 PM +08:00
Opened connection at 6/6/2015 10:59:32 PM +08:00
    [Extent1].[RecordID] AS [RecordID], 
    [Extent1].[WeatherDate] AS [WeatherDate], 
    [Extent1].[WeatherDescription] AS [WeatherDescription], 
    [Extent1].[WeatherSecondaryDescription] AS [WeatherSecondaryDescription], 
    [Extent1].[IconFileName] AS [IconFileName], 
    [Extent1].[Temperature] AS [Temperature], 
    [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[Weathers] AS [Extent1]
WHERE [Extent1].[Temperature] > cast(37 as decimal(18))
-- Executing at 6/6/2015 10:59:33 PM +08:00
-- Completed in 28 ms with result: SqlDataReader

So, as you can see, even the Code First migration related activity is logged as well. If you would like to know what are being logged, you can read an article about SQL Logging in EF6 which was written before it’s released.

Migration and the Verbose Flag

Speaking of Code First migration, if you would like to find out the SQL being generated when Update-Database is executed, you can add a Verbose flag to the command.

Update-Database -Verbose

Navigation Property

“I have no idea why tables in our database don’t have any relationship especially when we are using relational database.”

I heard from my friend that my ex-colleague shouted this in the office. He left his job few days after. I think bad codes and bad design do anger some of the developers. So, how do we do “relationship” in Entity Framework Code First? How do we specify the foreign key?

I quit!
I quit!

In Entity Framework, we use the Navigation Property to represent the foreign key relationship inside the database. With Navigation Property, we can define relationship between entities.

If we have a 1-to-1 Relationship between two entities, then we can have the following code.

public class Entity1
    public int Entity1ID { get; set; }
    public virtual Entity2 Entity2 { get; set; }

public class Entity2
    [Key, ForeignKey("Entity1")]
    public int Entity1ID { get; set; }
    public virtual Entity1 Entity1 { get; set; }

By default, navigation properties are not loaded. Here, the virtual keyword is used to achieve the lazy loading, so that the entity is automatically loaded from the database the first time a property referring to the entity is accessed.

However, there are people against using virtual keyword because they claim that lazy loading will have subtle performance issue in the application using it. So, what they suggest is to use the include keyword, for example

dbContext.Entity1.Include(x => x.Entity2).ToArray();

By specifying the ForeignKey attribute for Entity1ID in Entity2 class, Code First will then create a 1-to-1 Relationship between Entity1 and Entity2 using the DataAnnotations attributes.

For 1-to-n Relationship, we then need to change the navigation property, for example, in Entity1 class to use collection as demonstrated in the code below.

public class Entity1
    public int Entity1ID { get; set; }
    public virtual ICollection<Entity2> Entity2s { get; set; }

Finally, how about n-to-m Relationship? We will just need to change the navigation property in both Entity1 and Entity2 classes to use collection.

public class Entity2
    public int Entity2ID { get; set; }
    public virtual ICollection<Entity1> Entity1s { get; set; }

Together with the following model builder statement.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        .HasMany(e2 => e2.Entity1s)
        .WithMany(e1 => e1.Entity2s)
        .Map(e12 => 

The code above is using Fluent API which won’t be discussed in this post.

Database Context Disposal

When I first used Scaffolding in MVC 5, I noticed the template of controller class it generates look something as follows.

public class MyController : Controller
    private MyContext db = new MyContext();
    protected override void Dispose(bool disposing)
        if (disposing) 

Before using Scaffolding, I have always been using the Using block, so I only create database context where I have to, as recommended in a discussion on StackOverflow. Also, the Using block will have the Dispose() be called automatically at the end of the block, so I don’t need to worry about forgetting to include the Dispose() method to dispose the database context in my controller.

Azure SQL: Database Backup and Restore

Before ending this post, I would like to share about how DB backup and restore is done in Azure SQL Database.

First of all, Azure SQL Database has built-in backups and even self-service point in time restores. Yay!

For each activate databases, Azure SQL will create a backup and geo-replicate it every hour to achieve 1-hour Recovery Point Objective (RPO).

If there is a need to migrate the database or archive it, we can also export the database from Azure SQL Database. Simply click on the Export button in the SQL Databases section of Azure Management Portal and then choose an Azure blob storage account to export the database to.

Finally, just provide the server login name and password to the database and you are good to go.

Export DB from Azure SQL Database.
Export DB from Azure SQL Database.

Later, we can also create a new database using the BACPAC file which is being generated by the Export function. In the Azure Management Portal, click New > Data Services > SQL Database > Import. This will open the Import Database dialog, as shown in the screenshot below.

Create a new database in Azure SQL Database by import BACPAC file.
Create a new database in Azure SQL Database by import BACPAC file.

Okai, that’s all for this post on Entity Framework, database, and Azure SQL Database. Thank you for your time and have a nice day!

Summer 2015 Self-Learning Project

This article is part of my Self-Learning in this summer. To read the other topics in this project, please click here to visit the project overview page.

Summer Self-Learning Banner

Summer 2015 Self-Learning

Summer Self-Learning
It has been about half a year since I started to learn ASP .NET MVC and Entity Framework (EF). In this period of time, I have learnt about not just MVC and EF, but also Azure PaaS, Google Maps API, web application security, cool jQuery plugins, Visual Studio Online, etc.

In the beginning of May, I started to note down useful things I’d learned in my learning journey. Months of bringing together information in this summer has helped me compile my notes about what I’ve learned in the past 6 months. I have currently completed compiling notes for 17 topics that I’ve learnt in this summer.

I listed down the title of the 17 posts below to give you a quick overview about all the 17 topics.


ASP .NET MVC and Entity Framework


Microsoft Azure

Google APIs

Web Development Tools

Learning After Work

I’m working in Changi Airport. The office working hour is from 8:30am to 6pm. In addition, I am staying quite far from the airport which will take about one hour for me to travel from home to office. Hence, the only time that I can have sufficient time to work on personal projects is weekends.

This summer self-learning project is originally planned to be done by the end of May. Normally, it takes me about one day to finish writing a post. After that, if I find any new materials about the topics, I will then modify the post again. Sometimes, however, I am just too tired and I would not write anything even though it’s weekend. Hence, I end up finishing all the 17 topics three months later.

This summer learning project covers not only what I’ve learnt in my personal projects, but also new skills that I learn in my workplace. I always enjoy having a chat with my colleagues about the new .NET technology, app development, Azure hosting, and other interesting development tools. So yup, these 17 articles combine all the new knowledge I acquire.

I’m also very happy that that I am able to meet developers from both .NET Developers Community Singapore and Azure Community Singapore and share with them what I’ve learnt. That gives me a great opportunity to learn from those experienced .NET developers. =)

Azure Community March Meetup in Microsoft Singapore office.
Azure Community March Meetup in Microsoft Singapore office.

I am not that hardworking to work on personal projects every day. Sometimes, I will visit family and friends. Sometimes, I will travel with friends to overseas. Sometimes, I will play computer games or simply just sleep at home. So ya, this self-learning project takes a longer time to complete. =D

Working on personal projects after work is stressful also. Yup, so here is a music that helps reducing my stress. =)

Journey to ASP .NET MVC 5

When I first worked as web developer after graduation, I used to think what I knew about web development was already enough. However, as I learned more from friends and colleagues, I realized how difficult the field is, even though in we were just dealing with ASP .NET for web development.

New Ideas

Singapore .NET Developers Community meetup
Singapore .NET Developers Community meetup (Photo Credit: .NET Developers Singapore)

I participated in the Singapore .NET Developers Community meetup with my colleagues on 28 January. The theme is about web development. We had the chance to learn about ASP .NET MVC 5, Dependency Injection and how ASP .NET MVC 5 works with Angular JS.

What interested me is the ASP .NET MVC 5 talk given by Nguyen Quy Hy. In work, I was always using ASP .NET Web Forms. When I first started the ASP .NET MVC project in Visual Studio, I was already shocked by new terminologies like Razor, Identity, Scaffold, and all sort of folders, such as Models, Views, Controllers, App_Start, etc. Those are basically not found in my existing Web Forms project.

Working in a startup, there is always more to do and even more to learn, no matter the size of business. In many ways, my job changes frequently. I have to always take time to learn and challenge myself to play with new technology. Hence, learning ASP .NET MVC becomes my new challenge in this year.

I thus decided to write this post to share about what I’ve learned in my ASP .NET MVC 4/5 projects in February.


Let’s start with simple stuff first. The GUI.

It’s nowadays quite common that people want a website which is responsive and mobile friendly. Luckily, there are frameworks to help. A even better news is that Visual Studio web application template by default is using Bootstrap, a framework providing design and theming features.

Previously we were using VS 2008. There was no such thing as bootstrap in our Web Forms application. Hence, I only started playing with Bootstrap when I did my first ASP .NET MVC 4 project in VS 2012.

ASP .NET web server controls can no longer be seen in ASP .NET MVC project. I was once asked about how GridView and paging were going to be handled in ASP .NET MVC without the use of the web server controls. I found some online discussions and articles which gave good answer to the question.

  1. Grid Controls for ASP .NET MVC
  2. Bootwatch: Free themes for Bootstrap including table and paging themes
  3. Paging, Searching, and Sorting in ASP .NET MVC 5
  4. ASP .NET MVC Paging Done Perfectly with @Html.PagedListPager()

12-Column Grid System is another thing I learnt when playing with Bootstrap. The grid system allows us to easily create complex grid layouts for different devices.

Grid System of Bootstrap 3
Grid System of Bootstrap 3

With the help of Bootstrap, even before I do anything, my web application is already responsive and mobile friendly. It’s true that technology is just a tool but with the right tools, we are able to work more efficiently and productively. =)

Native Support of Clean URL: Good News for SEO

My colleague, who was doing SEO, always received requests to do URL Rewrite in our existing Web Forms applications. Whenever there is a new page created, he has to add a new rule to web.config, sometimes just to get rid of the .aspx thingy.

<urlrewritingnet rewriteOnlyVirtualUrls="true" contextItemsPrefix="QueryString" defaultPage="default.aspx" xmlns="">
        <add name="RedirectInDomain" virtualUrl="^http\://(.*)/SomethingFriendly"
            destinationUrl="~/test.aspx" ignoreCase="true" 
            redirectMode="Permanent" rewrite="Domain" />

If there are one thousand pages, then there will be same amount of rules. So in the end, we even need to create separate config file just to keep the rules for URL rewrite.

In ASP .NET MVC 5, with the help of ASP .NET Routing, URLs no need to be mapped to specific web pages. Hence, in MVC web application, we can always see clean URLs which is friendly to not only the web crawler but also sometimes to the users. This is one of the features that I love in ASP .NET MVC.

Identity and Social Network Login

Whenever I visit an online store, I always find it more customer-friendly to accept Facebook or Google login.

Fortunately, ASP .NET Identity is powerful enough to not just accept application-wise user name and password, but also allows the connections from social websites like Facebook, Twitter, and Google+.

I only need to create a Facebook app and then key in the https URL of my website. After that, I put both the application ID and secret key to Startup.Auth.cs. Tada, users can now login to my website with their Facebook credentials.

    appId: "<Facebook app ID here>",
    appSecret: "<Facebook app secret here>");
Localhost HTTPS URL is also accepted! =)
Localhost HTTPS URL is also accepted! =)

Just in case if you also encounter exception saying “Object reference not set to an instance of an object” on the line with AuthenticationManager.GetExternalLoginInfoAsync(), as shown in the following screenshot, please update Microsoft.Owin.Security.Facebook Nuget package.

Facebook Login Exception. Boom!
Facebook Login Exception. Boom!
Update - Microsoft.Owin.Security.Facebook
Update – Microsoft.Owin.Security.Facebook

Entity Framework Code First

Due to the fact that my project is a new one. So, I used Code First to help me create tables in a new database according to my Model definition.

There is also a video on MSDN Data Developer Center website where they give an introduction to Code First development.

I like how easy it is to have all my tables created auto-magically by just defining model using classes. Then after that, I can create new views and controller by adding Scaffold.

Easily create MVC controller and views with Scafolding
Easily create MVC controller and views with Scafolding

Headache with Migrations

In order to have database scheme updated when the model is changed, I have enabled migration by running the Enable-Migrations command.

Ran Enable-Migrations command in the Package Manager Console
Ran Enable-Migrations command in the Package Manager Console

After that, whenever I changed my model classes, I will run Update-Database to have database schema updated as well. However, soon I encountered a problem.

When I was working on an ASP .NET MVC 4 project with VS2012, the Id in the Users table is integer. So, in VS2013, I assumed it to be the same when I created the model classes and updated the database. Unfortunately, nope. The default web application of VS2013 uses GUID for user ID. There is an online tutorial on how to change the primary key of Users back to integer, if you are interested.

Due to the fact that my project is a totally new project, so what I am going to do is just to change my model classes to use GUID as the type of storing user ID in other tables. However, when I ran the Update-Database command, the console prompted me an error message, saying “Operand type clash: int is incompatible with uniqueidentifier”. To quickly get rid of this problem, I deleted my tables (Don’t do this at home. =P) from the database. Then when I ran Update-Database command again, it complaint the table was missing. Finally, I had no choice but deleting the relevant records in __MigrationHistory table before making Update-Database to work again. =P

Yay, successfully updated database schema after deleting migration history.
Yay, successfully updated database schema after deleting migration history.

Yay with Entity Framework

Before using Entity Framework, I played with stored procedure for few years. My colleagues have always been complaining that sometimes the logic was being hidden in stored procedures and thus made the debugging difficult. Also, having logic in stored procedures means that our business logic is actually split up into both C# and SQL. So, sometimes the developers need to spend a few hours debugging the C# code before realizing the store procedure was actually the culprit.

With Entity Framework, I am now able to modify the table structure and logic all in C# code which helps developers to easily find out where goes wrong.

Still, sometimes it is good to group related functions into one well-defined stored procedure so that the system only needs to call to the database once to get all the work done. However, after reading a 400-line store procedure once, I decided that doing this may not be the best option because no one in my team was interested to debug SQL code.

Review a long stored procedure?
Review a long stored procedure?

There are more related topics online regarding Entity Framework vs. Stored Procedures, as listed below. If you are interested, feel free to check them out.

  1. Entity Framework Vs Stored Procedures – Performance Measure
  2. Stored Procedure or Entities?

Using MySQL Instead of Default SQL Server: I Was Having a Hard Time

By default, the data provider of ASP .NET Identity with Entity Framework is set to be MS SQL in VS 2013. However, MS SQL Server is not free. So, I decided to use MySQL instead. Hence, I need to find ways to configure Entity Framework on my project to work with MySQL.

The first tutorial that I started with is a detailed step-by-step guide on ASP .NET website regarding how to use use MySQL Storage with an Entity Framework MySQL Provider. It mainly involves steps on changing the web.config. Some important steps are listed below.

Change database connection string.

<add name="DefaultConnection" connectionString="Server=localhost;Uid=root;Pwd=password;Database=mediablog;" providerName="MySql.Data.MySqlClient" />

Configure Entity Framework to use MySQL.

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=" />

However, if I am not wrong, part of these can be done easily by just including the related MySQL nuget packages. I chose four of them to be installed in my project: MySQL.Data, MySQL.Data.Entity, MySQL,Data.Entities, and MySQL.Web.

Install related NuGet packages to make Entity Framework Code First works with MySQL.
Install related NuGet packages to make Entity Framework Code First works with MySQL.

After changing web.config, I followed the tutorial to introduce two new classes in the project. One is MySqlHistoryContext.cs which will sync the model changes with the database schema using MySQL standard and not MS SQL.

According to an online post, I added extra one line to the OnModelCreating method MySQLHistoryContext.cs. It’s to fix the exception of the famous Error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.

modelBuilder.Properties<String>().Configure(c => c.HasColumnType("longtext"));

However, the Error 0040 didn’t disappear because of this line. I will share later the other steps I took to fix this problem.

The famous Error 0040 encountered when doing migrations for MySQL.
The famous Error 0040 encountered when doing migrations for MySQL.

Another new class is called MySqlConfiguration which is used to make sure the Entity Framework will use MySqlHistoryContext, instead of the default one.

Besides, I also made changes to Configuration.cs. Remember the Error 0040? A discussion thread on Github actually suggested to add the following line to fix it.

SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());

This didn’t fix the Error 0040 on my project too.

In the end, I found a Chinese post which said the following.


The sentence basically says that due to the fact that the migration earlier done in SQL Server and thus some data types are not compatible with MySQL, we need to delete the Migrations folder. So, after I excluded the 201502231459263_InitialCreate.cs file (which was created when I am still using MS SQL for my project) in Migrations folder from the project, the Error 0040 was gone when I did Update-Database. Yay!

So yup, sometimes it’s very, very useful to know more than one language. And yup, I spent half of my holiday to figure out how to make Entity Framework to work with MySQL. =)

Oh well, half day gone just to make MySQL work in my little project.
Oh well, half day gone just to make MySQL work in my little project.

By the way, the Chinese web page mentioned above was already not available. What I shared with you is actually a link to its Google cached copy. I am not sure if the cache is still around when you visit it.

Self Learning ASP .NET MVC on MVA during Chinese New Year

The talks given during the community meetup are good. However, in order to learn more, I also need to get advice from my colleagues who have more experience with ASP .NET MVC.

In addition, during Chinese New Year period, instead of watching the new year shows, I stayed in front of my computer to complete the introductory series of ASP .NET MVC delivered by two Microsoft experts, Christopher Harrison and Jon Galloway. It’s definitely a good starting point for beginners. And yup, the two speakers are very good at explaining the key concepts and they also tell good jokes so you shouldn’t find the course to be boring. =P

Yup, people from Malaysia are watching the live too!
Yup, people from Malaysia are watching the live too!

The End of the Beginning

I am now still a beginner in ASP .NET MVC. I always find that there are many new things to learn in just web development. Actually, it’s very challenging. For example, to get Entity Framework Code First to work with MySQL already takes me half day to figure it out.

Anyway, this is just a post sharing how I get started on ASP .NET MVC. In the future, I will do my best to share with you all more about what I learn in this cool technology. =)