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

Asked 2012-02-25T06:33:30.917

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.

Edit Report

2 Answers

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 voi


                        
                        
                            answered 2012-02-26T22:00:34.013


            
            
                
                    
                        
                            
                            
                        
                        7
                        
                            
                            
                        
                        
                    
                    
                        
                        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
                        
                        
                            answered 2012-02-25T22:58:37.283



        
        
            Your Answer