ADO.NET Entity Framework and LINQ to Entities

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, that 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. INSERTs, UPDATEs, and DELETEs 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:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.htm

Mapping Oracle Data Types to EDM Types

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-4 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-4 Mapping of Oracle Data Types and EDM Types

Oracle Data Types EDM Types(Primitive-TypeKind) Provider Type Attributes: Name and Value EDM Type Facets

Bfile

Binary

  • Equal Comparable: False

  • Order Comparable: False

EDM Type Facets for Bfile

Binary_Double

(introduced in 10g)

Double

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Binary_Float

(introduced in 10g)

Single

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Binary_Integer

Int32

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Blob

Binary

  • Equal Comparable: False

  • Order Comparable: False

EDM Type Facets for Blob

Char

String

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Char

Clob

String

  • Equal Comparable: False

  • Order Comparable: False

EDM Type Facets for Clob

Date

DateTime

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Date

Float

Decimal

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Float

Int

Int32

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Interval Day To Second

Decimal

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Interval Day To Second

Interval Year To Month

Decimal

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Interval Year To Month

Long

String

  • Equal Comparable: False

  • Order Comparable: False

EDM Type Facets for Long

Long Raw

Binary

  • Equal Comparable: False

  • Order Comparable: False

EDM Type Facets for Long Raw

NChar

String

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for NChar

NClob

String

  • Equal Comparable: False

  • Order Comparable: False

EDM Type Facets for NClob

Nested Table

  

Not Applicable

Not Applicable and Not Supported

Number(1,0)

Number(2,0)

Number(3,0)

Number(4,0)

Number(5,0)

Int16

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Number(6,0)

Number(7,0)

Number(8,0)

Number(9,0)

Number(10,0)

Int32

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Number(11,0)

Number(12,0)

Number(13,0)

Number(14,0)

Number(15,0)

Number(16,0)

Number(17,0)

Number(18,0)

Number(19,0)

Int64

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Number

(all other cases)

Decimal

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Number

NVarchar2

String

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for NVarchar2

Object

  

Not Applicable

Not Applicable and Not Supported

Raw

Binary

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Raw

Raw(16)

Guid

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Ref

  

Not Applicable

Not Applicable and Not Supported

ROWID

String

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for ROWID

Smallint

Int16

  • Equal Comparable: True

  • Order Comparable: True

Not Applicable

Timestamp

DateTime

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Timestamp

Timestamp with

Local Time Zone

DateTime

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Timestamp with Local Time Zone

Timestamp with

Time Zone

DateTimeOffset

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Timestamp with Time Zone

UROWID

(size)

Binary

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for UROWID

Varchar2

String

  • Equal Comparable: True

  • Order Comparable: True

EDM Type Facets for Varchar2

VArray

  

Not Applicable

Not Applicable and Not Supported

XMLType

(introduced in 9i)

String

  • Equal Comparable: False

  • Order Comparable: False

EDM Type Facets for XMLType

EDM Type Facets

The following sections enumerate the EDM type facets for the preceding Oracle data types:

EDM Type Facets for Bfile 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 2147483648

True
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for Blob 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 2147483648

True
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for Char 

Facet name Attribute name Value
MaxLength Minimum

Maximum

DefaultValue

Constant

 1

2000

2000

False
Unicode DefaultValue

Constant

 False

True
FixedLength DefaultValue

Constant

 True

True

EDM Type Facets for Clob 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 2147483647

True
Unicode DefaultValue

Constant

 False

True
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for Date 

Facet name Attribute name Value
Precision Constant

DefaultValue

 True

0

EDM Type Facets for Float 

Facet name Attribute name Value
Precision Minimum

Maximum

DefaultValue

Constant

 0

126

0

False
Scale Minimum

Maximum

DefaultValue

Constant

 0

38

0

False

EDM Type Facets for Interval Day To Second 

Facet name Attribute name Value
Precision Minimum

Maximum

DefaultValue

Constant

 1

251

251

False
Scale Minimum

Maximum

DefaultValue

Constant

 0

9

0

False

Note:

EDM types do not support TimeSpan.

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

Maximum

DefaultValue

Constant

 1

250

250

False
Scale Minimum

Maximum

DefaultValue

Constant

 0

9

0

False

EDM Type Facets for Long 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 2147483647

True
Unicode DefaultValue

Constant

 False

True
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for Long Raw 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 2147483647

True
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for NChar 

Facet name Attribute name Value
MaxLength Minimum

Maximum

DefaultValue

Constant

 1

1000

1000

False
Unicode DefaultValue

Constant

 True

True
FixedLength DefaultValue

Constant

 True

True

Note:

For NChar, 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.

EDM Type Facets for NClob 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 2147483647

True
Unicode DefaultValue

Constant

 True

True
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for Number 

Facet name Attribute name Value
Precision Minimum

Maximum

DefaultValue

Constant

 1

38

38

False
Scale Minimum

Maximum

DefaultValue

Constant

 0

38

0

False

EDM Type Facets for NVarchar2 

Facet name Attribute name Value
MaxLength Minimum

Maximum

DefaultValue

Constant

 1

2000

2000

False
Unicode DefaultValue

Constant

 True

True
FixedLength DefaultValue

Constant

 False

True

Note:

For NVARCHAR2, 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.

EDM Type Facets for Raw 

Facet name Attribute name Value
MaxLength Minimum

Maximum

Constant

 1

2000

False
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for ROWID 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 18

True
Unicode DefaultValue

Constant

 False

True
FixedLength DefaultValue

Constant

 True

True

EDM Type Facets for Timestamp 

Facet name Attribute name Value
Precision Minimum

Maximum

DefaultValue

Constant

 0

9

6

False

EDM Type Facets for Timestamp with Local Time Zone 

Facet name Attribute name Value
Precision Minimum

Maximum

DefaultValue

Constant

 0

9

6

False

EDM Type Facets for Timestamp with Time Zone 

Facet name Attribute name Value
Precision Minimum

Maximum

DefaultValue

Constant

 0

9

6

False

EDM Type Facets for UROWID 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 4000

True
FixedLength DefaultValue

Constant

 True

True

EDM Type Facets for Varchar2 

Facet name Attribute name Value
MaxLength Minimum

Maximum

DefaultValue

Constant

 1

4000

4000

False
Unicode DefaultValue

Constant

 False

True
FixedLength DefaultValue

Constant

 False

True

EDM Type Facets for XMLType 

Facet name Attribute name Value
MaxLength DefaultValue

Constant

 2147483647

True
Unicode DefaultValue

Constant

 True

True
FixedLength DefaultValue

Constant

 False

True

Oracle Number Default Data Type Mapping and Customization

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.

Resolving Compilation Errors When Using Custom Mapping

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:

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

  2. Compile the project again to eliminate the compilation errors.

Mapping Boolean and Guid Parameters in Custom INSERT, UPDATE, and DELETE Stored Procedures

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.