Entity Framework: Executing Arbitrary SQL Commands

If your application’s data model layer uses an ORM, such as the Entity Framework, to read and write data to and from a database, you may find yourself in situations whereby the solution cannot be provided through the normal offerings of the particular ORM.

An example of this with the Entity Framework would be a situation requiring you to create a new VIEW without dropping the rest of the database. You cannot do something like this using the standard capabilities exposed by the Entity Framework.

Fortunately, the framework does allow for the execution of arbitrary SQL commands. Read on for more information.

There are a number of methods that will allow you to execute arbitrary SQL statements.

Some examples of functions provided to allow for arbitrary statement execution are ExecuteStoreCommand and ExecuteStoreQuery. You can use ExecuteStoreQuery when you are expecting a result set to be returned, and ExecuteStoreCommand otherwise.

Executing these methods requires a live data context. The code sample showing the usage of these methods is going to refer to some static properties in a class named “SqlQueries”. I’m going to show you some snippets from this static holder class so you know what queries we’re executing:

    public static string DoesViewExist
    {
      get
      {
        return
          @"SELECT
                COUNT
           FROM
                 INFORMATION_SCHEMA.VIEWS
           WHERE
                 table_name = @viewName";
      }
    }

    public static string CreateRoomView
    {
      get
      {
        return
          // The view particulars aren't necessary here.
          @"CREATE VIEW [dbo].[RoomView]
             .
             .
             .";
      }
    }

Yikes, I hate having raw SQL in code; however, for the purposes of this article, we don’t want to get too complicated here. I have one query that checks if a view exists, and one that creates a specific view. Notice that the view checker query takes a parameter named “@viewName”.

This is just an example. I’m not making any proclamations here that modifying your schema at run-time like this is a proper upgrade strategy for your data sources, but there may be some situations where this just makes sense.

In the following code example, **DataSession** acts as our unit of work, and has a property named “Context”, which has the live **DbContext** for the session. I’m not providing the code for that; substitute any IUnitOfWork pattern implementation you can find on the net here instead, if you will.

The code in the sample checks for the existence of a view named “RoomView” and creates it if it is missing.

    private static bool ViewExists(SqlParameter viewName, ObjectContext context)
    {
      return
        0 != context.ExecuteStoreQuery<int>(SqlQueries.DoesViewExist, viewName).FirstOrDefault();
    }

    private static void VerifySchema()
    {
      // 'Strings' class is a string table/resource file/etc.
      SqlParameter roomViewName = new SqlParameter(Strings.SqlParameterViewName, Strings.SqlRoomViewName);

      using (IDataSession session = new DataSession())
      {
        // This let's us get our ObjectContext.
        IObjectContextAdapter adapter = session.Context;

        if (!ViewExists(roomViewName, adapter.ObjectContext))
          adapter.ObjectContext.ExecuteStoreCommand(SqlQueries.CreateRoomView);
      }
    }

I created a method named “ViewExists” because I’d probably have more than one view to check for, and that makes the whole process a bit cleaner.

But, as you can see, fairly simple.