(It Just) Has To Be .Net
Two long-term IT techies, with a penchant for Microsoft's .NET Framework, air views on whatever topics take their fancy.

Howto: Use ASP.NET SQL Server Session State Provider with Entity Framework Code-First with SQL Azure - Part II

This is the second and final part of a two-part post. In Part I, I described some of the issues with combining EF Code-First and ASP.NET SQL Server Session State in the same database and by the end of the article, I'd described how I'd got Entity Framework managing the two tables that support the SQL Server Session State Provider.

All access to the state database is implemented using a significant number of SQL Server Stored Procedures. Stored procs seem to be falling out of favour, as developers put their faith in abstraction layers such as Entity Framework to magically infer the best way to interact with their data. I'm still a big fan of stored procs and despite arguments to the contrary, my experience suggests that I'm better at optimising data access that EF is. An unfortunate consequence is that, in certain areas (and so far, I see EF Code-First as one of them), stored procs are very much under-supported. The Reverse Engineer Code First Power Tool that I described in Part I will happily reverse-engineer your database tables, but gives no consideration to those all-important stored procedures. As a consequence, when EF recreates the database, it will create the state tables, but no stored procs to accompany them. That just won't do.

The approach I took was to get Entity Framework to automatically create the stored procedures when it recreated the database. The magic is in an Initializer class; in this case called SchoolInitializer. Here's the original source of that class, with various lines of database population code removed for brevity:

public class SchoolInitializer : DropCreateDatabaseIfModelChanges<SchoolContext>
{
    protected override void Seed(SchoolContext context)
    {
        var students = new List<Student>
        {
            new Student { FirstMidName = "Carson",   LastName = "Alexander", EnrollmentDate = DateTime.Parse("2005-09-01") },
            new Student { FirstMidName = "Meredith", LastName = "Alonso",    EnrollmentDate = DateTime.Parse("2002-09-01") }
        };
        students.ForEach(s => context.Students.Add(s));
        context.SaveChanges();

        // Other content removed for brevity
    }
}

There are two points of note, here. Firstly, SchoolInitializer is derived from DropCreateDatabaseIfModelChanges<SchoolContext>. This tells EF to automatically recreate the database if the model associated with the context changes in code. That's not a bad approach for Development and Test environments (a bit risky for Production ones, though). The problem at this point is that it relies on the presence of a table in the database called EdmMetadata and as our database only has the ASP State tables in it at the moment, it will fail.

