Defining Custom Functions in Entity Framework

Well, I's been 1 month since my last post. Today, I'm gonna write something about the well-known not supported issue of Entity Framework. I intended to write a LinQ to Entity query that would require a comparison and conversion between uniqueidentifier and string value in SQL database. The query looks like this:

var items = from x in XRepository.GetAll()
            join y in YRepository.GetAll() on x.UniqueId.ToString() equals y.StringValue into xy
            from z in xy.DefaultIfEmpty()
            select x;

I wrote the unit test and the test run through the code without any problem. But when I run the application against the real SQL database, there would be a following exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression. After a while googling, I realized that there are not any built-in support to achive what I want. However, in EF4, there is a class named: SqlFunctions.StringConvert that can convert from double to string. Moreover, it can be used in LinQ to Entity queries. That class led me to the EdmFunctionAttribute and then I decided to create something similar to convert from uniqueidentifier to nvarchar for my need. I definitely need a custom SQL Function for conversion:

CREATE FUNCTION [dbo].[GuidToString]
(
    @guid as uniqueidentifier
)
RETURNS varchar(50)
AS
BEGIN
    return CONVERT(varchar(50), @guid)
END");

What I do next is to modify the EDMX file and add a small xml code:

<Function Name="GuidToString" 
          Aggregate="false" 
          BuiltIn="false" 
          NiladicFunction="false" 
          IsComposable="true" 
          ParameterTypeSemantics="AllowImplicitConversion" 
          Schema="dbo" ReturnType="varchar">
    <Parameter Name="guid" Type="uniqueidentifier" Mode="In" />
</Function>

I just find </Schema></edmx:StorageModels> and add the xml code before it. Because the function would return something, so the IsComposable must be set to true and we must provide the ReturnType value for the function. After that, I create a static class like below. It can be put anywhere in your solution.

[EdmFunction("My.Namespace.Store", "GuidToString")]
public static string GuidToString(Guid guid)
{
    // This code will never been run against real SQL database
    // This will help any test requires this method pass
    return guid.ToString().ToUpper();
}

The namespace I provide to EdmFunctionAttribute is really important. It must match the namespace in the EDMX file. You can easily find the namespace as an attribute of the xml node "Schema" when viewing the EDMX file using any XML editor. Finally, I can change the code to use the method like below:

var items = from x in XRepository.GetAll()
            join y in YRepository.GetAll() on GuidToString(x.UniqueId) equals y.StringValue into xy
            from z in xy.DefaultIfEmpty()
            select x;

Cheers