CLR Method to Canonical Function Mapping

The Entity Framework provides a set of canonical functions that implement functionality that is common across many database systems, such as string manipulation and mathematical functions. This enables developers to target a broad range of database systems. When called from a querying technology, such as LINQ to Entities, these canonical functions are translated to the correct corresponding store function for the provider being used. This allows function invocations to be expressed in a common form across data sources, providing a consistent query experience across data sources. The bitwise AND, OR, NOT, and XOR operators are also mapped to canonical functions when the operand is a numeric type. For Boolean operands, the bitwise AND, OR, NOT, and XOR operators compute the logical AND, OR, NOT, and XOR operations of their operands. For more information, see Canonical Functions.

For LINQ scenarios, queries against the Entity Framework involve mapping certain CLR methods to methods on the underlying data source through canonical functions. Any method calls in a LINQ to Entities query that are not explicitly mapped to a canonical function will result in a runtime NotSupportedException exception being thrown.

System.String Method (Static) Mapping

System.String method (static) Canonical function

System.String Concat(String str0, String str1)

Concat(str0, str1)

System.String Concat(String str0, String str1, String str2)

Concat(Concat(str0, str1), str2)

System.String Concat(String str0, String str1, String str2, String str03)

Concat(Concat(Concat(str0, str1), str2), str3)

Boolean Equals(String a, String b)

= operator

Boolean IsNullOrEmpty(String value)

(IsNull(value)) OR Length(value) = 0

Boolean op_Equality(String a, String b)

= operator

Boolean op_Inequality(String a , String b)

!= operator

Microsoft.VisualBasic.Strings.Trim(String str)

Trim(str)

Microsoft.VisualBasic.Strings.LTrim(String str)

Ltrim(str)

Microsoft.VisualBasic.Strings.RTrim(String str)

Rtrim(str)

Microsoft.VisualBasic.Strings.Len(String expression)

Length(expression)

Microsoft.VisualBasic.Strings.Left(String str, Int32 Length)

Left(str, Length)

Microsoft.VisualBasic.Strings.Mid(String str, Int32 Start, Int32 Length)

Substring(str, Start, Length)

Microsoft.VisualBasic.Strings.Right(String str, Int32 Length)

Right(str, Length)

Microsoft.VisualBasic.Strings.UCase(String Value)

ToUpper(Value)

Microsoft.VisualBasic.Strings.LCase(String Value)

ToLower(Value)

System.String Method (Instance) Mapping

System.String method (instance) Canonical function Notes

Boolean Contains(String value)

this LIKE '%value%'

If value is not a constant, then this maps to IndexOf(this, value) > 0

Boolean EndsWith(String value)

this LIKE '%value'

If value is not a constant, then this maps to Right(this, length(value)) = value.

Boolean StartsWith(String value)

this LIKE 'value%'

If value is not a constant, then this maps to IndexOf(this, value) = 1.

Length

Length(this)

Int32 IndexOf(String value)

IndexOf(this, value) - 1

System.String Insert(Int32 startIndex, String value)

Concat(Concat(Substring(this, 1, startIndex), value), Substring(this, startIndex+1, Length(this) - startIndex))

System.String Remove(Int32 startIndex)

Substring(this, 1, startIndex)

System.String Remove(Int32 startIndex, Int32 count)

Concat(Substring(this, 1, startIndex) , Substring(this, startIndex + count +1, Length(this) - (startIndex + count)))

Remove(startIndex, count) is only supported if count is an integer greater than or equal to 0.

System.String Replace(String oldValue, String newValue)

Replace(this, oldValue, newValue)

System.String Substring(Int32 startIndex)

Substring(this, startIndex +1, Length(this) - startIndex)

System.String Substring(Int32 startIndex, Int32 length)

Substring(this, startIndex +1, length)

System.String ToLower()

ToLower(this)

System.String ToUpper()

ToUpper(this)

System.String Trim()

Trim(this)

System.String TrimEnd(Char[] trimChars)

RTrim(this)

System.String TrimStart(Char[]trimChars)

LTrim(this)

Boolean Equals(String value)

= operator

System.DateTime Method (Static) Mapping

System.DateTime method (static) Canonical function Notes

Boolean Equals(DateTime t1, DateTime t2)

= operator

System.DateTime.Now

CurrentDateTime()

System.DateTime.UtcNow

CurrentUtcDateTime()

Boolean op_Equality(DateTime d1, DateTime d2)

= operator

Boolean op_GreaterThan(DateTime t1, DateTime t2)

> operator

Boolean op_GreaterThanOrEqual(DateTime t1, DateTime t2)

>= operator

Boolean op_Inequality(DateTime t1, DateTime t2)

!= operator

Boolean op_LessThan(DateTime t1, DateTime t2)

< operator

Boolean op_LessThanOrEqual(DateTime t1, DateTime t2)

<= operator

Microsoft.VisualBasic.DateAndTime.DatePart( _

ByVal Interval As DateInterval, _

ByVal DateValue As DateTime, _

Optional ByVal FirstDayOfWeekValue As FirstDayOfWeek = VbSunday, _

Optional ByVal FirstWeekOfYearValue As FirstWeekOfYear = VbFirstJan1 _

) As Integer

See the DatePart Function section for more information.

Microsoft.VisualBasic.DateAndTime.Now

CurrentDateTime()

Microsoft.VisualBasic.DateAndTime.Year(DateTime TimeValue)

Year()

Microsoft.VisualBasic.DateAndTime.Month(DateTime TimeValue)

Month()

Microsoft.VisualBasic.DateAndTime.Day(DateTime TimeValue)

Day()

Microsoft.VisualBasic.DateAndTime.Hour(DateTime TimeValue)

Hour()

