dotConnect for Oracle fully supports ADO.NET Entity Framework and can be used with standard Visual Studio Entity Data Model Wizard. The wizard, however, is tuned for SQL Server and exposes some problems when used against other data sources. This topic provides information on overcoming the issues of Entity Data Model Wizard. This topic is also applicable to LightSwitch Attach Data Source Wizard.

Please note that Entity Data Model Wizard and LightSwitch Attach Data Source Wizard are not supported by Entity Framework Core. They are supported for previous Entity Framework versions only.

To invoke the wizard, add new item to the project and select ADO.NET Entity Data Model. When supplied with valid connection parameters, the wizard shows a tree of objects that you can include in the model. There are three types of objects: tables, views, and stored procedures. The objects are grouped by schema name and sorted by object name.

You may encounter the following problems during the process of creating the model with Entity Data Model Wizard:

• Some database objects are not available in the tree.
• Some database objects are not included in the model though you have selected them.
• Tables or views have some columns missing.
• The wizard or EDM Designer work very slowly.

For Entity Framework v6 you also need to register our Entity Framework provider in the config file of your project.

Database Objects Not Available in the Tree

The wizard includes all objects that the database user has access to. The visibility scope is determined by SELECT and EXECUTE grants, system privileges SELECT ANY TABLE and EXECUTE ANY PROCEDURE for both user and his roles. If you have any objects missing, make sure you are granted proper privileges.

Database Objects Are Not Included in the Model

ADO.NET Entity Framework can't work with user-defined types and PL/SQL types that cannot be used in tables. Therefore, database objects that can be included in the model are subject to the following restrictions:

• Object tables must be based upon primitive types only.
• Stored procedures must have only parameters of supported types. If any parameter has unsupported type, the stored procedure is excluded from the model.
• Tables and views must have inferable entity key.

Entity Framework requires every entity to have at least one column in the entity key. Usually entity key is inferred basing on the table's primary key. If a table has no primary key, the entity key includes all NOT NULL columns. If a table has neither primary key nor NOT NULL columns, it is excluded from the model. The same applies to views: if a view has no NOT NULL columns, it is excluded from the model.

Entity Framework also exposes the following requirements to primary keys: it should be constrained NOT NULL and should not contain binary columns. If a primary key fails in these conditions, the entity is excluded from the model.

Tables Or Views Have Some Columns Missing

Columns of unsupported types are not included in the model. The tables and views that contain such columns are included, but without corresponding property.

After the generation of the model you can inspect the list of excluded objects in Visual Studio Error List window.

The Wizard Or EDM Designer Work Very Slowly

The way Microsoft EDM Wizard retrieves metadata from data source is not the best way for Oracle. The wizard's performance significantly decreases when there are many database objects available. EDM Designer can be extremely slow for models with more than 100 entities.

If you experience performance problems, you can try the following workarounds:

• Do not connect as DBA.
• For the current user or role remove the privileges SELECT ANY TABLE and EXECUTE ANY PROCEDURE. Also you can remove grants SELECT and EXECUTE for particular objects.
• Create a new user with access only to objects that are intended for the model.
• Move the database schema to a clean installation of Oracle server and generate the model with that server.
• Decrease the amount of model entities.

Instead of Microsoft EDM Wizard, you can use our Entity Developer - our advanced model designer for Entity Framework and LinqConnect. It is much more compatible with Oracle and does not have the mentioned problems.

Using the Entity Data Provider with Entity Framework v6

Since there are no global Entity Framework provider registration for Entity Framework v6, you should register our provider manually in the config file of your project. For this you should add the following line:

<provider invariantName="Devart.Data.Oracle"
type="Devart.Data.Oracle.Entity.OracleEntityProviderServices, Devart.Data.Oracle.Entity.EF6, Version=8.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />

To the entityFramework -> providers section.

<entityFramework>
    <providers>
      <provider invariantName="Devart.Data.Oracle"
      type="Devart.Data.Oracle.Entity.OracleEntityProviderServices, Devart.Data.Oracle.Entity.EF6, Version=8.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
</entityFramework>

Note: replace 8.1.26.0 with the actual assembly version.

This section appears in the project config file only after you add the NuGet EntityFramework package either explicitly, by executing the "install-package EntityFramework" command, or implicitly, by adding an Entity Framework v6 model for SQL Server via EDM Wizard.

See Also

Entity Framework Support