Wrong type mappings from SQLServer to Oracle

[gabera]

Hi,

I'm trying to migrate some SQLServer databases to Oracle using Entity Developer and dotConnect for Oracle in VS2017. While some of the databases was successfully converted, others I get errors. One of these errors come from SQLServer type "varchar(max)", which is converted to .NET as "String - maxlength 2147483647", but the output script still shows "varchar(max)", so the execution produces an error because this type do not exist in Oracle (it should be mapped to "varchar(4000) or something alike"). Other errors are related to types "bit" (converted to "Boolean" on .NET), "ntext" (converted to "String - maxlength 2147483647") and "varbinary(max)" (converted to "Binary - maxlength 2147483647), which appears in the output script with the same types as the input, producing errors.

Here's the steps I'm taking to get to it:

- VS2017 - Add - ADO.NET Entity Data Model;
- From Database - specify database - all tables and columns - finish;
- Model.edmx - Open with... - Entity Developer;
- Regenerate Storage and Mapping;
- Update Database from Model - Next (no checkboxes checked) - select "Devart dotConnect for Oracle" and connection properties - Next - Next - (all tables selected) Next;
- If I click on "Script" I see the wrong mappings here, like "CREATE TABLE user."Application" ("name" VARCHAR(MAX) NOT NULL)" and so on;
- If I click on Execute, the error shows up.

How can I map the right types from SQLServer to Oracle?

In time, I have the latest versions of Entity Developer and dotConnect.

Thanks!

[Shalex]

First, you should change the provider to "Devart dotConnect for Oracle" (Devart.Data.Oracle). Then, do Regenerate Storage and Mapping.

Assuming that dotConnect for Oracle Professional is already installed on your workstation, here is a recommended way of converting *.edmx (System.Data.SqlClient) to *.edml (Devart.Data.Oracle):
1) install Entity Developer Professional Trial for migrating (you can uninstall it after the migration is done, it is needed because it supports System.Data.SqlClient your model was originally created for)
2) create a new user (schema) in your Oracle server
3) open your existing *.edmx model in Entity Developer
4) change provider to Devart.Data.Oracle via Database Explorer and set the connection string to Oracle server
5) right-click on designer surface > Regenerate Storage and Mapping
6) right-click on designer surface > Update Database From Model
7) right-click on the Templates node in Model Explorer > New template and choose a template.

The functionality of the DbContext template is described at https://blog.devart.com/entity-develope ... plate.html.