Entity SQL: Canonical Functions

Canonical functions were introduced in the Beta 2 release of Entity Framework. Their purpose is to expose a [virtually] canonical API beyond the core language constructs. Consumers of canonical functions should be aware that Entity Framework defines only the “syntax” part of the API. The behavior is entirely up to the store provider. Not the store, but the ADO.NET provider used to connect to it. That means different providers for the same store may map functions differently. A decent store provider would document how exactly each canonical function is mapped to a store function. For SqlClient that reference is placed under:

ADO.NET Entity Framework > Feature Reference > .NET Framework Data Provider for SQL Server

in the Entity Framework documentation.

Canonical functions are contained in the Edm namespace. Specifying the Edm namespace is optional. However, specifying a store namespace is not. For instance, all three function calls in the following Entity SQL query do exactly the same:

ROW(Edm.Length('abc') AS [Edm.Length],

    Length('abc') AS [Length],

    SqlServer.LEN('abc') AS [SqlServer.Len])

-- Powered by eSqlBlast

The T-SQL that SqlClient produces is:

SELECT

    1 AS [C1],

    CAST(LEN('abc') AS int) AS [C2],

    CAST(LEN('abc') AS int) AS [C3],

    CAST(LEN('abc') AS int) AS [C4]

FROM  ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

Canonical functions are the preferred way to access functionality outside the core language, because they keep the queries [virtually] portable. The set of canonical functions defined by Entity Framework for version 1 is a tiny subset of the set of functions any major database server exposes, but it will grow over time.

Comments

• Anonymous
  December 05, 2007
  PingBack from http://msdnrss.thecoderblogs.com/2007/12/05/entity-sql-canonical-functions/

• Anonymous
  December 06, 2007
  Zlatko: I can't find the ADO.NET Entity Framework > Feature Reference > .NET Framework Data Provider for SQL Server path in either the online or .chm versions. Are you referring to a topic in the Beta 3 docs? See http://oakleafblog.blogspot.com/2007/12/linq-and-entity-framework-posts-for.html. --rj

• Anonymous
  December 06, 2007
  That is the path in the Beta 3 wd_EntityFramework.chm file.

• Anonymous
  December 06, 2007
  Zlatko, Found the section in the Beta 3 wd_EntityFramework.chm file installed with the runtime, but the docs in your Entity Framework Documentation link (http://go.microsoft.com/fwlink/?LinkID=104984) appear to be for Beta 2 (mislabeled as Beta 3). See updated http://oakleafblog.blogspot.com/2007/12/entity-framework-beta-3-available-for.html --rj

• Anonymous
  December 06, 2007
  What I see is the Beta 3 documentation. I see features introduced in Beta 3. There must have been a caching issue with the redirection when it was made public. BTW, I didn't know the doc would go online. Now that I can copy-and-paste hyperlinks rather than writing paths in the .chm file.

• Anonymous
  December 19, 2007
  I recently blogged about canonical functions in Entity Framework and SQL Server’s LEN() function . The

• Anonymous
  December 19, 2007
  I recently blogged about canonical functions in Entity Framework and SQL Server’s LEN() function . The