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
Advertisements

One thought on “Using EF code first with an mdf database in App_Data

  1. I have mdf file App_Data and EF. I need add new table in mdf file (localdb) and use EF without errors.

    Is it possible ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s