Switching from Database First to Model First approach

crazypit · Post by **crazypit** » Mon 17 Jan 2011 12:28

Hello,

I need to migrate a database schema, along with the relevant data context model, from Oracle to SQL Server. So, i used the Database First approach and created the model from my Oracle Database. I made a lot of changes on the generated data types etc.

Now, i need 2 things:

1). Create an Oracle generation script, without taking into account the Server Data Types of my properties. I need to do this, because the correct data types are changed and properly defined in Type and Facets properties. e.g for all the numbers, my database had a NUMBER data type. I need to change it to more specific numeric data types as defined in my model.

2)> Create an SQL Server generation script as above. If i change the type of Database Connection, Entity Developer still takes the old Server Data Types into account and the generated script is invalid because it still uses the Oracle data types.

Is there a proper way to do this? Or maybe if i just manually remove all the DbType attributes from the lqml file, i will effectively changed my model as if i created it using the Model First approach?

StanislavK · Post by **StanislavK** » Thu 20 Jan 2011 12:46

Concerning your questions:

1) As far as I can understand, you've specified the server data types in the model in more details than they were originally defined in the database. Am I correct? In this case, the script prepared by the Generate Database wizard should contain these specified data types, e.g., 'NUMBER(10,2)' instead of 'NUMBER'. Please specify if this is the desired scenario, and whether you are observing some other behavior of the Generate Database wizard.

2) To change server data types for another DBMS, you can, e.g., run the Generate Database wizard with the 'Regenerate Storage' option enabled (this supposes that you've changed the model connection type). In this case, model mappings will be updated for SQL Server data types.

Please tell us if this helps.

crazypit · Post by **crazypit** » Thu 20 Jan 2011 15:18

1). I do not have ANY server data types or source properties defined in my model because i removed them. The reason was, that i need to create a schema with the more strict .NET types defined in my model.

StanislavK · Post by **StanislavK** » Mon 24 Jan 2011 14:56

As I can understand, the automatic synchronization of storage part and mapping is disabled in your model. Am I correct (you can check this with the Synchronization -> Mapping -> 'Enable automatic synchronization' check box in model settings)?

In this case, you can enable this synchronization and re-generate the storage (e.g., by launching the Generate Database wizard with the 'Regenerate Storage' option enabled). In this case, server data types will be auto-generated by the data type settings you've specified. The DBMS data types of which should be used, can be defined in the Synchronization -> Mapping dialog as well.

Please tell us if this helps.

crazypit · Post by **crazypit** » Mon 24 Jan 2011 15:17

Yes, the aforementioned check is disabled. So, i enable it and enter proper DBMS Type and schema and close. A Database Connection is created in the Database Explorer which does not have proper connection string. What is the point of that? Maybe a better design should be applied...

Nevertheless, i enter the correct connection string. I go to the Generate Database option where i enable the Regenerate Storage option and proceed until i get an exception, i guess at the final step:

Exception Information
=========================
Message: convertType
Source: EntityDeveloper.Common
TargetSite: Void AddMainDefinition(System.String, EntityDeveloper.ServerDataTypeInfo, Boolean, System.Text.StringBuilder)
HelpLink:
Type: System.ArgumentException
Stack:
at EntityDeveloper.OracleDataTypeGenerator.AddMainDefinition(String dataTypeName, ServerDataTypeInfo typeInfo, Boolean isIncludeDimensions, StringBuilder sb)
at EntityDeveloper.BaseDataTypeGenerator.GetServerTypeName(String dataTypeName, ServerDataTypeInfo typeInfo, ServerDataTypeGenerationBehaviour behaviour)
at EntityDeveloper.Linq.LinqModelMapper.a(LinqProperty A_0)
at EntityDeveloper.Linq.LinqModelMapper.b(LinqProperty A_0, String A_1)
at EntityDeveloper.Linq.LinqModelMapper.AddPropertyMapping(Property property)
at EntityDeveloper.ModelMapper.AddModelMapping()
at EntityDeveloper.ai.b()
at EntityDeveloper.ai.d(Boolean A_0)
at EntityDeveloper.c7.l(Boolean A_0)
at EntityDeveloper.co.u()
at EntityDeveloper.fc.b(Object A_0, EventArgs A_1)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)

