Entity Framework, Technical

In defence of the RDBMS: Development is not slow. You just need better tooling.

I pride myself on being a real world software developer.

Anything that I publish in this blog is a result of a some real world coding. I don’t just play around with small, unrealistic demoware applications. I write real world applications that get used by lots of users.

With experience comes wisdom, and in software part of that wisdom is knowing when to employ a certain technology. There is almost never a right or wrong answer on what technologies you should be using on a project, as there are plenty of factors to consider. They usually include:

  • Is technology x supposed to be the right tool for this job?
  • Do we have the knowledge to work with and become productive in technology x within the scope of the budget?
  • How mature is technology x?

The above questions will even have varying importance depending on where you are currently working. If you are working at an agency, where budgets must be stuck to in order for the company to make a profit, and long term support must be considered, then the last two points from the above have a greater importance.

If you are working in house for a company that has it’s own product, then the last two points become less important.

Just about the only wrong reason would be something like:

  • Is technology x the latest and greatest thing, getting plenty of buzz on podcasts, blogs and twitter?

This doesn’t mean you should be using it on all projects right now. This just means that technology x is something that you ought to look into and make your own assessment (I’ll let you guess where it might be on the technology hype life cycle). But is this project the right place to make that assessment? Perhaps, but it will certainly increase your project’s risk if you think that it is.

One of the technologies that we are hearing more and more about are NoSQL databases. I won’t go into explanation details here, but you should be able to get a good background from this Wikipedia article.

Whilst I have no issues with NoSQL databases, I do take issue with one of the arguments against RDBMS’s – that development against them is slow. I have now seen several blog posts that have argued that developing with a NoSQL databases makes your development faster, which would imply that developing against a traditional RDBMS is slow. This isn’t true. You just need to improve your tooling.

Here’s a snippet from the mongo db “NoSQL Databases Explained” page:

NoSQL databases are built to allow the insertion of data without a predefined schema. That makes it easy to make significant application changes in real-time, without worrying about service interruptions – which means development is faster, code integration is more reliable, and less database administrator time is needed.

Well I don’t know about you guys, but I haven’t had to call upon the services of a DBA for a long time. And nearly all of my applications are backed by a SQL Server database. Snippets like the above totally miss a key thing out – the tooling.

Looking specifically at .net development, the tooling for database development has advanced massively in the last 6 years or so. And that is largely down to the plethora of ORMs that are available. We now no longer need to spend time writing stored procedures and data access code. We now no longer need to manually put together SQL scripts to handle schema changes (migrations). Those are real world huge development time savers – and they come at a much smaller cost as at the core is a well understood technology (unless your development team is packed with NoSQL experts).

Let’s look specifically at the real world use. Most new applications that I create will be built using Entity Framework code first. This gives me object mapping and schema migration with no difficulty.

It also gives me total control over the migration of my application’s database:

  1. I can ask Entity Framework to generate a script of the migrations that need to be run on the database. This can then be run manually against the application, and the application will even warn you at runtime if the schema is missing a migration
  2. I can have my deployment process migrate the database. The Entity Framework team bundle a console app that can be packaged up and called from another process – migrate.exe
  3. I can have my application migrate itself. That’s right – Entity Framework even allows me to run migrations programatically. It’s not exactly hard either.

My point is this: whilst the migrating of a schema may not be something that you would need to do in a NoSQL database (although you would need to handle these changes further up your application’s stack), making schema changes to an RDBMS’s schema just isn’t as costly or painful as is being made out.

Think again before you dismiss good ol’ RDBMS development as slow – because it hasn’t been slow for me for a long time.

Entity Framework, Technical

Running SQL commands with EF Code First

Before ORMs we used to write SQL code.

Yes – real, “bare metal” SQL. We used it for our CRUD operations, and to perform other larger data manipulation tasks. The database server should be the quickest way to find, remove and join data – provided you know what you are doing.

Then we started using ORMs and stopped writing SQL. The advantages of this are that we should have reduced our development time, needed fewer developers with a good knowledge of SQL programming, and didn’t have to write lengthy and repetitive SQL statements (anyone who has worked on or built a data warehouse will fully agree).

