Entity Framework & Direct SQL

Entity Framework is a great time saver.

When I think back to the days when I had to manually construct SQL statements, parse the results, control updates, and of course ensure my actual database design was in sync with what my code expected – well it sends a shiver down my spine!

I remember back in the day I even built myself a little utility that would take my database and spit out boiler plate code for basic CRUD operations.

But then EF was released – and although it was a little bit flakey at first (with people tutting and muttering about NHibernate), it was still a lovely little thing.  These days of course I use it all the time.  In fact as you may have seen in one of my earlier posts I’ve even copied more than a little for an Azure Table Storage version.

And of course couple EF with decent patterns such as the repository pattern and Dependency Injection and you have a rock solid foundation.

But, (there’s always a but) – EF is sometimes a bit slow when compared with issuing SQL commands directly – especially with batch operations.

For this reason the EF context exposes a Database property which in turn exposes a number of options for issue SQL commands directly.

I will show you how to use the two most common ones – SqlQuery and ExecuteSqlCommand

As I like to decouple as much as possible, and because I like my interfaces to only depend on core libraries I’m going to hide away some of the EF specific stuff.

So, first of all I like to have an IRepository<TEntity> interface and a Repository<TEntity> base class, that way each repository gets the basic CRUD methods and anything else I might want;

public interface IRepository where TEntity: class
void Delete(object id);
void Delete(TEntity entity);
System.Linq.IQueryable GetAll();
System.Linq.IQueryable GetAll(object filter);
IPagedResponseViewModel GetPaged(int take, int skip, string orderBy, bool orderByAscending, object filter);
TEntity GetById(object id);
TEntity GetFullObject(object id);
void Insert(TEntity entity);
void Update(TEntity entity);
void Commit();
void Dispose();
// Direct SQL Stuff
int ExecuteSqlCommand(string sql, object[] parameters);
ICollection SqlQuery(string sql, object[] parameters);
int ExecuteSqlCommand(string sql);
ICollection SqlQuery(string sql);
 public abstract class RepositoryBase : IRepository where TEntity : class
        internal DataContext context;
        internal DbSet dbSet;

        public RepositoryBase(DataContext context)
            this.context = context;
            this.dbSet = context.Set();

        public virtual int ExecuteSqlCommand(string sql)
            return context.Database.ExecuteSqlCommand(sql);
        public virtual int ExecuteSqlCommand(string sql, object[] parameters)
            return context.Database.ExecuteSqlCommand(sql, parameters);
        public virtual ICollection SqlQuery(string sql)
            return context.Database.SqlQuery(sql).ToList();
        public virtual ICollection SqlQuery(string sql, object[] parameters)
            return context.Database.SqlQuery(sql, parameters).ToList();
        public virtual void Commit()
        public virtual void Dispose()

As you can see all we are really doing is encapsulating the SqlQuery and ExecuteSqlCommand methods by taking in a string or a string and a list of parameters.

First lets look at ExecuteSQLCommand – as this is very straight forward, we simply pass in our SQL string such as

ExecuteSQLCommand("UPDATE SomeTable SET SomeColumn='Some Value'");

EF issues the command and returns an int indicating the number of affected rows.
If you want you can pass in parameters like this

ExecuteSQLCommand("UPDATE SomeTable SET SomeColumn='Some Value' WHERE ID=@p0", new object[]{3});

Now for SQLQuery. You’ll may notice SQLQuery uses TEntity (if you’re not familiar with Generics we use TEntity to refer to any class we pass in during the instantiation of our repository – therefore anything that refers to TEntity refers to whatever object we want to use – e.g. we would have

public class MyObject{
    int Id { get; set; }
    string someProperty { get; set; }

public class MyRepository: Repository<MyObject>{

so when then instantiate the actual repository

var myrepo = new MyRepository();

We get all the commands from the base Repository class referencing our MyObject class model.

Anyway, if we ignore our repository for now, if we were to just query Database.SQLQuery directly we’d use

Database.SQLQuery<MyObject>("SELECT * FROM MyObjects");

this will result in a list of MyObject – EF actually attempts to convert the results it receives back to the model you pass in.
So all we have done now is automate that because we already know the model from when we instantiated our repository – thus when calling our encapsulated method we just use

SQLQuery("SELECT ALL FROM MyObjects");

Again we can pass in parameters just like with SQLExecuteCommand.

So as you can see this gives you complete flexibility in using EF – as mentioned earlier – if I have to iterate through and update large datasets I sometimes construct my SQL Directly and use these methods instead.

Note : This post was created in response to a user question on my course about ASP.NET Development Techniques.  My Blog viewers can get the course for just $10 by clicking this link 

401.1 error on Windows 2008 when you deploy a Web Site that uses Integrated Authentication

If you deploy an ASP.NET website on Windows 2008 that uses Integrated Authentication AND you are using host headers – then when you try to browse on the server itself you will be given a 401.1 error.  

This often leads users to go looking at the local access rights etc on the server as it’s as if the user just can’t read the directory.

In fact this is caused by a ‘loopback’ check that Windows 2008 (and 2003 + SP1) has in order to prevent reflection attacks on your server.

If you access the site remotely it works fine – however this can be a real pain whilst debugging!  It can also cause problems if (for example) you have a second site on the same server that exposes a service the first site requires and you are also accessing it via a FQDN.

The answer to this issue can be found here:

In brief, the fix is:

  1. Set the

    registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:

    281308 Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
  2. Click Start, click Run, type regedit, and then click OK.
  3. In Registry Editor, locate and then click the following registry key:
  4. Right-click MSV1_0, point to New, and then click Multi-String Value.
  5. Type BackConnectionHostNames, and then press ENTER.
  6. Right-click BackConnectionHostNames, and then click Modify.
  7. In the Value data box, type the host name or the host names for the sites that are on the local computer, and then clickOK.
  8. Quit Registry Editor, and then restart the IISAdmin service.

A quick fix, that can stop hours of scratching your head!

MVC4 / ASP.NET 4.5 on Windows 2008 gives 403.14 – Forbidden

If you manually deploy and ASP.NET MVC application to a windows 2008 Server that has the .NET Framework installed but NOT the actual MVC Tools the server won’t know how to handle and route incoming requests.

There are a number of issues, depending on the version of Visual Studio and patch level you are using.

Older versions of VS simply were not putting all the required DLLs in the bin folder – specifically System.Web.Razor, System.Web.WebPages, System.Web.MVC etc.

So the quick fix (or part of the fix) is the manually add these files to the deployed application.  Later Visual Studio SPs actually addressed this issue.

The next problem is that even with these files the server still didn’t know what to do, and therefore the final piece of the puzzle is a web.config change to tell it what to do.

A lot of posts suggest adding the following under <system.webServer>

<modules runAllManagedModulesForAllRequests=”true” />

This will work, however is not really recommended as it handles ALL requests (e.g. html, css, jpg) as apposed to just the cshtml files.

Therefore a better solution is as follows:


      <remove name=”UrlRoutingModule-4.0″ />

      <add name=”UrlRoutingModule-4.0″ type=”System.Web.Routing.UrlRoutingModule” preCondition=”” />

      <!– any other modules you want to run in MVC e.g. FormsAuthentication, Roles etc. –>



This is generally considered a far better fix.

Now you just have the usual rights issues to deal with….