Entity Framework with MySQL - Timeout Expired while Generating Model

Question

I've constructed a database in MySQL and I am attempting to map it out with Entity Framework, but I start running into "GenerateSSDLException"s whenever I try to add more than about 20 tables to the EF context.

An exception of type 'Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine+GenerateSSDLException' occurred while attempting to update from the database. The exception message is: 'An error occurred while executing the command definition. See the inner exception for details.'

Fatal error encountered during command execution.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

There's nothing special about the affected tables, and it's never the same table(s), it's just that after a certain (unspecific) number of tables have been added, the context can no longer be updated without the "Timeout expired" error. Sometimes it's only one table left over, and sometimes it's three; results are pretty unpredictable. Furthermore, the variance in the number of tables which can be added before the error indicates to me that perhaps the problem lies in the size of the query being generated to update the context which includes both the existing table definitions, and also the new tables that are being added to it. Essentially, the SQL query is getting too large and it's failing to execute for some reason.

If I generate the model with EdmGen2 it works without any errors, but the generated EDMX file cannot be updated within Visual Studio without producing the aforementioned exception.

In all likelihood the source of this problem lies in the tool within Visual Studio given that EdmGen2 works fine, but I'm hoping that perhaps others could offer some advice on how to approach this very unique issue, because it seems like I'm not the only person experiencing it.

One suggestion a colleague offered was maintaining two separate EBMX files with some table crossover, but that seems like a pretty ugly fix in my opinion. I suppose this is what I get for trying to use "new technology". :(

Answer 1

I just had headache on this problem in the whole afternoon. However, I found the solution that you can just add a statement in app.config or web.config where your EF desinger connection exists as 'Default Command Timeout=300000;'. The problem's gone.

Answer 2

The above advice is not correct.

Default Command Timeout is the only connection string parameter you need to change. Connect Time just regulates the amount of time to wait to get a connection in the first place; that is not your problem.

Default Command Timeout appears to have no effect in the connection string with Connector/Net 6.3.4. I think this is a bug in Connector/Net, and I filed a bug report with Oracle. EDIT: This bug was acknowledged by MySql developers and has been fixed as of 10/13/2010. Fixes were put in 6.0.8, 6.1.6, 6.2.5, and 6.3.5.

The only way I got around this was to change my ObjectContext object's CommandTimeout property to something other than null. If it's null, it's supposed to use the value in the "underlying provider" per MSDN. If not null, it is the authoritative value for number of seconds before a timeout.

For example:

var context = new CitationData.de_rawEntities();
context.CommandTimeout = 180;

Answer 3

Check out:

http://efvote.wufoo.com/forms/ado-net-entity-framework-vote-of-no-confidence/

Oops, just realised that this link was already posted! sorry

I would also strongly consider "One suggestion a colleague offered was maintaining two separate EBMX files with some table crossover"

It may be ugly, but it should work!