Oracle® Data Provider for .NET Developer's Guide 12c Release 1 (12.1.0.1.0) Part Number E41125-02 |
|
|
PDF · Mobi · ePub |
ODP.NET 11.2.0.3.0 and higher includes support for the ADO.NET Entity Framework and LINQ to Entities. ODP.NET also supports Entity SQL.
Entity Framework is a framework for providing object-relational mapping service on data models. Entity Framework addresses the impedance mismatch between the relational database format and the client's preferred object format. Language Integrated Query (LINQ) defines a set of operators that can be used to query, project, and filter data in arrays, enumerable classes, XML, relational databases, and other data sources. One form of LINQ, LINQ to Entities, allows querying of Entity Framework data sources. ODP.NET supports Entity Framework such that the Oracle database can participate in object-relational modeling and LINQ to Entities queries.
Entity Framework and LINQ provides productivity benefits for the .NET developer. It abstracts the database's data model from the application's data model. Working with object-relational data becomes easier with Entity Framework's tools. Oracle's integration with Entity Framework and LINQ enables Oracle .NET developers to take advantage of all these productivity benefits.
Note:
Entity Framework and LINQ to Entities support is included in ODP.NET for .NET Framework 4. ODP.NET for .NET Framework 2.0 does not support the ADO.NET Entity Framework and LINQ to Entities.
Entity Framework 4.1 is supported. However, the Code First feature, which is part of Entity Framework 4.1, is not currently supported.
Binding scalar parameters is supported with ODP.NET and Entity Framework. In Entity Framework, parameter binding by name is supported. Binding by position is not supported.
Entity data models can now be generated from Oracle database schemas. These Oracle entity data models can be queried and manipulated using Visual Studio and ODP.NET. Oracle supports Database First and Model First modeling approaches. Specifying filters on the Visual Studio Server Explorer data connection enables the Entity Data Model Wizard to also filter Oracle database objects that are fetched and displayed.
LINQ to Entities can perform queries on the Oracle Database using ODP.NET, including using LINQ to Entities built-in functions. INSERT
s, UPDATE
s, and DELETE
s can be executed using Oracle stored procedures, or by using the ObjectContext
SaveChanges
method.
ODP.NET supports function import of Oracle stored procedures that Entity Framework can then execute. These Oracle function imports can return a collection of scalar, complex, and entity types, including returning an Oracle implicit result set as an entity type. Implicit result set binding is supported using Oracle REF CURSOR.
See "Implicit REF CURSOR Binding" for more details.
See Also:
For a tutorial on how to use Entity Framework, Language Integrated Query (LINQ), and generate Data Definition Language (DDL) scripts using Model First, refer to:The ODP.NET manifest file describes the primitive types, such as VARCHAR2
and Number
, and the Entity Data Model (EDM) types, such as string
and Int32
, that they map to. It also includes the facets for each EDM type.
ODP.NET does not support Time literals and canonical functions related to the Time type.
Oracle considers both NULL
and empty strings to be NULL
strings and are considered to be equal. Operations, such as Equals(), Length(),
and Trim()
on such strings will result in a NULL
string.
Table 3-5 maps the Oracle data types to their corresponding EDM types. The table also includes details about provider type attributes and the EDM type facets associated with each Oracle data type.
Table 3-5 Mapping of Oracle Data Types and EDM Types
Oracle Data Types | EDM Types(Primitive-TypeKind) | Provider Type Attributes: Name and Value | EDM Type Facets |
---|---|---|---|
|
|
|
|
(introduced in 10g) |
|
|
Not Applicable |
(introduced in 10g) |
|
|
Not Applicable |
|
|
|
Not Applicable |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Not Applicable |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Not Applicable |
Not Applicable and Not Supported |
|
|
|
|
Not Applicable |
|
|
|
Not Applicable |
|
|
|
Not Applicable |
(all other cases) |
|
|
|
|
|
|
|
|
Not Applicable |
Not Applicable and Not Supported |
|
|
|
|
|
|
|
|
Not Applicable |
|
Not Applicable |
Not Applicable and Not Supported |
|
|
|
|
|
|
|
|
Not Applicable |
|
|
|
|
|
|
|
|
|
|
|
|
(size) |
|
|
|
|
|
|
|
|
Not Applicable |
Not Applicable and Not Supported |
|
(introduced in 9i) |
|
|
The following sections enumerate the EDM type facets for the preceding Oracle data types:
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
2147483648
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
2147483648
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
Minimum
|
1
|
Unicode |
DefaultValue
|
False
|
FixedLength |
DefaultValue
|
True
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
2147483647
|
Unicode |
DefaultValue
|
False
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
Precision |
Constant
|
True
|
Facet name | Attribute name | Value |
---|---|---|
Precision |
Minimum
|
0
|
Scale |
Minimum
|
0
|
EDM Type Facets for Interval Day To Second
Facet name | Attribute name | Value |
---|---|---|
Precision |
Minimum
|
1
|
Scale |
Minimum
|
0
|
Note:
EDM types do not supportTimeSpan
.
Use Decimal
to represent the total number of seconds. An application can obtain a TimeSpan
by using the TimeSpan.FromSeconds
static method.
EDM Type Facets for Interval Year To Month
Facet name | Attribute name | Value |
---|---|---|
Precision |
Minimum
|
1
|
Scale |
Minimum
|
0
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
2147483647
|
Unicode |
DefaultValue
|
False
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
2147483647
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
Minimum
|
1
|
Unicode |
DefaultValue
|
True
|
FixedLength |
DefaultValue
|
True
|
Note:
ForNChar,
the actual data is subject to the maximum byte limit of 2000.
The value of 1000 for Maximum
and DefaultValue
allows the EDM wizard to display columns of NCHAR(1000)
, where 1000 is the maximum number of characters allowed in DDL.
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
2147483647
|
Unicode |
DefaultValue
|
True
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
Precision |
Minimum
|
1
|
Scale |
Minimum
|
0
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
Minimum
|
1
|
Unicode |
DefaultValue
|
True
|
FixedLength |
DefaultValue
|
False
|
Note:
ForNVARCHAR2,
the actual data is subject to the maximum byte limit of 4000.
The value of 2000 for Maximum
and DefaultValue
allows the EDM wizard to display columns of NVARCHAR2(2000)
, where 2000 is the maximum number of characters allowed in DDL.
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
Minimum
|
1
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
18
|
Unicode |
DefaultValue
|
False
|
FixedLength |
DefaultValue
|
True
|
Facet name | Attribute name | Value |
---|---|---|
Precision |
Minimum
|
0
|
EDM Type Facets for Timestamp with Local Time Zone
Facet name | Attribute name | Value |
---|---|---|
Precision |
Minimum
|
0
|
EDM Type Facets for Timestamp with Time Zone
Facet name | Attribute name | Value |
---|---|---|
Precision |
Minimum
|
0
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
4000
|
FixedLength |
DefaultValue
|
True
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
Minimum
|
1
|
Unicode |
DefaultValue
Constant |
False
|
FixedLength |
DefaultValue
|
False
|
Facet name | Attribute name | Value |
---|---|---|
MaxLength |
DefaultValue
|
2147483647
|
Unicode |
DefaultValue
|
True
|
FixedLength |
DefaultValue
|
False
|
You can configure a custom mapping in the .NET configuration file to override the default mapping for the Number(p,0)
Oracle data type. So, for example, Number(1,0)
, which is mapped to Int16
by default, can be custom mapped to the .NET Bool
or .NET Byte
type.
Example 3-1 shows a sample app.config
file that uses custom mapping to map the Number(1, 0)
Oracle data type to the bool
EDM type. The example also maps Number(3,0)
to byte
, and sets the maximum precisions for the Int16, Int32,
and Int64
data types to 4, 9, and 18 respectively.
Example 3-1 Sample Application Configuration File to Custom Map the Number (p,0) Data Type
<?xml version="1.0" encoding="utf-8"?> <configuration> <connectionStrings> </connectionStrings> <oracle.dataaccess.client> <settings> <add name="bool" value="edmmapping number(1,0)" /> <add name="byte" value="edmmapping number(3,0)" /> <add name="int16" value="edmmapping number(4,0)" /> <add name="int32" value="edmmapping number(9,0)" /> <add name="int64" value="edmmapping number(18,0)" /> </settings> </oracle.dataaccess.client> </configuration>
Example 3-1 customizes the mappings as follows:
Oracle Type | Default EDM Type | Custom EDM Type |
---|---|---|
Number(1,0) |
Int16 |
bool |
Number(2,0) to Number(3,0) |
Int16 |
byte |
Number(4,0) |
Int16 |
Int16 |
Number(5,0) |
Int16 |
Int32 |
Number(6,0) to Number(9,0) |
Int32 |
Int32 |
Number(10,0) |
Int32 |
Int64 |
Number(11,0) to Number(18,0) |
Int64 |
Int64 |
Number(19,0) |
Int64 |
Decimal |
Custom mapping configures the maximum precision of the Oracle Number
type that would map to the .NET/EDM type. So, for example, the preceding custom application configuration file configures ODP.NET to map Number(10,0)
through Number(18,0)
to Int64
, as opposed to the default range of Number(11,0)
through Number(19,0)
for Int64
.
Note:
Custom mapping does not require you to map all the .NET/EDM types. For example, if custom mapping is required just for Int16
, then having a single entry for Int16
is sufficient. Default mapping gets used for the other types.
When using Model First, a Byte
attribute is mapped to Number(3,0)
by default. However, when a model is generated for a Number(3,0)
column, it gets mapped to Int16
by default unless custom mapping for Byte
is specified.
You must make sure that your mappings allow the data to fit within the range of the .NET/EDM type and the Number(p, s)
type. If you select a .NET/EDM type with a range too small for the Oracle Number
data, then errors will occur during data retrieval. Also, if you select a .NET/EDM type, and the corresponding data is too big for the Oracle Number
column, then INSERTs and UPDATEs to the Oracle database will error out.
To enable custom mapping, add the mapping information to the .NET config file prior to EDM creation.
If the EDM was created already before providing the mapping information, then you can modify the mappings either through the Visual Studio tools or manually. Using Visual Studio, go to the EDM Model Browser page. Right-click on the table(s) requiring new data type mapping and select Table Mapping from the pop-up menu. The Mapping Details window will appear usually at the bottom of your screen. Update Column Mappings as desired.
If you need to add or delete mappings, find the Type values in the CSDL mapping section of your project's existing EDMX file. Add or delete those Type values to the .NET data types you want the application to use. In the example below, the property name types for BOOLCOL
and BYTECOL
are added to the CSDL and mapped to Boolean and Byte, respectively.
Example Mapping Before CSDL Customization:
<Property Name="INT16COL" Type="Int16" Nullable="false" />
Example Mapping After CSDL Customization:
<Property Name="BOOLCOL" Type="Boolean" Nullable="false" /> <Property Name="BYTECOL" Type="Byte" Nullable="false" /> <Property Name="INT16COL" Type="Int16" Nullable="false" />
You can employ combinations of these customization possibilities depending on your planned mapping changes. If many tables and many columns require mapping changes, it is most efficient to delete the EDMX file and regenerate the data model. If a few tables and many columns require changes, then delete the affected tables, save the EDMX file, and select Update Model from Database... to include those tables again. If only a single table and one or two columns require changes, then modify the EDMX either manually or by using the Mapping Details window.
The following sections describe the Identity attribute and the Virtual column.
Oracle Database 12c (12.1) and later versions support table or view Identity attribute columns. Oracle has three Identity attribute types. When the EDM wizard generates a data model from an Oracle Identity attribute-containing table or view, ODP.NET will set the value of StoreGeneratedPattern
to Identity
in the .edmx
file for any of three Oracle Identity types. The Identity attribute-associated column will use the server-generated value during INSERT
: hence, application developers no longer need to create a sequence nor trigger. If the .NET application attempts to set the Identity attribute itself, this value will be ignored.
For Oracle Database 11g Release 2 (11.2) and earlier versions that do not support Identity columns, application developers can manually set StoreGeneratedPattern
to Identity
in columns through the entity model designer Properties after model generation, then create an INSERT
trigger. Depending on the data type, a sequence may not be necessary if a server function, such as sys_guid()
, can generate the value for the column.
Oracle Database 11g (11.1) and later versions can store expressions directly in base tables as Virtual columns, also known as Generated columns. Virtual columns cannot be inserted into or updated. ODP.NET will not automatically set StoreGeneratedPattern
to Computed
in the EF model for Virtual columns. To avoid errors, application developers need to add or change the value of StoreGeneratedPattern
to Computed
for Virtual columns after the model generation. Once done, Virtual columns are excluded from INSERT
s and UPDATE
s upon calling SaveChanges()
.
If the custom mapping in a .NET configuration file has changed, then regenerate the data model to solve compilation errors introduced by the changes.
Under certain scenarios, custom mapping may cause compilation errors when a project that uses custom mapping is loaded by Visual Studio. You may use the following workaround for such scenarios:
Open Visual Studio Help, About Microsoft Visual Studio. Click OK to exit the dialog box.
Alternatively, open the to-be-used connection in Server Explorer.
Compile the project again to eliminate the compilation errors.
When using your custom INSERT, UPDATE,
or DELETE
stored procedure in Stored Procedure Mapping, the following error might occur:
Error 2042: Parameter Mapping specified is not valid.
This can happen if a Number
parameter has been mapped to a Boolean
attribute, or if a RAW
parameter has been mapped to a Guid
attribute.
The solution is to manually add Precision="1"
for the Number
parameter, and MaxLength="16"
for the RAW parameter of your stored procedure in the SSDL.