createdatabase in c#

[hcro12]

For some reason, the CreateDatabase call for a new MySql database does not work in my c# code, I've tried several options but either I get an 'Error on executing DbCommand.' with the internal error '{"Table 'test' already exists"}' (on a new database?) or a schema is created but has no tables in it while the context has several tables.

My code is rather simple:

ConfigDatabaseDataContext ctxt = new configDatabaseDataContext(connectionString);
ctxt.CreateDatabase(false, true);

The connectionstring is containing the new database name. This is the way it worked with Linq to SQL in case a MS SQL server was used (without the flags on the CreateDatabase call).


Can someone please give me a hint on how this should be done?

[MariiaI]

In such scenarios it is necessary to create models with the unselected "Preserve schema name in storage" check box in the Model Settings.
Thus, to create a new database, please perform the following steps:
- create LinqConnect model from an existing database (or use Model-First approach) with the unselected 'Preserve schema name in storage" option;
- define a connection string for a new database;
- create a DataContext object:

Code: Select all

ConfigDatabaseDataContext ctxt = new configDatabaseDataContext(connectionString); 
{ Log = Console.Out };

- use the CreateDatabase method to create a new database:

Code: Select all

ctxt.CreateDatabase(false, true);

JIC: use Log property to see the generated SQL query ( http://www.devart.com/linqconnect/docs/?Logging.html)

If you encounter any further issues with this or have any questions, please let us know.

[hcro12]

Thanks for the quick reply, I was able to find the same solution and the database is
created but with errors. I get an error: "{"Identifier name 'FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements' is too long"}"
when creating a table. I've used dbMonitor to see what's happening.
This is part of the output of the SQL statement going wrong:

CREATE TABLE omse_networkelements (
....
CONSTRAINT FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements FOREIGN KEY (lnk_Omse_NetworkElements) REFERENCES omse_networkelements (NetworkElement_Id) ON DELETE NO ACTION,
CONSTRAINT FK_omse_networkelements_Omse_Networkelement_Type FOREIGN KEY (lnk_Omse_NetworkElement_Types) REFERENCES omse_networkelement_types (NetworkElement_Type_Id) ON DELETE NO ACTION
)

and indeed FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements is too long.
I do not know why Linq creates this long name? The second FK is composed without the "_lnk ..". Shouldn't the FK be "FK_omse_networkelements_Omse_Networkelement" iso "FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements"?

If I create this table via MySql workbench, I get:

KEY `Omse_NetworkElement_Omse_NetworkElement` (`lnk_Omse_NetworkElements`),
KEY `Omse_NetworkElement_Type_Omse_NetworkElement` (`lnk_Omse_NetworkElement_Types`), CONSTRAINT `Omse_NetworkElement_Omse_NetworkElement` FOREIGN KEY (`lnk_Omse_NetworkElements`) REFERENCES `omse_networkelements` (`NetworkElement_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Omse_NetworkElement_Type_Omse_NetworkElement` FOREIGN KEY (`lnk_Omse_NetworkElement_Types`) REFERENCES `omse_networkelement_types` (`NetworkElement_Type_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION

[hcro12]

I already added TruncateLongDefaultNames in de config file but no luck ...

Code: Select all

<Devart.Data.MySql.Entity>
    <CodeFirstOptions TruncateLongDefaultNames="true" />
  </Devart.Data.MySql.Entity>

[MariiaI]

I get an error: "{"Identifier name 'FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements' is too long"}"

Please specify the version of LinqConnect you are working with and send us a small test project with the LinqConnect model, so that we are able to reproduce this error and find the solution for you in a shortest time.

already added TruncateLongDefaultNames in de config file but no luck ...

This option is applicable for Entity Framework models, not LinqConnect.

[hcro12]

I've installed Devart dotConnect for MySql Trial V8.3.352.0.
A small test project is sent to Devart support.

Regards,

Hugo

[MariiaI]

Thank you for the test project. We have reproduced the issue. This behaviour is related to the MySQL constraints:
http://stackoverflow.com/questions/4464 ... identifier
We will investigate the possibility to implement changes, which could affect this behaviour, and inform you about the results as soon as any are available.

To avoid the error, you can try changing the names of the navigations properties in your model, for example, by double-clicking your association and setting Name for "End1" and " End2" to a shorter one ( e.g. "End1_name" and " End2_name").

[MariiaI]

The bug related to long default names of identifiers (for example, indexes, primary keys, foreign keys) when using CreateDatabase() method is fixed.
New build of dotConnect for MySQL 8.3.379 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=2&t=31497.