StanislavK · Post by **StanislavK** » Tue 25 Jan 2011 18:10

To create the model connection, it is necessary to specify its type, which is possible only after you set the DBMS with which to synchronize. It is created with almost blank connection string, as no other connection information is available at this moment.

As for exception, could you please send us the model with which you've encountered it, and specify
- the exact version of LinqConnect you are using (is it the latest 2.0.10 build?);
- the DBMS, its version and the schema name you've set in the synchronization properties.

crazypit · Post by **crazypit** » Wed 26 Jan 2011 07:38

I have already created a relevant support ticket titled "Error when trying to generate Database through Entity Developer." since 17/01 where the relevant info along with the problematic lqml file are presented.

StanislavK · Post by **StanislavK** » Thu 27 Jan 2011 11:59

Sorry for the delay, the related letters were blocked by our filter for some reason.

We have reproduced the problem with generating the script for the model you've sent. We will investigate the problem and inform you about the results as soon as possible.

StanislavK · Post by **StanislavK** » Fri 28 Jan 2011 18:19

The problem is that the ActivationStatusEnum type unknown to Entity Developer is used for one of the fields. We have fixed the crash of script generation: now all unknown types will be mapped to string server data types.

The fix will be available in the nearest build. At the moment, you can set one of supported types for this field to generate the script.

crazypit · Post by **crazypit** » Mon 31 Jan 2011 09:21

Well, this is an Enum type and the implementation should take into account the final Enum support you plan to integrate into the product. The string server data type is ok for me, particularly if you honor the Fixed Length & Max Length facets, for me an Enum in Oracle is a Char(1) or varchar2(2) data types. But for others, it can be something else, an int maybe. So, there is more to it than a simple fallback scenario which is ok with a really unknown data type.

StanislavK · Post by **StanislavK** » Thu 03 Feb 2011 16:49

After the Enum support is implemented, we will consider differentiating server data types for Enum fields when generating scripts.

crazypit · Post by **crazypit** » Tue 08 Feb 2011 10:20

To continue, i have some observations:

1). What is the point of MaxLength facet for numbers? It seems that only the precision and scale are checked for proper column data type generation.

2). Is seems to me that there is not a way to generate a CLOB column. I have a string property which i need it to map to a CLOB. If i set MaxLength = -1, a VARCHAR2(4000) is generated. If i set the MaxLength to a large Int32 like 9999999, an error (i guess) is raised. I only get a message "convertType" when switching to the "Choose Schemas" form of the relevant wizard.

Of course, the aforementioned issues should be properly handled for all DB Types.

StanislavK · Post by **StanislavK** » Wed 09 Feb 2011 16:07

Thank you for your suggestion, we will consider disabling some of the Facets properties depending on the field type. We will post here when the status of this feature changes.

As for CLOB columns, could you please specify the version of LinqConnect you are using? In the latest 2.20.11 build, strings with MaxLength equal to 0 or -1 should be mapped to CLOBs.

crazypit · Post by **crazypit** » Thu 10 Feb 2011 09:32

That is true. Using the latest version, a CLOB column can be generated as described. Thanks.

StanislavK · Post by **StanislavK** » Fri 07 Oct 2011 09:40

We have implemented filtering the available facets depending on the fields type. This change is available in the latest 4.1.55 build of Entity Developer (and in the new builds of data providers as well). The new build of Entity Developer can be downloaded from
http://www.devart.com/entitydeveloper/download.html
(the trial and free versions only) or from Registered Users' Area (for users with active subscription only).

For more information about the fixes and improvements available in the new build, please refer to
http://www.devart.com/forums/viewtopic.php?t=22169