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.

Azure, MVC, MVC 3, MVC4, Technical

Redirecting legacy pages in asp.net

Picture this situation.

An old (legacy) application has landed on your project pile. It is largely built in php, and you intend on re-writing it in asp.net MVC.

You will therefore need to somehow inform any parties that may be trying to access the old urls ending in .php, that the resource they are looking for has moved permanently. You may also wish to do this for SEO reasons.

This is something that cannot be achieved easily through routing; by default, IIS will not pass requests for resources ending in .php to your application. Your routing will therefore never be put to use for resources ending with .php.

The nicest solution I found to this issue is to setup a list of redirects within the system.Webserver section of your web.config file. The following listing below will send a HTTP Response status of 301 (Moved Permanently) for any requests for index.php and for prices.php:

<system.webServer>
 <httpRedirect enabled ="true" httpResponseStatus="Permanent" exactDestination="true">
 <add wildcard="/prices.php" destination="/prices"/>
 <add wildcard="/index.php" destination="/"/>
 </httpRedirect>
</system.webServer>

Index.php will now redirect to /, and prices.php will now redirect to /prices.

This code is currently running in the wild on Azure.

If you are unsure if you need a Permanent redirect or not, have a read of this article from Google.