Convert EDMX file from MySQL to dotConnect?

KendallB · Post by **KendallB** » Fri 24 Jun 2011 01:22

Hi Guys,

I have completely mapped our entire MySQL database using the Entity Framework and the MySQL provided Connector/Net. However I have run into some issues with their connector, so I was looking around at other options and discovered dotConnect. It looks to be exactly what I need, and I like the extra tools etc.

The problem of course is that my EDMX file is specific to the MySQL connector, and when I try to just change the provider over to work with dotConnect, it does not work due to the fact that types appear to be mapped differently on the connectors.

Are there any tools to help with converting a database first EDMX file from one database, over to dotConnect? I can't go through the entire process of mapping it all again, as that took a long time due to the fact that our database is all MYISAM tables so I had to manually build all the foreign key relationships (not to mention I renamed all the table columns to make more sense on the C# side, and be PascalCase).

Another option I would consider would be to ditch the EDMX files completely, and go with a Code First approach, if I can find something that would generate the correct Code First classes from an EDMX model. Seems like that should be possible using T4, but I have not found anything indicating that anyone has done or tried to do this?

AndreyR · Post by **AndreyR** » Fri 24 Jun 2011 14:17

Could you please send us the model? We will convert it.
As for the Code First, we have a DbContext template that can be used for the Code First classes generation, but it can be applied to a Devart Entity model only.

KendallB · Post by **KendallB** » Fri 24 Jun 2011 19:40

EDMX file has been sent.

KendallB · Post by **KendallB** » Fri 24 Jun 2011 22:58

BTW, when using the MySQL connector, any datatype in the database that is stored as TINYINT(1) will map to a Boolean type at the conceptual level. But with dotConnect they map to an Sbyte (which it seems EF has a lot of issues with when related to MySQL).

According to this page MySQL BOOLEAN types are supposed to map to Boolean types:

http://www.devart.com/dotconnect/mysql/ ... pping.html

Unfortunately BOOLEAN is a synonym for TINYINT(1) in MySQL, so if you set a column type to BOOLEAN, it will map to TINYINT(1). I think dotConnect should recognize this and also map to a Boolean type at the conceptual level, not an Sbyte?

It seems that using BIT(1) does properly map, but the BIT type has only been around since MySQL 5.0.3:

http://dev.mysql.com/doc/refman/5.0/en/ ... rview.html

I will try changing all my data types that are Boolean to BIT(1), and see if that maps properly in regular MySQL connector code as well as dotConnect.

AndreyR · Post by **AndreyR** » Mon 27 Jun 2011 14:09

As far as I understand form your e-mails, you have converted the model successfully.
We have already implemented the solution for the Tinyint(1) as Boolean issue.
The Devart Entity model will be created with Boolean conceptual properties corresponding to tinyint(1) storage properties if the TinyAsBoolean connection string parameter is set to true in the model connection string in the upcoming build.

KendallB · Post by **KendallB** » Mon 04 Jul 2011 22:16

Is the build with support for TINYINT(1) mapping to boolean types available yet? I would really like to try it, and see how the performance compares to the stock MySQL Connector/net (which is pretty bad).

AndreyR · Post by **AndreyR** » Tue 05 Jul 2011 09:35

We plan to release this build in a week.
I will let you know as soon as it is available.

KendallB · Post by **KendallB** » Sat 09 Jul 2011 00:11

Looks like the new build just got released! I plan to check it out.

KendallB · Post by **KendallB** » Sat 09 Jul 2011 20:01

The new build does not properly support TINYINT(1) as Boolean? There is an option for it now in the connection string, but even if I set that value in the connection string it still imports the items as Sbyte, and not Boolean when it updates the EDMX file from the database?

Any suggestions on what I might be doing wrong?

AndreyR · Post by **AndreyR** » Mon 11 Jul 2011 12:49

We have added the design time support for the Tinyint(1) as Boolean only in Devart Entity models (.edml) at the moment.
I recommend you to use Devart Entity models in your project instead of Microsoft Entity model. We have more control over the metadata in our models, and a more convenient designer - Entity Developer.

KendallB · Post by **KendallB** » Tue 12 Jul 2011 14:46

Yes, I wanted to try tha but there is no support for migrating and EDMX model to the devart linqConnect model and our model is large and complex so manually building the model again is not an option at the moment.