Secondly, the Seed() method is called to populate the database with initial data when it is recreated. The example uses this method to add test data. I shall use the method to solve my stored procedure problem... (I should say, there may well be a better place to do this, but it'll do for now).

I decided to approach the problem by having the Seed() method run a SQL script to generate the stored procs. I have a SQL script, of course, which is the InstallSqlState.SQL script that I used originally to create the ASP State tables. 

There are some changes required, though...

Firstly, I needed to remove the script that created the ASP state tables. EF takes care of those, now.

The next problem got me scratching my head for a few minutes, though. The ASPStateTempSessions table makes use of default values for a couple of its columns:

CREATE TABLE dbo.ASPStateTempSessions (
    SessionId        nvarchar(88)    NOT NULL PRIMARY KEY,
    Created          datetime        NOT NULL DEFAULT GETUTCDATE(),
    Expires          datetime        NOT NULL,
    LockDate         datetime        NOT NULL,
    LockDateLocal    datetime        NOT NULL,
    LockCookie       int             NOT NULL,
    Timeout          int             NOT NULL,
    Locked           bit             NOT NULL,
    SessionItemShort VARBINARY(7000) NULL,
    SessionItemLong  image           NULL,
    Flags            int             NOT NULL DEFAULT 0,
)

Note the use of DEFAULT GETUTCDATE() for the Created column and DEFAULT 0 for Flags. The problem here is that EF is now responsible for creating our tables but EF and default values do not make happy bed fellows. Fortunately, the solution was simple enough. I updated the stored procs to explicitly supply the values instead of relying on the presence of the defaults. There's another point in favour of stored procedures!

As an example, this stored procedure:

CREATE PROCEDURE dbo.TempInsertStateItemLong
    @id         nvarchar(88),
    @itemLong  image,
    @timeout    int
AS    
    DECLARE @now AS datetime
    DECLARE @nowLocal AS datetime
    
    SET @now = GETUTCDATE()
    SET @nowLocal = GETDATE()

    INSERT dbo.ASPStateTempSessions 
        (SessionId, 
         SessionItemLong, 
         Timeout, 
         Expires, 
         Locked, 
         LockDate,
         LockDateLocal,
         LockCookie) 
    VALUES 
        (@id, 
         @itemLong, 
         @timeout, 
         DATEADD(n, @timeout, @now), 
         0, 
         @now,
         @nowLocal,
         1)

    RETURN 0

 became

CREATE PROCEDURE dbo.TempInsertStateItemLong
    @id         nvarchar(88),
    @itemLong  image,
    @timeout    int
AS    
    DECLARE @now AS datetime
    DECLARE @nowLocal AS datetime
    
    SET @now = GETUTCDATE()
    SET @nowLocal = GETDATE()

    INSERT dbo.ASPStateTempSessions 
        (SessionId, 
         SessionItemLong, 
         Timeout, 
         Expires, 
         Locked, 
         LockDate,
         LockDateLocal,
         LockCookie,
         Flags,
         Created) 
    VALUES 
        (@id, 
         @itemLong, 
         @timeout, 
         DATEADD(n, @timeout, @now), 
         0, 
         @now,
         @nowLocal,
         1,
         0,
         @now)

    RETURN 0

 The subtle changes, for the less-than-eagle-eyed are that I now explicitly set the Flags and Created columns. Each insert into ASPStateTempSessions was reviewed and updated where necessary. I've provided the updated script for you to download.

 The next step was to get the Seed() method to run this updated SQL script whenever the database was created. There's a complication to running SQL scripts from code. In our application, we can use

context.Database.ExecuteSqlCommand("string of SQL");

 to execute a SQL instruction against the database. We might consider reading our SQL script into a string, then executing it with ExecuteSqlCommand(), but that won't work. The problem is that our SQL script is not one SQL command, but many. Each is delimited by the

GO

instruction. ExecuteSqlCommand will baulk at commands being provided in this way. There are several methods that we can employ to resolve the problem.

  • Spawn a new process running osql.exe or sqlcmd.exe passing the script file as a parameter
  • Use SQL Server Management Objects to run the script
  • Break the script into commands that can be run using ExecuteSqlCommand()

I hate spawning out to another process and waiting for it to complete. This isn't Unix and running up processes is an expensive and time-consuming operation. It's also, shall we say, a little 'inelegant'.

Ordinarily, I'd use SQL Server Management Objects to run a script. However, in this case, I didn't want to add further dependencies to the application but rather wanted to keep it lighter weight. So in this case, I chose the third option and wrote a routine that would chop up my SQL script into individual statements and execute them one-by-one.

Here it is, in its entirety:

private void RunSQLScript(DbContext context, string scriptFile)
{
    var reader = File.OpenText(scriptFile);

    StringBuilder commandBuilder = new StringBuilder();
    string line;

    while (!reader.EndOfStream)
    {
        line = reader.ReadLine();

        if (line.Trim().ToLower() == "go")
        {
            context.Database.ExecuteSqlCommand(commandBuilder.ToString());
            commandBuilder.Clear();
        }
        else
        {
            commandBuilder.Append(line);
            commandBuilder.Append("\r\n");
        }
    }

    if (commandBuilder.Length > 0)
    {
        context.Database.ExecuteSqlCommand(commandBuilder.ToString());
    }
}

The method assumes that statements are delimited by a "GO" instruction on a line by itself. It builds up the SQL command in a StringBuilder and executes it when it encounters a "GO". The extra call to ExecuteSqlCommand() at the end is there to cater for scripts that don't have a "GO" as the last instruction. As a refinement, I should really get the code to skip blank lines (and maybe comments).

Armed with my new ability to run a SQL script, I updated the Seed() method as follows:

protected override void Seed(SchoolContext context)
{
    var students = new List<Student>
    {
        new Student { FirstMidName = "Carson",   LastName = "Alexander", EnrollmentDate = DateTime.Parse("2005-09-01") },
        new Student { FirstMidName = "Meredith", LastName = "Alonso",    EnrollmentDate = DateTime.Parse("2002-09-01") }
    };
    students.ForEach(s => context.Students.Add(s));
    context.SaveChanges();

    // Code yet again removed for brevity

    RunSQLScript(context, @"C:\ContosoUniversity\ContosoUniversity\ContosoUniversity\DBScripts\InstallSqlStateProcs.SQL");
}

I was feeling somewhat lazy by this point, so specified the full path to the SQL script. In reality, this should be picked up from the relevant location.

That takes care of getting EF to set my database up to support the ASP.NET SQL Server Session State Provider. All that remains is to configure the application to use SQL Server Session State:

<system.web>
    <sessionState mode="SQLServer" sqlConnectionString="Data Source=.\SQLExpress;Initial Catalog=School;Integrated Security=SSPI" cookieless="false" timeout="20" allowCustomSqlDatabase="true" />
</system.web>

 The sample Contoso University application doesn't use Session State at all, so to test, I added a couple of lines of code to the CoursesController...

public ActionResult Create()
{
    Session["SomethingInSession"] = "Hello!";
    PopulateDepartmentsDropDownList();
    return View();
}

[HttpPost]
public ActionResult Create(Course course)
{
    System.Diagnostics.Debug.Assert(Session["SomethingInSession"] == "Hello!");

    // Snip
}

So, that's it. I won't be surprised to find further issues as we move through the lifecycle, but this seems like a good start. I hope you find it of use, too.


Posted Aug 12 2011, 09:22 AM by Steve Morgan
Steve Morgan 2008. All rights reserved.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems