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.

Answer 2

Got it! Horrible, horrible experience...

In short: EF cannot correctly pluralize entity names that end with "s" (e.g. "status", "campus", etc.)

Here's how I got it and proof.

• I've created and re-created my original set up several times with the same result.
• I also tried renaming my table to things like TransStatus and the like - no luck.
• While I was researching about this I came across the pluralization article by Scott Hanselman where he added pluralization rules for words like sheep and goose. This got me thinking "what if he problem is in the actual name of the entity?"
• I did some searching about the word status in particular and found this report of the problem on Connect. I rushed to try renaming my entity...
• Renaming TransactionStatuses to TransactionStates (while even keeping the columns as StatusID) fixed the issue! I can now get List<TransactionState> statuses = db.TransactionStates.ToList();
• I thought the problem was with the particular word status in the name... But after vocally complaining about this to a friend I was suggested that maybe the problem is with the word ending with "s"... So I decided to check it out.
• I set up another table called Campuses and matching POCO Campus (and updated the edmx). Did the simple test List<Campus> test = db.Campuses.ToList(); and got the now expected

Invalid object name 'dbo.Campus'.

So there you go, the mighty EF cannot handle pluralization of words ending with "s". Hopefully, the next poor bugger hitting the problem will find this question and save him- or herself 3-4 hours of pain and frustration.

Answer 3

Sigh. Same type of an issue with the class and model Photo and Video; it is looking for the table Photoes and Videoes. I hate to just change the table name, but not much of a choice it looks like.

Answer 4

You mention EDMX but I can't tell if you are doing databse first with EDMX or code first and just using EDMX to see what's going on.

If you are using Code First, then you can use a configuration to specify the table name. Data Annotation is [Table("TransactionStatuses")], fluent is modelBuilder.Entity().ToTable("TransactionStatuses"). (I'm typing the annotation and fluent code from memory so double check references. ;) )

If you are using database first, the SSDL should absolutely be aware of the name of the database table, so I'm guessing that you are using code first & the edmx is just for exploration. (???)

hth