Devart Forums

Posted: **Mon 30 Jun 2014 12:49**

Hi!

We have created an .NET Framework 4.0 entity data model (.edmx) from Oracle Database. Is it possible to use Entity Developer to generate, for example, MS SQL Server mappings and other needed files to use same model without changing any LINQ queries in our software? So target database would simply be selected by changing connection string in App.config.

I was playing around with Pro trial and I was able to create .csdl, .msl and .ssdl files for the database but do I need all of them?

My current Oracle connection string is like this:

Code: Select all

connectionString="metadata=res://*/DashboardModel.csdl|res://*/DashboardModel.ssdl|res://*/DashboardModel.msl;provider=Oracle.DataAccess.Client;provider connection string="data source=DB:1521/SCHEMA;password=PASSWORD;persist security info=True;user id=USER""

So MSSQL connection string should be something like this, right?:

Code: Select all

connectionString="metadata=DashboardModel.MSSQL.ssdl|DashboardModel.MSSQL.csdl|DashboardModel.MSSQL.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;Initial Catalog=CATALOG;password=PASSWORD;user id=USER;Integrated Security=SSPI""

Btw. If .edmx has been modified inside Visual Studio (like names of the properties and tables changed from UPPER case to "First letter uppercase" like TableName or PropertyName, should Entity Developer use these same tablenames and property names when targeting some other database? It seemed that I didn't get same results but everything was uppercase even though I tried to change settings...

Br,
Kalle

Posted: **Tue 01 Jul 2014 13:17**

Hi,

Little bit progress here. I opened my .edmx file in Entity Developer 5.7.387 and changed Synchronization->Mapping to SQL Server (2012). Then I added Template (DbContext) and after saving the .edml I was able to generate .ssdl, .msl and .csdl files.

I'm still little bit confused if it's enough to use my original .csdl and .msl as an embedded resources and only add new Entity Developer generated MSSQL .ssdl like this:

connectionString="metadata=res://*/DashboardModel.csdl|DashboardModel.MSSQL.ssdl|res://*/DashboardModel.msl;provider=System.Data.SqlClient;..."; providerName="System.Data.EntityClient"...

This gives me following errors:

Code: Select all

Schema specified is not valid. Errors: 

DashboardModel.msl(6,10) : error 2007: The Table 'COMPANY' specified as part of this MSL does not exist in MetadataWorkspace.

DashboardModel.msl(5,8) : error 2063: At least one property must be mapped in the set mapping for 'Companies'.

DashboardModel.msl(21,10) : error 2007: The Table 'CONTROL_INSTANCE' specified as part of this MSL does not exist in MetadataWorkspace.

DashboardModel.msl(20,8) : error 2063: At least one property must be mapped in the set mapping for 'ControlInstances'.

But if I use also .csdl and .msl that were generated using Entity Developer I get different error at the same point of the code where I'm trying to query for user parameter:

connectionString="metadata=DashboardModel.MSSQL.csdl|DashboardModel.MSSQL.ssdl|DashboardModel.MSSQL.msl;provider=System.Data.SqlClient;

Code: Select all

The Member 'DashboardUser' in the conceptual model type 'Model.USER_PARAMETER__USER_FK' is not present in the CLR type 'Model.USER_PARAMETER__USER_FK'.

Which one of my connection strings is more right?

How can I get correct files generated based on my existing .edmx file and how to use them in connection string or is there still some manual work to do?

Br,

Kalle

Posted: **Wed 02 Jul 2014 16:53**

Please refer to http://forums.devart.com/viewtopic.php?f=30&t=28002.
More tips: http://blog.devart.com/entity-framework ... html#first.

Posted: **Thu 03 Jul 2014 06:39**

Hi!

This was helpful, although what I'm trying to do now to start with is to get CSDL, SSDL and MSL based on my existing .edmx and use those for Oracle (which works fine if I use MSL, CSDL and SSDL as embedded resources in connection string like so):

Code: Select all

... connectionString="metadata=res://*/DashboardModel.csdl|res://*/DashboardModel.ssdl|res://*/DashboardModel.msl;provider=Oracle.DataAccess.Client;

But now that I generate Oracle CSDL, SSDL and MSL using Entity Developer I haven't been able to make it work. So I'm basically trying to get this work the same way like it works with the original .edmx (as embedded) but use Entity Developer generated CSDL, SSDL and MSL instead.

Connection string is like this:

Code: Select all

... connectionString="metadata=.\DashboardModel.Oracle.csdl|.\DashboardModel.Oracle.ssdl|.\DashboardModel.Oracle.msl;provider=Oracle.DataAccess.Client;...

What I just tried:
- changed DbContext template to use "Database Independent = True", "Fluent Mapping = True", then there's this "Mapping Generation Strategy", should I go with default UseOnModelCreatingMethod?

After generating the code, should I replace the Model.Designer.cs that Visual Studio has generated for my .edmx to this .Designer.cs that is generated by Entity Developer? I did that and I had to add some assemblies to the project (EntityFramework, System.Data.Entity, System.ComponentModel.DataAnnotations,...) to get rid of some errors but there is still some left:

Error 79 Argument 1: cannot convert from 'System.Data.Objects.ObjectContext' to 'string'

Error 30 Argument 2: cannot convert from 'bool' to 'System.Data.Entity.Infrastructure.DbCompiledModel'

Error 78 The best overloaded method match for 'System.Data.Entity.DbContext.DbContext(string, System.Data.Entity.Infrastructure.DbCompiledModel)' has some invalid arguments

At this point I had added EntityFramework 6 to my project using NuGet:
install-package EntityFramework -Project <myproject>

All errors come in this part of the code:

Code: Select all

        /// <summary>
        /// Initialize a new EDMEntitiesEF object.
        /// </summary>
        public EDMEntitiesEF(ObjectContext objectContext, bool dbContextOwnsObjectContext) :
                base(objectContext, dbContextOwnsObjectContext)
        {
            Configure();
        }

So could you clarify that I do need to replace this Visual Studio generated .Designer.cs with the one that Entity Developer generates and in addition I need to set connection string to use CSDL, SSDL and MSL of the oracle model? Previously I have only tried to use these CSDL, SSDL and MSL's like in my connection string example and I get error:

The Member 'DashboardUser' in the conceptual model type 'Model.USER_PARAMETER__USER_FK' is not present in the CLR type 'Model.USER_PARAMETER__USER_FK'.

After I get this Oracle model working using these files I think it's easier to generate same files for MSSQL Server and get it working also... But when generating files for MSSQL I don't need to use that .Designer.cs because it's basically the same that I created with Oracle, right?

My library project that uses this Oracle .edmx is using .NET Framework 4 as Target framework.

What is the minimum EntityFramework version for this btw? I added EntityFramework 6 earlier to the project and now that I changed it to 4.3.1 I didn't get the errors above anymore but new ones instead:

Error 76 The type or namespace name 'Schema' does not exist in the namespace 'System.ComponentModel.DataAnnotations' (are you missing an assembly reference?)

Thanks for all the information you can provide, we need to get this multi-DB support working asap.

Br,

Kalle

Posted: **Thu 03 Jul 2014 09:43**

Hi!

Now we are getting somewhere. I managed to figure out that if I used Entity Framework 5.0.0.0 this .Designer.cs would compile so I changed all my projects to use .NET Framework 4.5 (some were using 4.0) and installed Entity Framework 5.0.0.0 to every project in my solution.

Now my application compiles but now I'm having problems with the connection string I guess:

<add name="EDMEntitiesEF" connectionString="metadata=.\DashboardModel.Oracle.csdl|.\DashboardModel.Oracle.ssdl|.\DashboardModel.Oracle.msl;provider=Oracle.DataAccess.Client;provider connection string='data source=HOST:1521/SCHEMA;password=PASSWORD;persist security info=True;user id=USERNAME'" providerName="System.Data.EntityClient" />

I get this when trying to do LINQ query:
An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

Inner Exception:
The provider did not return a ProviderManifestToken string.

Inner Exception #2:
Oracle.DataAccess.Client.OracleConnection is invalid

App.config has in configSections:

<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

and:

<system.data>
<DbProviderFactories>
<clear />
<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=4.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

and also:

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="v11.0" />
</parameters>
</defaultConnectionFactory>

</entityFramework>

I had to install Oracle DataAccess Components (ODAC1120320) earlier to get connection to work (otherwise I got some exception that provider was not registered or not found or something) and that is still installed.

Br,

Kalle

Posted: **Fri 04 Jul 2014 12:40**

hytka wrote:What is the minimum EntityFramework version for this btw?

You can use any of 4.3, 5.0, 6.x versions of Entity Framework.

hytka wrote:The type or namespace name 'Schema' does not exist in the namespace 'System.ComponentModel.DataAnnotations' (are you missing an assembly reference?)

Be aware of the namespace changes in EF6 comparing to the previous versions of Entity Framework: http://msdn.microsoft.com/en-us/data/upgradeef6.aspx.

hytka wrote:changed DbContext template to use "Database Independent = True", "Fluent Mapping = True", then there's this "Mapping Generation Strategy", should I go with default UseOnModelCreatingMethod?

It doesn't matter from the point of view of code correctness. Select option which will be convenient for you (e.g.: UseAttributes).

hytka wrote:

Code: Select all

connectionString="metadata=.\DashboardModel.Oracle.csdl|.\DashboardModel.Oracle.ssdl|.\DashboardModel.Oracle.msl;provider=Oracle.DataAccess.Client;provider connection string='data source=HOST:1521/SCHEMA;password=PASSWORD;persist security info=True;user id=USERNAME'" providerName="System.Data.EntityClient" />

Why don't you want to turn off generation of mapping files? For this, set Metadata Artifact Processing=Do Not Generate Mapping Files. With this setting, the connection string will look like:

Code: Select all

connectionString="data source=HOST:1521/SCHEMA;password=PASSWORD;persist security info=True;user id=USERNAME"
      providerName="Oracle.DataAccess.Client" />

Posted: **Fri 04 Jul 2014 12:57**

Hi!

Thanks for the reply. If I turn off generation of mapping files, which model (.edml or .edmx is used in the application) and what kind of setup is that? I mean I thought that I need to provide these .csdl, .ssdl and .msl files in connection string for each target.

Today I managed to get both Oracle and MS SQL Server working by generating .csdl, .ssdl and .msl for both and use .Designer.cs generated by Entity Developer with "Database Independent" set to true but "Fluent Mapping" set to false. For some reason if I used "Fluent Mapping" true then when my code calls:

using (var context = new EDMEntitiesEF())
{

}

The constructor that was selected didn't get any connection strings from the App.config but always tried to use Oracle.DataAccess with empty connection string. Then when I set "Fluent Mapping" false I got little bit different code that had constructor with these generated mapping files in the constructor and empty connection string... So basically what I need to do now is to delete this constructor base(...) string after generating the .Designer.cs so that when entities are created I get connection string from the App.config.

So I'm not sure what Fluent Mapping should do in my case or what difference would it make if I could use it? I now need to provide these 3 generated files for both Oracle and MSSQL and also replace .Designer.cs (which has been generated from .edmx in visual studio) with the one generated by Entity Developer.

It's looking good now because I've managed to get connected to both Oracle and MS SQL Server but still if this "process" could be simplified or clarified more I would appreciate it a lot.

One more thing: When I loaded my original .edmx file to Entity Developer for some reason all Entity Sets are named TABLE_NAME + s => TABLE_NAMEs and if I use files generated with that store my LINQ queries fail with "Table or view does not exist". I needed to remove this "s" from all Entity Set names, is there any way to prevent this in Entity Developer?

Also if I write something to "Save connection settings in App.Config as:" no App.config is created or updated but instead this string that is written goes to the constructor base() call inside .Designer.cs.

Br,

Kalle

Posted: **Mon 14 Jul 2014 14:22**

hytka wrote:If I turn off generation of mapping files, which model (.edml or .edmx is used in the application) and what kind of setup is that?

EF runtime never uses *.edml/*.edmx. Both these model types are used for generating code / mapping files which are actually compiled by JIT-compiler / loaded in the app domain.
If Metadata Artifact Processing=Do Not Generate Mapping Files, only code (no mapping) files are generated and used.

hytka wrote:The constructor that was selected didn't get any connection strings from the App.config

If connection string name from app.config is not specified in the constructor, EF runtime will look for the connection string with the name which is the same as the name of your ObjectContext/DbContext descendant.

hytka wrote:When I loaded my original .edmx file to Entity Developer for some reason all Entity Sets are named TABLE_NAME + s

For existing entities in the model: open Model Explorer, navigate to *.Store part, open the Tables/Views node, check and modify the Table Name properties of all your storage entities.
For newly added entities: open Model Settings > Synchronization > Database Naming and set the needed naming rules.

hytka wrote:Also if I write something to "Save connection settings in App.Config as:" no App.config is created or updated but instead this string that is written goes to the constructor base() call inside .Designer.cs.

This option should work only if you open the model in Entity Developer which is integrated in Visual Studio (not a standalone version).

Devart Forums

Targeting multiple databases using one Entity Model (.edmx)

Targeting multiple databases using one Entity Model (.edmx)

Re: Targeting multiple databases using one Entity Model (.edmx)

Re: Targeting multiple databases using one Entity Model (.edmx)

Re: Targeting multiple databases using one Entity Model (.edmx)

Re: Targeting multiple databases using one Entity Model (.edmx)

Re: Targeting multiple databases using one Entity Model (.edmx)

Re: Targeting multiple databases using one Entity Model (.edmx)

Re: Targeting multiple databases using one Entity Model (.edmx)