Using EF code first with an mdf database in App_Data

I’ve been playing around some more with EF code first. Today’s task was to try to get it to work with a .mdf file located in the App_Data folder of the web project.

It wasn’t much fun.

I hijacked the existing aspnetdb.mdf and tried to get that to work with EF Code First. Short answer: don’t bother, for these reasons:

  1. I couldn’t get EF code first to create my Category table
  2. So then I’d created the table manually myself in SQL Server Express, but EF still wouldn’t recognise it – “Invalid object name dbo.Category”
  3. So then I decided to start with a new .mdf file and yay EF code first created my Category table
  4. run aspnet_regsql.exe on it to get the ASP.NET Membership tables in there – fail
  5. Visual Studio has a lock on the mdf file – delete it from VS’s Server Explorer, run aspnet_regsql, yay
  6. Open DB in SQL Server Management studio to see what my database looks like – fail, locks on file

I’m not sure how to debug EF code first, I mean if it doesn’t work (i.e. your table isn’t created) you have no idea why.

So I guess I got it working in the end, but all the problems with the files being locked made me realise it’s not worth the hassle so I started again with a normal database inside SQL Express.

  1. Add your connection string:
    <add name="SiteDB" 
         connectionString="data source=.\SQLEXPRESS;Initial Catalog=Site;Integrated Security=SSPI;"
         providerName="System.Data.SqlClient" />

I recommend explicitly setting a connection string otherwise your database will have a big silly namespaced name. You don’t need to create the database though, EF will do that much for you.

  1. Run your app and hit the Categories page so that EF will create the tables
  2. Run aspnet_regsql.exe on it to add the membership tables
  3. Change your membership connection string to point to the same DB – or tell the AspNetSqlMembership provider to use that connection string blah blah

Entity Framework Code-First CTP5 walkthrough

A couple of months ago I watched some of videos from Microsoft PDC 2010 at http://player.microsoftpdc.com/

One of the interesting presentations was Jeff Derstadt and Tim Laverty’s “Code First with Entity Framework”, viewable here (or direct download here). In the demo, Jeff and Tim very quickly create a basic twitter clone using ASP.NET MVC 2 and EF Code-First CTP4.

For fun I decided to watch the talk and follow along in Visual Studio. This would give me a chance to play with some Microsoft bleeding edge pre-release stuff: ASP.NET MVC 3 (RC2) and Razor, NuGet, and EF Code-First CTP5 (CTP4 was the “Magic Unicorn Edition“).

If you are as lame as me, here’s some tips to help you do a similar walkthrough.

Getting started

You’ll need Visual Studio 2010, and to download ASP.NET MVC 3 (currently RC2) which also installs NuGet.

Once you’ve created your MVC application, you need to add EF Code-First by using NuGet. NuGet’s only been around for a couple of months and already it’s a gotten disorganised and is hard to find the right package. In the demo, Tim adds a reference to “EFCTP4” and indeed that is still present in NuGet. But if you dig around a bit and search for “EFCodeFirst”, well that appears to be EFCTP5. The package you should install is now called “EntityFramework”.

Changes

The first issue I came across was getting EF to talk to SQL Server, as mentioned in my previous post.

A few other things I’ve found that must have changed between CTP4 and CTP5:

1. I don’t think the [StoreIgnore] attribute exists any more. I couldn’t find it, and in my experiments it didn’t seem to be necessary to tell EF to ignore the calculated “TweetActivity” field. I did discover the [NotMapped] attribute which you can use if you want to tell EF not to save a property. I think it’s the same as [StoreIgnore].

2. When it comes time to setup the DB initialiser for populating the DB with static data, Tim does something like this:

public class TweetInitializer : AlwaysRecreateDatabase<TweetContext>

However, AlwaysRecreateDatabase doesn’t exist any more. I think:

public class TweetInitializer : DropCreateDatabaseIfModelChanges<TweetContext>

might be its replacement.

3. I had a strange issue when I tried to create the oData feed. Visual Studio couldn’t find the System.Data.Objects namespace. A quick fix was to add an empty “ADO.NET Entity Data Model” to my project, and then delete it.

4. When you view your oData feed in your browser, by default it appears all purty:

feed

In order to see the raw XML you need to disable IE’s “feed reading view”.

Tools –> Internet Options –> Content tab –> Feeds and web slices –> Settings –> Uncheck “Turn on feed reading view”.

xml

System.Data.ProviderIncompatibleException was unhandled by user code

Message=The provider did not return a ProviderManifestToken string.

Or, how to run EF code-first without SQL Express.

I got the above exception being thrown when I started playing around with Entity Framework’s Code-First CTP5. The inner exception revealed the true problem:

ysod

The problem is that by default EF Code-First tries to create your database on a SQL Express instance.

I don’t run SQL Express, I run SQL Server Developer edition. The solution is to add a connection string for the code-first database to your web.config (even though this database doesn’t yet exist):


<connectionStrings>
 <add name="TweetContext" connectionString="Data Source=.; Initial Catalog=Tweet; Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
 </connectionStrings>

Note that the name (TweetContext) must match the name of your class which derives from DbContext, i.e.


public class TweetContext : DbContext

And you have to specify the DB name, i.e Initial Catalog=Tweet, but that database should not already exist, as EF code-first will create it for you.