Microsoft.VisualBasic.DateAndTime.Minute(DateTime TimeValue)

Minute()

Microsoft.VisualBasic.DateAndTime.Second(DateTime TimeValue)

Second()

System.DateTime Method (Instance) Mapping

System.DateTime method (instance) Canonical function

Boolean Equals(DateTime value)

= operator

Day

Day(this)

Hour

Hour(this)

Millisecond

Millisecond(this)

Minute

Minute(this)

Month

Month(this)

Second

Second(this)

Year

Year(this)

System.DateTimeOffset Method (Instance) Mapping

The mapping shown for the get methods on the listed properties.

System.DateTimeOffset method (instance) Canonical function Notes

Day

Day(this)

Not supported against SQL Server 2005.

Hour

Hour(this)

Not supported against SQL Server 2005.

Millisecond

Millisecond(this)

Not supported against SQL Server 2005.

Minute

Minute(this)

Not supported against SQL Server 2005.

Month

Month(this)

Not supported against SQL Server 2005.

Second

Second(this)

Not supported against SQL Server 2005.

Year

Year(this)

Not supported against SQL Server 2005.

Note

The Equals method returns true if the compared DateTimeOffset objects are equal; false otherwise. The CompareTo method returns 0, 1, or -1 depending on whether the compared DateTimeOffset object is equal, greater than, or less than, respectively.

System.DateTimeOffset Method (Static) Mapping

The mapping shown for the get methods on the listed properties.

System.DateTimeOffset method (static) Canonical function Notes

System.DateTimeOffset.Now()

CurrentDateTimeOffset()

Not supported against SQL Server 2005.

System.TimeSpan Method (Instance) Mapping

The mapping shown for the get methods on the listed properties.

System.TimeSpan method (instance) Canonical function Notes

Hours

Hour(this)

Not supported against SQL Server 2005.

Milliseconds

Millisecond(this)

Not supported against SQL Server 2005.

Minutes

Minute(this)

Not supported against SQL Server 2005.

Seconds

Second(this)

Not supported against SQL Server 2005.

Note

The Equals method returns true if the compared TimeSpan objects are equal; false otherwise. The CompareTo method returns 0, 1, or -1 depending on whether the compared TimeSpan object is equal, greater than, or less than, respectively.

DatePart Function

The DatePart Function is mapped to one of several different canonical functions, depending on the value of Interval. The following table displays the canonical function mapping for the supported values of Interval:

Interval value Canonical function

DateInterval.Year

Year()

DateInterval.Month

Month()

DateInterval.Day

Day()

DateInterval.Hour

Hour()

DateInterval.Minute

Minute()

DateInterval.Second

Second()

Mathematical Function Mapping

CLR method Canonical function

System.Decimal.Ceiling(Decimal d)

Ceiling(d)

System.Decimal.Floor(Decimal d)

Floor(d)

System.Decimal.Round(Decimal d)

Round(d)

System.Math.Ceiling(Decimal d)

Ceiling(d)

System.Math.Floor(Decimal d)

Floor(d)

System.Math.Round(Decimal d)

Round(d)

System.Math.Ceiling(Double a)

Ceiling(a)

System.Math.Floor(Double a)

Floor(a)

System.Math.Round(Double a)

Round(a)

System.Math.Round(Double value, Int16 digits)

Round(value, digits)

System.Math.Round(Double value, Int32 digits)

Round(value, digits)

System.Math.Round(Decimal value, Int16 digits)

Round(value, digits)

System.Math.Round(Decimal value, Int32, digits)

Round(value, digits)

System.Math.Abs(Int16 value)

Abs(value)

System.Math.Abs(Int32 value)

Abs(value)

System.Math.Abs(Int64 value)

Abs(value)

System.Math.Abs(Byte value)

Abs(value)

System.Math.Abs(Single value)

Abs(value)

System.Math.Abs(Double value)

Abs(value)

System.Math.Abs(Decimal value)

Abs(value)

System.Math.Truncate(Double value, Int16 digits)

Truncate(value, digits)

System.Math.Truncate(Double value, Int32 digits)

Truncate(value, digits)

System.Math.Truncate(Decimal value, Int16 digits)

Truncate(value, digits)

System.Math.Truncate(Decimal value, Int32 digits)

Truncate(value, digits)

System.Math.Power(Int32 value, Int64 exponent)

Power(value, exponent)

System.Math.Power(Int32 value, Double exponent)

Power(value, exponent)

System.Math.Power(Int32 value, Decimal exponent)

Power(value, exponent)

System.Math.Power(Int64 value, Int64 exponent)

Power(value, exponent)

System.Math.Power(Int64 value, Double exponent)

Power(value, exponent)

System.Math.Power(Int64 value, Decimal exponent)

Power(value, exponent)

System.Math.Power(Double value, Int64 exponent)

Power(value, exponent)

System.Math.Power(Double value, Double exponent)

Power(value, exponent)

System.Math.Power(Double value, Decimal exponent)

Power(value, exponent)

System.Math.Power(Decimal value, Int64 exponent)

Power(value, exponent)

System.Math.Power(Decimal value, Double exponent)

Power(value, exponent)

System.Math.Power(Decimal value, Decimal exponent)

Power(value, exponent)

Bitwise Operator Mapping

Bitwise operator Canonical function for non-Boolean operands Canonical function for Boolean operands

Bitwise AND operator

BitWiseAnd

op1 AND op2

Bitwise OR operator

BitWiseOr

op1 OR op2

Bitwise NOT operator

BitWiseNot

NOT(op)

Bitwise XOR operator

BitWiseXor

((op1 AND NOT(op2)) OR (NOT(op1) AND op2))

Other Mapping

Method Canonical function

Guid.NewGuid()

NewGuid()

See Also

Concepts

LINQ to Entities