But with this, we sacrificed control over what SQL was run against our database server, leaving it to the ORM to decide what to run.

Looking specifically at Entity Framework’s code first, lets take a look at how you can run into problems with a delete.

So here’s the scenario. I have a task that pulls in data from an external source every hour and needs to be “mirrored” into a table in my application’s database.  Let’s call the table BatchImportData.

As I do not own the external data and have absolutely no control over it and need to mirror the data into my application’s database, I need to do the following to get the task accomplished:

  • Delete all of the data in the BatchImportData table
  • Grab the data from the external resource
  • Insert all of the grabbed data into BatchImportData

Using EF code first, I would normally expect to delete all records from the BatchImportData table with the following code:

foreach (var batchImportDataItem in context.BatchImportData)
    {
         Db.BatchImportData.Remove(batchImportDataItem);
    }

This will work, but it will be slow to execute. At the very least, EF will run a delete statement for every single record that exists in BatchImportData.

If we were writing bare metal SQL, we would write either a single delete statement, or a single truncate statement:

DELETE FROM BatchImportData

--OR

TRUNCATE TABLE BatchImportData

We can still do this through EF Code First simply by opening up our DbContext a bit more. Currently, our DbContext will look something like this:

public class DbContext : System.Data.Entity.DbContext, IDbContext
{
    public IDbSet<BatchImportData> BatchImportData { get; set; }
}

Let’s add a public method in our DbContext that exposes System.Data.Entity.DbContext.Database.ExecuteSqlCommand:

public class DbContext : System.Data.Entity.DbContext, IDbContext
{
    public int ExecuteSqlCommand(string sql)
    {
        return base.Database.ExecuteSqlCommand(sql);
    }

    public IDbSet<BatchImportData> BatchImportData { get; set; }
}

This method will take in a SQL statement and will run it against the database.

You can then call the new ExecuteSqlCommand method that you have just added:

   Db.ExecuteSqlCommand("TRUNCATE TABLE BatchImportData");

We now have a much quicker way of removing all records from a table.

Use with caution!

Do not use this if you are going to build up a SQL statement based on user input. You will make yourself susceptible to an injection attack.

This SQL command is merely a string – it is not strongly typed. If we rename our BatchImportData entity and forget to update this SQL command to reflect this change, we will experience a runtime error.

This opens you up to some potential serious data loss mistakes. The classic being a missing where clause.

Entity Framework, Technical

Creating a composite primary keys in Entity Framework 4.1

There are two main ways of achieving this. Let’s look at an object – Brochure: { ProductId, Year, Month, ProductName}. We want:

  • ProductId
  • Year
  • Month

To make up the primary key.

Method 1 – Data annotations

In your entity class, simply decorate any properties that you want to make up your key with the attribute “Key”:

public class Brochure
{
    [Key, Column(Order = 0)]
    public int ProductId { get; set; }

    [Key, Column(Order = 1)]
    public int Year { get; set; }

