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.