I’ve been trying to do the MVCMusicStore v3.0b tutorial, and I wanted to use SQLExpress rather than SqlServerCe.4.0.
When trying to do this I ran into a number of problems but I finally figured it out.

This guide assumes you already have a working copy of SQL Server Express and that your user account is a System Administrator for the database.

This is the link to the PDF document for the v3.0b tutorial. MvcMusicStore v3.0

(*all page numbers quoted as on the document, page 45 is actually Page 46 when listed in Acrobat)

Below is what I did and the 2 solutions that I found..

1. As on page 45* I created the AppData Folder.
2. I downloaded the MVC assets and extracted the files from the zip file.
3. Right click on AppData and add an existing file, and add in the MvcMusicStore.mdf.
4. Edit the web.config and added the following connection string:
   [xml]
   <connectionStrings>
   <add name="MusicStoreDb"
   connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MvcMusicStore.mdf;Integrated Security=True;"
   providerName="System.Data.SqlClient" />
   </connectionStrings>
   [/xml]

5. Added the MusicStoreEntities.cs as on page 47.

At this point Debug the site and I was getting errors. First I was getting errors about meta data failures.
I managed to play a bit more and somehow fixed the meta data issues so that it was no longer erroring, but the queries were not returning any data.

Then I noticed the following 2 issues:

1. Write some code like the following
   [csharp]
   MusicStoreEntities db = new MusicStoreEntities();
   string connection = db.Database.Connection.ConnectionString;
   [/csharp]
   Run up in debug and check the value stored in the connection string.
   Note that the database name is related to the application MvcMusicStore, “MvcMusicStore.Models.MusicStoreEntities”? WTF?
2. Put a watch on the db.Database.Genres object and note the sql statement is is going to execute. The table name is “dbo.Genres”.
   The issue? Your datbase doesn’t have a “Genres” table it has a “Genre” table (minus the ‘s’).

Solution 1

So to fix these issues I have done the following:

1. Create a default constructor in the MvcMusicStoreEntities class like follows:
   [csharp]
   public MusicStoreEntities()
   {
   this.Database.Connection.ConnectionString =
   ConfigurationManager.ConnectionStrings["MusicStoreDb"].ConnectionString;
   }
   [/csharp]
   This over-rides the connection string with the one that we have specified in the web.config.
   
2. Pluralise your database.

   If your hosting your database on SQL Server you can use the SQL Management studio, or if you are hosting it in AppData then use the server explorer.
   Right click on each table and select “Open Table Definition”. Press F4 to view the properties window, Edit the “Name” property and change the name of the table and add an ‘s’ to the end, Genre -> Genres, Album -> Albums, Artist -> Artists etc. Save the file to apply the name change.

3. Edit: A bit more digging and I also found this solution to the Pluralised problem, this removes the pluralised table names. Probably a bit easier than the above solution
   In your MusicStoreEntities class add the following
   [csharp]
   using System.Data.Entity.ModelConfiguration.Conventions;

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
   modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
   }
   [/csharp]

Debug your site and it should all work now.

Solution 2

In attempting to work out the above solution I also found a second solution.

1. Ensure the constructor is removed from the MusicStoreEntities class.
2. Edit the web.config file and add the following connection string
   [xml]
   <connectionStrings>
   <add name="MusicStoreDb"
   connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;"
   providerName="System.Data.SqlClient" />
   </connectionStrings>
   [/xml]
3. Add the Sample Data as on page 47 & 48 of the PDF guide.

Debug your site and it should work.

Launch SQL Mangement Studio and refresh the list of databases, you will see you new database [MvcMusicStore.Models.MusicStoreEntities] and look at the tables and they have pluralised names.

Hope this works for you as it did for me. Happy MVC’ing.