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.
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="data source=.\sqlexpress;initial catalog=MyEntities;integrated security=True;multipleactiveresultsets=True;App=EntityFramework""
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.
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.
TransStatus
and the like - no luck.sheep
and goose
. This got me thinking "what if he problem is in the actual name of the entity?"status
in particular and found this report of the problem on Connect. I rushed to try renaming my entity...TransactionStatuses
to TransactionStates
(while even keeping the columns as StatusID
) fixed the issue! I can now get List<TransactionState> statuses = db.TransactionStates.ToList();
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.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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With