3

I'm using EF4.1 in code-first style to persist POCO objects for a small ASP.NET MVC 3-based webapp in an SQL Azure DB 'MyAppsDB'. The tables are automatically created in MyAppsDB when they don't exist. I'd like to host the app on SQL Azure, but I don't want to pay $10/mo for an extra 1GB DB to store 50MB of session state, and using Cache would be even more expensive.

I'd like to store session state in MyAppDB along with my app's models. Is there a way to do this? Can I have the Session table auto-created if it doesn't exist, same as EF code-first models?

I've tried modifying my web.config as follows:

<sessionState mode="Custom" customProvider="DefaultSessionProvider">
  <providers>
    <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" 
      connectionStringName="MyAppDBContext" applicationName="/" />
  </providers>
</sessionState>

But it complains the session table doesn't exist. If I create the session table, EF4.1 complains that the DB metadata doesn't match its expectations.

Seth
  • 2,712
  • 3
  • 25
  • 41

1 Answers1

2

It's certainly possible to use your existing SQL Azure database for session state. I'm doing exactly that (and for exactly the same reason). The standard scripts don't work, as they're not compatible with SQL Azure. There are some updated scripts somewhere (I can't remember where, but I'll have a Google around) that I used to set up the DB.

It's not code-first, though. You may be able to reverse-engineer the schema, but there are a number of stored procs involved.

Note that you'll also want to think about tidying up expired sessions. There's a stored procedure that needs running periodically (DeleteExpiredSessions, I think) and the typical recommendation is to run it from a Worker Role. But you won't want to deploy a Worker Role for the sole purpose of running a stored procedure periodically!

Edit: Found the script on this page. Note that there's an issue with the script posted in the main article, but there's a modified script further down the page in a comment from Peter McEvoy.

Some recommend use of an Azure Table Storage session state provider in preference and if I wasn't using SQL Azure already, I'd be inclined to agree. But I've had problems with the Azure provider (particular in the dev environment) but have had no problems at all using SQL Server Session State once the updated scripts are deployed.

Edit 2: I've made further progress and I'll write it up for my blog and post a link here.

Highlights, though are:

  • Run the InstallSqlState.SQL script (the modified one for Azure) against an empty database
  • Use EF Power Tools to reverse-engineer the ASP State Tables into EF Code-First
  • Integrate the reverse-engineered classes into your model and context
  • Modify the mapping for the ASPStateTempSessions table to use an Image column type
  • Create a new SQL Script from InstallSqlState.SQL that just creates the stored procs
  • Modify the stored procs to always specify default values
  • Add some code to run the SQL Script in the Initializer Seed() method

I think that's about it, but I'll give a more complete description on my blog when I've written it up.

Edit 3:

Part I of my write-up is now on my blog. Part II coming soon (but time for bed, now).

Edit 4:

Part II is also written, now.

Steve Morgan
  • 12,978
  • 2
  • 40
  • 49
  • I think the main problem with using SQL Azure for session is that there is no cleanup. The on-premises version occasionally runs a job to clean out old session. You would have to do that manually today or automate your role to do so. – dunnry Aug 10 '11 at 18:41
  • If that's the main problem, I reckon we're pretty well off! ;-) It could be slipped-in to a web role to run it periodically without requiring a worker role. Or, indeed, you could use an on-premise scheduler to do it - especially now that inbound data isn't charged. Just a wrinkle that needs to be dealt with. We found lots of issues with roles not starting properly using the Table Storage Provider. – Steve Morgan Aug 10 '11 at 18:45
  • When I create the Session table manually, my EF CodeFirst models won't load. They complain that the database doesn't match the description in EdmMetadata. Did you run into this and work around it somehow? – Seth Aug 10 '11 at 20:00
  • Maybe the EF Power Tools would help http://blogs.msdn.com/b/adonet/archive/2011/05/18/ef-power-tools-ctp1-released.aspx?CommentPosted=true#commentmessage as it claims to convert database-first to code-first. In theory, once you've got a code-first representation of the tables, it should stop EF complaining. There might still be issues with the stored procs, though. – Steve Morgan Aug 10 '11 at 20:23
  • haven't gotten this working, but at least I know its possible. Seems the real conflict is between the "Auto DB Table Management" aspect of EF and having an extra Table in the store. – Seth Aug 11 '11 at 14:31
  • @Seth, if you work it out, can you let me know? We're intending to use Code-First from now on and are about to have exactly the same problem. Likewise, if I make any progress, I'll update my answer. – Steve Morgan Aug 11 '11 at 14:58
  • Yup, will let you know. I'm checking if there's a way to specify a that a table inside the DB is outside EF's control... seems like an issue that would come up in lots of other non-web-appy situations. – Seth Aug 11 '11 at 15:30
  • @Seth, I think I've cracked it - it's gonna take some writing up, though, so maybe we can discuss it in chat or via e-mail? – Steve Morgan Aug 11 '11 at 17:33
  • @Seth let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2380/discussion-between-steve-morgan-and-seth) – Steve Morgan Aug 11 '11 at 17:33