Using the ADO.NET Entity Framework

Using the ADO.NET Entity Framework

Developers can use the ADO.NET Entity Framework to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema.

Mapping Pervasive Data Types to Simple Types

In Table 15, the columns are defined as follows:

•

The Pervasive Database Data Type column refers to the native type name.

•

The SSDL column refers to data types used by the store schema definition language (SSDL) file. The storage metadata schema is a formal description of the database that persists data for an application built on the EDM.

•

The EDM Simple Type column refers the method that must be used to access a column of this type when using a DataReader.

Table 15

Mapping Pervasive Data Types to EDM Simple Types

Pervasive Database Data Type

EDM Simple Type

UNIQUEIDENTIFIER

Table 16 shows the mapping of EDM Simple Types to Pervasive data types. The columns are defined as follows:

•

The EDM Simple Type column refers the method that must be used to access a column of this type when using a DataReader.

•

The SSDL column refers to data types used by the store schema definition language (SSDL) file. The storage metadata schema is a formal description of the database that persists data for an application built on the EDM.

•

The Pervasive Data Type column refers to the native type name.

Table 16

Mapping EDM Simple Types to Pervasive Data Types

EDM Simple Type

Pervasive Database Data Type

UNIQUEIDENTIFIER

Mapping EDM Canonical Functions to Pervasive Functions

The ADO.NET Entity Framework translates the Entity Data Model (EDM) canonical functions to the corresponding data source functionality for the ADO.NET Entity Framework data provider for Pervasive PSQL. The function invocations are expressed in a common form across data sources.

Because these canonical functions are independent of data sources, argument and return types of canonical functions are defined in terms of types in the EDM. When an Entity SQL query uses canonical functions, the appropriate function is called at the data source.

Both null-input behavior and error conditions are explicitly specified for all canonical functions. However, the ADO.NET Entity Framework does not enforce this behavior. Further details are available at: http://msdn.microsoft.com/en-us/library/bb738626.aspx

Aggregate Canonical Functions

Table 17 describes the mapping of EDM aggregate canonical functions to Pervasive functions.

Table 17

Mapping Aggregate Canonical Functions

Aggregate Canonical Function

Pervasive functions

Avg(expression)

avg(expression)

BigCount(expression)

count(expression)

Count(expression)

count(expression)

Max(expression)

max(expression)

Min(expression)

min(expression)

StDev(expression)

stdev(expression)

StDevP(expression)1

stdevp(expression)

Sum(expression)

sum(expression)

Var(expression)1

var(expression)

VarP(expression)1

varp(expression)

1 Requires Pervasive PSQL v11.30 Update 4 (May 2013)

Math Canonical Functions

Table 18 describes the mapping of EDM math canonical functions to Pervasive PSQL functions used to process columns that contain only decimal and integer values.

For more information, refer to the Numeric Functions.

Table 18

Mapping Math Canonical Functions

Math Canonical Function

Pervasive Function

Abs(value)

abs(value)

Floor(value)

floor(value)

Power(value, exponent)

power(value, exponent)

Round(value)

round(numeric_expression1, integer_expression2)

Round(value, digits)

round(value, digits)

Truncate(value, digits)

truncate(value, digits)

Date and Time Canonical Functions

Table 19 describes the mapping of EDM date and time canonical functions to Pervasive functions that generate, process, and manipulate data that consists of data types such as DATE and TIME.

Table 19

Mapping Date and Time Canonical Functions

Date and Time Canonical Function

Pervasive Functions

AddNanoseconds(expression,number)

dateadd(millisecond,number/1000000)

AddMicroseconds(expression,number)

dateadd(millisecond,number/1000)

AddMilliseconds(expression,number)

dateadd(millisecond,number)

AddSeconds(expression,number)

dateadd(second,number)

AddMinutes(expression,number)

dateadd(minute,number)

AddHours(expression,number)

dateadd(hour,number)

AddDays(expression,number)

dateadd(day,number)

AddMonths(expression,number)

dateadd(month,number)

AddYears(expression, number)

dateadd(year,number)

CreateDateTime(year,month,day,hour,minute,second)

datetimefromparts(year,month,day,hour,minute,second,0)

CreateDateTimeOffset(year,month,day, hour,minute,second,tzoffset)1

datetimeoffsetfromparts(year,month,day,hour, minute,second,tzoffset)

CreateTime(hour,minute,second)1

timefromparts(hour,minute,second,0,0)

CurrentDateTime()

CurrentDateTimeOffset()

sysdatetimeoffset()

CurrentUtcDateTime()

current_timestamp()

Day(expression)

datepart(day,expression)

DayOfYear(startexpression,endexpression)

dayofyear(expression)

DiffNanoSeconds(startexpression,endexpression)

datediff(millisecond,startexpression,endexpression)*1000000

DiffMilliSeconds(startexpression,endexpression)

datediff(millisecond,startexpression,endexpression)

DiffMicroSeconds(startexpression,endexpression)

datediff(millisecond,startexpression,endexpression)*1000

DiffSeconds(startexpression,endexpression)

datediff(second,startexpression,endexpression)

DiffMinutes(startexpression,endexpression)

datediff(minute,startexpression,endexpression)

DiffHours(startexpression,endexpression)

datediff(hour, startexpression,endexpression)

DiffDays(startexpression,endexpression)

datediff(day, startexpression, endexpression)

DiffMonths(startexpression,endexpression)

datediff(month,startexpression,endexpression)

DiffYears(startexpression,endexpression)

datediff(year,startexpression,endexpression)

GetTotalOffsetMinutes(DateTime Offset)

datepart(tzoffset,expression)

Year(expression)

datepart(year,expression)

Month(expression)

datepart(month,expression)

Day(expression)

datepart(day,expression)

Hour(expression)

datepart(hour,expression)

Minute(expression)

datepart(minute,expression)

Second(expression)

datepart(second,expression)

Millisecond(expression)

datepart(millisecond,expression)

TruncateTime(expression)

convert(expression, SQL_DATE)

1 Requires Pervasive PSQL v11.30 Update 4 (May 2013)

Bitwise Canonical Functions

Table 20 describes the mapping of EDM bitwise canonical functions to Pervasive PSQL functions.

Table 20

Mapping Bitwise Canonical Functions

Bitwise Canonical Function

Pervasive Functions

BitWiseAnd (value1, value2)

bit_and (value1, value2)

BitWiseNot (value)

BitWiseOr (value1, value2)

BitWiseXor (value1, value2)

String Canonical Functions

Table 21 describes the mapping of EDM string canonical functions to Pervasive PSQL functions.

Table 21

Mapping String Canonical Functions

String Canonical Function

Pervasive PSQL Function

Concat(string1, string2)

concat(string1, string2)

Contains(string, target)

contains(string, target)

EndsWith(string, target)

endswith(string, target)

IndexOf(target, string2)

instr(target, string2)

Left(string1, length)

left(string1, length)

Length(string)

length(string)

LTrim(string)

ltrim(string)

Trim(string)

trim (BOTH FROM string)

Replace(string1, string2, string3)

replace(string1, string2, string3)

Reverse(string)

reverse(string)

RTrim(string)

rtrim(string)

StartsWith(string, target)

startswith(string, target)

Substring(string, start, length)

INCOMPLETE regexpr_substr(…)

ToLower(string)

lower(string)

ToUpper(string)

upper(string)

Other Canonical Functions

Table 22 describes the mapping of other canonical functions to Pervasive PSQL functions.

Table 22

Mapping Other Canonical Functions

String Canonical Function

Pervasive PSQL Function