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.