Entity Framework (4.3) looking for singular name instead of plural (when entity name ends with "s")

Question

Here's my situation: I have been working on an ASP.NET MVC 3 application for a while. It has a database (built out of a db project; I'm going db-first) for which I have an edmx model and then a set of POCOs. My entities have plural names in the database and POCOs have singular names. Everything maps nicely without a problem.

Or used to until I added a new table (called TransactionStatuses). Now all of the old entities still work but the new one does not. When I try to eagerly load it together with a related entity:

var transactions = (from t in db.Transactions.Include(s => s.TransactionStatus) //TransactionStatus - navigation property in Transactions to TransactionStatuses
                    where t.CustomerID == CustomerID
                    select t).ToList();

I get

Invalid object name 'dbo.TransactionStatus'.

I even did a simpler test:

List<TransactionStatus> statuses = db.TransactionStatuses.ToList();

= same result.

I have updated (and even re-created) edmx from the db and have gone through it back and forth trying to figure out what is different about the mapping for dbo.TransactionStatus*es* which trips the whole thing up.

If somebody can point me in the direction of a fix it'd be wonderful.

P.S. Turning off pluralisation is not an option, thanks.

Update: I figured it out - my answer below.

Answer 1

This is probably happening because even though the intention was to use the Database First flow, in actual fact the application is using Code First to do the mapping. Let me explain a bit more because this can be confusing. :-)

When using Database First with the EF Designer and the DbContext templates in Visual Studio three very important things happen. First, the new Entity Data Model wizard adds a connection string to your app containing details of the Database First model (i.e. the EDMX) so that when the application is run it can find this model. The connection string will look something like this:

<connectionStrings>
    <add name="MyEntities"
    connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\sqlexpress;initial catalog=MyEntities;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;"
    providerName="System.Data.EntityClient" />
</connectionStrings>

Second, the generated context class makes a call to the base DbContext constructor specifying the name of this connection string:

public MyEntities()
: base("name=MyEntities")
{
}

This tells DbContext to find and use the "MyEntities" connection string in the config. Using "name=" means that DbContext will throw if it doesn't find the connection string--it won't just go ahead and create a connection by convention.

If you want to use Database First, then you must use a connection string like the one that is generated. Specifically, it must contain the model data (the csdl, msl, ssdl from the EDMX) and you must make sure that DbContext finds it. Be very careful when changing the call to the base constructor.

The third thing that happens is that OnModelCreating is overridden in the generated context and made to throw:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    throw new UnintentionalCodeFirstException();
}

This is done because OnModelCreating is only ever called when using Code First. This is because OnModelCreating is all about creating the model, but when you are using Database First the model already exists--there is nothing to create at runtime. So if OnModelCreating is called then it is probably because you started using Code First without meaning to, usually because of a change to the connection string or the call to the base constructor.

Now, it might be that you want to use Code First to map to an existing database. This is a great pattern and fully supported (see http://blogs.msdn.com/b/adonet/archive/2011/03/07/when-is-code-first-not-code-first.aspx) but you will need to make sure mappings are setup appropriately for this to work. If the mappings are not setup correctly then you will get exceptions like the one in this question.