Linq to Entities and Custom Database Functions

I've been using Linq to SQL since it came out. Linq to Entities didn't have any compelling functionality, and it was far less supported in the development community. Nobody seemed to be using it. For whatever reason, when the language group at Microsoft came out with Linq, the data group was slow on the uptake and that left the language group with the need for an actual database query layer, so Linq to SQL was born. There were rumors that Linq to SQL would not continue to evolve, but it certainly worked fine for what I needed.

The 2009 Microsoft PDC had all kinds of interesting things, but absolutely nothing regarding Linq to SQL. The language group became enammored with .NET 4 and the dynamic language features and have apparently orphaned Linq to SQL. Not so the data group. The improvements to Linq to Entities seem to breathe new life into what had previously been a "me too" implementation of Linq.

Certainly the biggest news about Linq to Entities relates to the new flexibility to use POCO objects, database first, or model first for defining your objects. I especially like the ability to use t4 templates for model generation. This development certainly takes away of the allure of NHibernate as an ORM. In fact, I think you can probably start calling Linq to Entities a nearly full-featured ORM in it's new incarnation. One potentially serious oversight is the lack of ability to perform dependancy injection on object creation, but let's hope that is rectified as soon as possible.

However, the one piece that really gets me interested is the ability to call custom database functions from within your Linq statements. This is potentially huge. It is really too bad that Shyam and Chris weren't able to get the demo of this little gem to work in their presentation. There is also not a lot of documentation available for this feature. In fact, I only found one useful article in the blogosphere.

The key thing that is unclear from the documentation and article is the thing that Shyam and Chris had problems with as well. So, here is how you fix the issue:

The namespace attribute in the schema entity in the Linq to Entities edmx file must match the namespace entry of the EdmFunction code attribute in your static method. This namespace is the value in the Model Browser namespace as shown here.

<Schema Namespace="Data.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
  <Function Name="IsAuthorized" ReturnType="bit" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <Parameter Name="Id" Type="int" Mode="In" />
  </Function>
</Schema>

public static class LinqExtensions {
  [EdmFunction("Data.Store", "IsAuthorized")]
  public static bool IsAuthorized(int? id) {
    throw new Exception("Direct calls not supported");
  }
}

This allows you to write the following Linq statement:

var result = from s in dc.SomeTable
  where LinqExtensions.IsAuthorized(s.Id)
  select s;

Another important thing is that you don't have to manually edit the .edmx file. You can use the Model Browser or the design surface to select "update model from database" from the context menu. From there you can add an existing function to the model, and not have to deal with the XML in the .edmx file