Using Entity Data Model Wizard

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.

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:

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 inferrable 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.