Update (12/08/10): This week the Entity Framework team released CTP5 of the code-first library.  I was hopeful that this new version would fix my issues below with creating a SQL Express database in an arbitrary directory or the ASP.NET App_Data directory.  Updated my code to use this latest CTP5 binary – no difference.

Just a short post this time about the cool new “Code-First development” option that the MS data team made available a few months ago.  There are already some great walkthroughs (ScottGu, Scott Hanselman) about this topic, so I’m not going to repeat it all here.  Below are just some thoughts on where I find it useful, and a couple issues I’ve run into.

I really like the concept:  instead of building the database first, just write the code (more precisely, write the model object classes) and at runtime, the code-first library will interpret properties on the classes, and use some conventions to come up with how those objects should be stored in the database.  You don’t have to add attributes to your classes or properties – the framework inspects everything at runtime. When the code runs the first time and the database doesn’t exist, the code-first library creates the database for you (from then on it tracks a “hash” of the structure of your objects and reacts when there has been a change since the database was created).  There are configuration options (set via attributes or a code-based API) for what to do if the database already exists, ways to fine-tune relationships between objects, etc.

Of course when we’re writing a production quality, large scale application, we will still create the database schema first, probably have a bunch of stored procs to go along with them, and then use Entity Framework to hit that database.  But…there are a class of applications that this code-first mentality is great for: quick and dirty demo apps and local structured data storage to name a couple.  In the past couple years, I’ve gotten into a pattern of writing “xml data providers” for various applications, that just read & write to a local XML file (usually for mock/demo data, offline capabilities, etc).  We always try to start with built-in serialization of our model objects, but inevitably the format isn’t good, or we need some customized format, so we end up writing our own XML parsing code.  This code-first addition to Entity Framework seems perfect for these situations – no data access code to write!

Since code-first supports SQL CE which does not require any SQL install, it ends up being just a .SDF data file local to your application – just like my old XML files that I used to parse!

Update (12/08/10): one of my co-workers pointed out that you must be using .NET 4 to use SQL CE without any SQL installation.  On a recent project, we used SQL CE, but in .NET 3.5 and still needed to install it before we could use it.

I’ve done the walkthroughs above and have it (mostly) working, at least with the SqlCe and Sql clients.

ERRORS / BUGS (?):

The main problem I’m having is creating the database on first run with SQL Express.  It works fine with SQL CE and Full SQL.  With SQL Express I have varying outcomes – very rarely, it WILL successfully create the database the first time, and then most of the time it will fail.  IF the database is already created, the code-first libraries work great.  When failing to create the database, I get an exception:

The underlying provider failed on Open.
Cannot open database "FootballDB" requested by the login. The login failed.
Login failed for user ‘{myAdminUsernameHere}’.

I’ve searched around, even posted questions to the forums, and emailed some MS guys.  No answer yet…

I’ll keep plugging away at it, or maybe one of you can point me in the right direction!  I haven’t tracked down exactly when they plan to release these bits – for now it’s just a CTP.  Hopefully this fires you up to go check it out!

Connection Strings

For reference, here are the connection strings I’m using for various connectivity methods:

SQL CE provider

<add name="FootballDB"        
    connectionString="Data Source=FootballDB.sdf"        
    providerName="System.Data.SqlServerCe.4.0"    
    />

SQL Express Provider (default SQL data directory)

<add name="FootballDB"        
    connectionString="
        Data Source=.\SQLEXPRESS;Initial Catalog=FootballDB;        
        Integrated Security=True;MultipleActiveResultSets=True;        
        User Instance=True;"        
    providerName="System.Data.SqlClient"    />

SQL Express Provider

(in the “App_Data” directory of an ASP.NET application)

<add name="FootballDB"        
  connectionString="Data Source=.\SQLEXPRESS;
      AttachDbFileName=|DataDirectory|FootballDB.mdf;        
      Initial Catalog=FootballDB;Integrated Security=True;        
      MultipleActiveResultSets=True;User Instance=True;"            
  providerName="System.Data.SqlClient"/>

Full SQL Server

<add name="FootballDB"        
  connectionString="Data Source=(local);Initial Catalog=FootballDB;        
    Integrated Security=True;"        
  providerName="System.Data.SqlClient"/>