DbSets appear empty in MVC5 EF Code First Project but DB appears populated in Server Explorer

Question

There's a very strange thing happening when I debug in Visual Studio.

I have a local database defined thus in Web.config...

<connectionStrings>
  <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\cms_test_data;Initial Catalog=cms_test_data;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

When I debug the project the DbSet objects are empty. For example Pages (see below) returns a Count of 0 at a breakpoint in a Page controller action.

But when I examine the tables in Server Explorer they are populated with the test data.

Here is how my DB Context is defined...

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public DbSet<Page> Pages { get; set; }
    ...

public ApplicationDbContext()
                : base("DefaultConnection", throwIfV1Schema: false)
    {
    }
    ...
}

Even more strangely, when I use an external database this all works.

Any ideas? Thanks.

are you sure you are examining the tables in |DataDirectory|\cms_test_data? — artm, Feb 27 '17 at 00:12
@artm In Solution Explorer I'm double-clicking the cms_test_data.mdf file in the App_Data folder. — clayRay, Feb 27 '17 at 00:25
... but I've noticed there is also a file called "cms_test_data" (with no extension) in this folder. Any idea what this contains? — clayRay, Feb 27 '17 at 00:27

score 0 · Accepted Answer · edited May 23 '17 at 11:53

It appears my connectionString contained AttachDbFilename=|DataDirectory|\cms_test_data instead of AttachDbFilename=|DataDirectory|\cms_test_data.mdf and indeed there was a file in the App_Data folder called cms_test_data (no file extension).

It appears the SQL Server doesn't care whether the database file has an extension, let alone a ".mdf" extension. See What is an MDF file?.

So when cmd_test_data was put into the connection string instead of cmd_test_data.mdf, a blank database with this name was created, alongside the existing .mdf file.

DbSets appear empty in MVC5 EF Code First Project but DB appears populated in Server Explorer

1 Answers1