    [Key, Column(Order = 3)]
    public int Month { get; set; }

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

Method 2 – DbMigration class

NOTE: You shouldn’t need to use this method if you are using full entity framework code first. However, some projects only use entity framework to handle migrations – so this might be of use to you:


public partial class BrochureTable : DbMigration
{
 public override void Up()
 {
 CreateTable("Brochures", c => new
 {
 ProductId = c.Int(nullable: false),
 Year = c.Int(nullable: false),
 Month = c.Int(nullable: false),
 ProductName = c.String(maxLength: 60)
 })
 .PrimaryKey(bu => new {bu.ProductId, bu.Year, bu.Month});
 }
}

Enjoy!

Entity Framework, Technical

Running Entity Framework code first migrations programatically

Entity Framework code first migrations can easily be run programmatically. You can specify a specific migration, or you can just update to the latest migration.

To rollback all migrations (calls the “Down” method on each migration):

var configuration = new Configuration();
 var migrator = new DbMigrator(configuration);
 //Rollback
 migrator.Update("0");

To rollback or update to a specific migration:

var configuration = new Configuration();
var migrator = new DbMigrator(configuration);
//Update / rollback to "MigrationName"
migrator.Update("MigrationName");

To update to the latest migration:

var configuration = new Configuration();
 var migrator = new DbMigrator(configuration);

//Update database to latest migration
 migrator.Update();

Azure, Entity Framework, SQL Server

Drop all tables in a SQL Server database (Azure Friendly!)

Most online solutions to this problem will point you in the direction of the undocumented stored procedure, “sp_MSforeachtable“. This stored procedure takes in a string of SQL as a parameter, and will execute this string for each non system table in your database.

Here’s the problem: undocumented stored procedures suck. Their usage has been determined by reverse engineering. They are not officially supported. Their implementation and usage could change with updates and new versions, or could disappear totally.

Here’s something that illustrates my point: “sp_MSforeachtable” does not exist in SQL Azure. So, if your development environment is SQL Server 2008 but your production environment is SQL azure and you are using “sp_MSforeachtable“, you will get problems when you go live, which sucks.

Below is a simple, Azure and Entity Framework friendly bit of SQL that will drop all tables in your database, with the exception of your entity framework migration history table – “__MigrationHistory“:


while(exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME != '__MigrationHistory'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + ']')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME != '__MigrationHistory'
exec (@sql)
 PRINT @sql
end

 

If you need to drop your table constraints first, the following code will allow you to do so:


while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 exec (@sql)
 PRINT @sql
end

Credit to SQL Server Central for the above snippet.

Entity Framework, Technical

Seed data from SQL scripts using Entity Framework Migrations (EF 4.3 +)

Normally you would add seed data using native C#.

You can also execute arbitrary SQL statements. To do so, in your Seed method (which can be overriden from your Migration folder in your Configuration class), simply read the contents of any SQL files you want to execute, and tell the context to run them:

</pre>
<pre>internal sealed class Configuration : DbMigrationsConfiguration&lt;MyDbContext>
 {

protected override void Seed(MyDbContext context)
 {
 var baseDir = AppDomain.CurrentDomain.BaseDirectory.Replace("\\bin", string.Empty) + "\\Migrations";

context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir + "\\DataClear.sql"));
 context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir + "\\Table1Insertssql"));
 context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir + "\\Table2Inserts.sql"));
 }
 }</pre>
<pre>

The above code will execute DataClear.sql, Table1Inserts.sql and Table2Inserts.sql, which are all in the root of my migrations folder.

Don’t forget that you can generate your insert statements using management studio, or by using the Static Data Generator tool.

 

Entity Framework, MVC 3, Technical

Entity Framework – Plural and Singular Table names

By default, the Entity Framework will assume that all of the names of your tables in your database are either pluralised, or in the case of code first, you would like them to be pluralised when created.

E.g. you have a table called “Product” and not “Products”, or you want your table to be called “Product” and not “Products”

This is the problem that I had. My MVC application consisted of one web page that just dumped out the contents of the “Product” table onto the page. When I browsed to the page, I got an “Invalid object name ‘dbo.Products’.” yellow screen of death runtime error.

The Solutions

1. Rename the table to “Products”. I didn’t want to do this as I’m from the school of singular table names. I was also curious about situations where the tables couldn’t be renamed.

2. Make use of Entity Framework’s fantastic Conventions, that allow you to specify how you have or want your database to be setup.

To tell Entity Framework not to pluralise database table names, simply add the following code into your DbContext class:

public class EfDbContext : DbContext
 {
  public DbSet&lt;Product&gt; Products { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Conventions.Remove&lt;PluralizingTableNameConvention&gt;();
  }
 }

This code will remove the Pluralising convention that is by default attached to all model builders. You will then be able to access database tables with Singular names.

Links

Table Naming Dilemma: Singular vs. Plural Names (StackOverflow)

PluralizingTableNameConvention Class (MSDN)