B Mapping LINQ Canonical Functions and Oracle Functions

This appendix lists the Entity Framework canonical functions and the corresponding ODP.NET provider functions to which they map.

Aggregate Canonical Functions

Canonical Function	Oracle Function
`Avg (` `expression` `)`	`AVG(expression)`
`BigCount (` `expression` `)`	`COUNT(expression)`
`Count (` `expression` `)`	`COUNT(expression)`
`Max (` `expression` `)`	`MAX(expression)`
`Min (` `expression` `)`	`MIN(expression)`
`StDev (` `expression` `)`	`STDDEV(expression)`
`StDevP(` `expression)`	`STDEVP(expression)`
`Sum (` `expression` `)`	`SUM` `(expression)`
`Var(expression)`	`VAR(expression)`
`VarP(expression)`	`VARP(expression)`

Math Canonical Functions

Canonical Function	Oracle Function
`Abs (` `value` `)`	`ABS (value)`
`Ceiling (` `value` `)`	`CEIL(value)`
`Floor (` `value` `)`	`FLOOR(value)`
`Power(value,` `exponent)`	`POWER(value`, `exponent)`
`Round (` `value` `)`	`ROUND(value)`
`Round (` `value,` `digits` `)`	`ROUND(value`, `digits)`
`Truncate(value,` `digits)`	`TRUNC(value`, `digits)`

String Canonical Functions

Canonical Function	Oracle Function
`Concat (` `string1`, `string2)`	`CONCAT(string1`, `string2)` or `( (string1) \|\| (string2))`
`Contains(string`, `target)`	`INSTR(string,` `target)`
`EndsWith(string`, `target)`	`INSTR(REVERSE(string), REVERSE(target))`
Comparison operators `(<, <=, >, >=, <>, !=)`	Comparison operators `(<, <=, >, >=, <>, !=)`
`IndexOf(` `target,` `string)`	`INSTR(string2,` `target)`
`Left (` `string1,` `length)`	`SUBSTR(string1,` `length)`
`Length (` `string` `)`	`LENGTH(string)`
`LTrim(` `string` `)`	`LTRIM(string)`
`Replace (` `string1,` `string2,` `string3)`	`REPLACE(string1,` `string2,` `string3)`
`Reverse (` `string` `)`	`REVERSE(`string`)`
`Right (` `string,` `length)`	`(CASE WHEN LENGTH(string) >= (length) THEN SUBSTR (string) ,-(length),` `length` `) ELSE` `string` `END)`
`RTrim(` `string` `)`	`RTRIM(string)`
`Substring (` `string,` `start,` `length)`	`SUBSTR((` `string,` `start,` `length)`
`StartsWith(string`, `target)`	`INSTR(string,` `target)`
`ToLower (` `string` `)`	`LOWER(string)`
`ToUpper(` `string` `)`	`UPPER`
`Trim (` `string` `)`	`LTRIM(RTRIM(string))`

Date And Time Canonical Functions

Canonical Function	Oracle Function
`AddNanoseconds(expression,` `number)`	`(expression) + INTERVAL`
`AddMicroseconds(expression,` `number)`	`(expression) + INTERVAL`
`AddMilliseconds(expression,` `number)`	`(expression) + INTERVAL`
`AddSeconds(expression,` `number)`	`(expression) + INTERVAL`
`AddMinutes(expression,` `number)`	`(expression) + INTERVAL`
`AddHours(expression,` `number)`	`(expression) + INTERVAL`
`AddDays(expression,` `number)`	`(expression) + INTERVAL`
`AddMonths(expression,` `number)`	`(expression) + INTERVAL`
`AddYears(expression,` `number)`	`(expression) + INTERVAL`
`CreateDateTime(year,` `month,` `day,` `hour,` `minute,` `second)`	`TO_TIMESTAMP`
`CreateDateTimeOffset(year,` `month,` `day,` `hour,` `minute,` `second,` `tzoffset)`	`TO_TIMESTAMP_TZ`
`CreateTime(hour,` `minute,` `second)`	Time literals are not supported in Oracle
`CurrentDateTime()`	`LOCALTIMESTAMP`
`CurrentDateTimeOffset()`	`SYSTIMESTAMP`
`CurrentUtcDateTime()`	`SYS_EXTRACT_UTC` `(LOCALTIMESTAMP)`
`Day(expression)`	`EXTRACT(DAY FROM` `expression)`
`DayOfYear(expression)`	`TO_NUMBER(TO_CHAR(CAST(expression` `AS TIMESTAMP), 'DDD'))`
`DiffNanoseconds(startExpression`, `endExpression)`	`EXTRACT` and arithmetic operations
`DiffMilliseconds(startExpression,` `endExpression)`	`EXTRACT` and arithmetic operations
`DiffMicroseconds(startExpression,` `endExpression)`	`EXTRACT` and arithmetic operations
`DiffSeconds(startExpression,` `endExpression)`	`EXTRACT` and arithmetic operations
`DiffMinutes(startExpression,` `endExpression)`	`EXTRACT` and arithmetic operations
`DiffHours(startExpression,` `endExpression)`	`EXTRACT` and arithmetic operations
`DiffDays(startExpression,` `endExpression)`	`EXTRACT` and arithmetic operations
`DiffMonths(startExpression,` `endExpression)`	`EXTRACT` and arithmetic operations
`DiffYears(startExpression,` `endExpression` `)`	`EXTRACT` and arithmetic operations
Comparison operators `(<, <=, >, >=, <>, !=)`	`<, <=, >, >=, <>, !=` operators
`GetTotalOffsetMinutes (` `datetimeoffset` `)`	`(EXTRACT(TIMEZONE_HOUR FROM (expression))) * 60 + EXTRACT (TIMEZONE_MINUTE FROM(expression))` (Require multiple operations.)
`Hour (expression)`	`EXTRACT(HOUR FROM` `expression)`
`Millisecond(expression)`	`NVL(TO_NUMBER(SUBSTR(TO_CHAR(CAST(expression` `AS TIMESTAMP), 'DD-` `MON-RR HH24:MI:SSXFF'), 20, 3)), 0)`
`Minute(expression)`	`EXTRACT(MINUTE FROM` `expression)`
`Month (expression)`	`EXTRACT(MONTH FROM` `expression)`
`Second(expression)`	`EXTRACT (SECOND FROM` `expression)`
`TruncateDate(expression)`	`TRUNC(expression)`
`Year(expression)`	`EXTRACT(YEAR FROM` `expression)`

Bitwise Canonical Functions

Canonical Function	Oracle Function
`BitWiseAnd (` `value1` , `value2` `)`	`BITAND(value1`, `value2)`
`BitWiseNot (` `value` `)`	`( 0 -` `value) - 1`
`BitWiseOr (` `value1` `,` `value2` `)`	`Value1` - `BITAND(value1,` `value2) +` `value2`
`BitWiseXor (` `value1` `,` `value2` `)`	`Value1` `- 2 * BITAND(value1,` `value2) +` `value2`

Other Canonical Functions

Canonical Function	Oracle Function
`NewGuid()`	`SYS_GUID`