(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 I

This article was prompted in response to a question on my favourite programmers' site, StackOverflow.com.

As part of my work on an accelerated engineering process for Windows Azure applications, I've been implementing SQL Server (and SQL Azure) databases using a database-first approach. However, it's hoped that moving to EF Code-First can further improve the engineering process and the latest code generators that my team and I are working with implement business databases this way.

Another design issue to deal with is that of session state. While there are Azure providers that use Azure Table Storage, I've had more success with the standard SQL Server Session State Provider, as I've found the Table Storage Provider to be somewhat unreliable in the development fabric.

There's an argument that using Table Storage for session state is more cost-effective that using SQL Server Session State with Windows Azure, as you have to pay for SQL Azure databases. That argument is weakened somewhat if you're using SQL Azure, anyway, but it still holds true if you have to pay for a separate SQL Azure database, just to hold the state.

The ideal, then, in my case, is to use the main application SQL Azure database for both business data and session state.

Using our previous code generators, with a database-first implementation, that was straightforward enough. Setting up the database for session state involves simply running a SQL script. The standard InstallSqlState.SQL script doesn't work, as it fails against some of the limitations of SQL Azure. I found a modified script, over at MSDN Forums. That was a good starting point, but it needs some modifications. Not least, I removed references to the [AspState] database and the creation of users. You can download a copy of my modified script here. It turns out that more changes would be required, but more later.

The problem is that if you simply run this script against your EF Code-First database, it will be lost if EF subsequently regenerates the database. And it turns out, it's not happy if the database contains tables that it doesn't know about.

The trick, then, is to have EF manage the tables along with those required to contain the business data.

Because I didn't want to risk breaking an existing application, I headed over to MSDN, downloaded the Contoso University ASP.NET MVC Application Using Entity Framework Code First sample and used this as the basis for my investigations.

I created a new database under SQL Express (it's easy enough to migrate it to SQL Azure using the SQL Azure Migration Wizard later). Then, using SQL Server Management Studio, I ran the modified InstallSqlState.SQL to set up the tables for the SQL Server Session State Provider. It creates numerous stored procedures, too, but these ultimately get thrown away.

Next, I downloaded and installed the EF Power Tools CTP from within Visual Studio 2010 (Tools -> Extension Manager and search the online gallery for Entity Framework Power Tools).

Visual Studio 2010 Extension Manager

After restarting Visual Studio, one the installation was complete, my solution was reloaded.

I right-clicked on the ContosoUniversity project and selected Entity Framework -> Reverse Engineer Code First.

Reverse Engineer Code First context menu item

Then, you simply connect to the new database and let the Power Tool do its stuff:

Connecting to the empty database

The import process creates a new context class (called SchoolContext.cs in my case, because the database was called School), an Entities folder with classes for the ASP State tables and a Mapping folder containing mapping classes). It also adds a connection string for the database to web.config.

I then set to work pulling the wizard's work apart an integrating into the application. First step was to add the new tables to the SchoolContext class. Because the wizard generates its context class in the root of the project, it was simple enough to copy-and-paste between the two before deleting the generated class.

     public class SchoolContext : DbContext
        public DbSet<ASPStateTempApplication> ASPStateTempApplications { get; set; }
        public DbSet<ASPStateTempSession> ASPStateTempSessions { get; set; }
        public DbSet<Course> Courses { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Enrollment> Enrollments { get; set; }
        public DbSet<Instructor> Instructors { get; set; }
        public DbSet<Student> Students { get; set; }
        public DbSet<Person> People { get; set; }
        public DbSet<OfficeAssignment> OfficeAssignments { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
            modelBuilder.Configurations.Add(new ASPStateTempApplicationMap());
            modelBuilder.Configurations.Add(new ASPStateTempSessionMap());
                .HasOptional(p => p.OfficeAssignment).WithRequired(p => p.Instructor);
                .HasMany(c => c.Instructors).WithMany(i => i.Courses)
                .Map(t => t.MapLeftKey("CourseID")
                .HasOptional(x => x.Administrator);

Next, some modifications were required to the ASPStateTempSessionMap class, to ensure that the table generated by EF matched that created by the script (as far as we can, at least).

public class ASPStateTempSessionMap : EntityTypeConfiguration<ASPStateTempSession>
    public ASPStateTempSessionMap()
        // Original content removed for brevity...
        this.Property(t => t.SessionItemLong).HasColumnName("SessionItemLong").HasColumnType("image");
        // ...

Here, I've added .HasColumnType("image"), because by default, EF creates the table with a varbinary column, instead.

Then, a temporary change to the SchoolInitializer class, to force EF to recreate the database...

I changed

public class SchoolInitializer : DropCreateDatabaseIfModelChanges<SchoolContext>


public class SchoolInitializer : DropCreateDatabaseAlways<SchoolContext>

But once I've finished, I'll revert that change.

Running the application now causes EF to create the database afresh, including the requisite ASPStateTempSessions and ASPStateTempApplications tables. But before I could use the SQL Server Session State Provider, I had to address the problem of the numerous missing stored procedures.

I address that issue in Part II of this article.


Posted Aug 11 2011, 10:52 PM by Steve Morgan


Steve Morgan - Technology Spot wrote Howto: Use ASP.NET SQL Server Session State Provider with Entity Framework Code-First with SQL Azure - Part II
on Fri, Aug 12 2011 10:36

This is the second and final part of a two-part post. In Part I , I described some of the issues with

bank student loans wrote bank student loans
on Tue, Oct 7 2014 2:27

Howto: Use ASP.NET SQL Server Session State Provider with Entity Framework Code-First with SQL Azure - Part I - Steve Morgan - Technology Spot - (It Just) Has To Be .Net

Steve Morgan 2008. All rights reserved.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems