Registering SQL Server Built-in Functions to Entity Framework Code First
It is possible to register custom functions that exist in the database so that they can be called by Entity Framework Code First LINQ queries.
For example, consider the SOUNDEX function (yes, I know, I know, I always give SOUNDEX as an example for LINQ extensions! 
). We could write it in C# as this:
1: public static String Soundex(this String input)
2: {
3: const String values = "01230120022455012623010202";
4: const Int32 encodingLength = 4;
5:
6: var prevChar = ' ';
7:
8: input = Normalize(input);
9:
10: if (input.Length == 0)
11: {
12: return (input);
13: }
14:
15: var builder = new StringBuilder(input[0]);
16:
17: for (var i = 1; ((i < input.Length) && (builder.Length < encodingLength)); ++i)
18: {
19: var c = values[input[i] - 'A'];
20:
21: if ((c != '0') && (c != prevChar))
22: {
23: builder.Append(c);
24: prevChar = c;
25: }
26: }
27:
28: while (builder.Length < encodingLength)
29: {
30: builder.Append('0');
31: }
32:
33: return (builder.ToString());
34: }
35:
36: private static String Normalize(String text)
37: {
38: var builder = new StringBuilder();
39:
40: foreach (var c in text)
41: {
42: if (Char.IsLetter(c) == true)
43: {
44: builder.Append(Char.ToUpper(c));
45: }
46: }
47:
48: return (builder.ToString());
49: }
If we want this method to be callable by a LINQ query, we need to add the DbFunctionAttribute to it, specifying the name of the database function we wish to call, because the .NET method and the database function names can be different:
1: [DbFunction("SqlServer", "SOUNDEX")]
2: public static String Soundex(this String input)
3: {
4: //...
5: }
And for calling it:
1: var soundexNames = ctx.Projects.Select(p => p.Name.Soundex()).ToList();
However, for certain database functions, it requires a bit more work to get done. Let us consider now the FORMAT function and a .NET implementation:
1: public static String Format(this DateTime value, String format, String culture)
2: {
3: return (value.ToString(format, CultureInfo.CreateSpecificCulture(culture)));
4: }
Besides adding the DbFunctionAttribute attribute:
1: [DbFunction("CodeFirstDatabaseSchema", "FORMAT")]
2: public static String Format(this DateTime value, String format, String culture)
3: {
4: //...
5: }
it also requires that we register it explicitly in our model, for that, we override the OnModelCreating method and add a custom convention:
1: protected override void OnModelCreating(DbModelBuilder modelBuilder)
2: {
3: modelBuilder.Conventions.Add(new RegisterFunctionConvention());
4:
5: base.OnModelCreating(modelBuilder);
6: }
The convention being:
1: public class RegisterFunctionConvention : IStoreModelConvention<EdmModel>
2: {
3: public void Apply(EdmModel item, DbModel model)
4: {
5: var valueParameter = FunctionParameter.Create("value", this.GetStorePrimitiveType(model, PrimitiveTypeKind.DateTime), ParameterMode.In);
6: var formatParameter = FunctionParameter.Create("format", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
7: var cultureParameter = FunctionParameter.Create("culture", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In);
8: var returnValue = FunctionParameter.Create("result", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.ReturnValue);
9:
10: var function = this.CreateAndAddFunction(item, "FORMAT", new[] { valueParameter, formatParameter, cultureParameter }, new[] { returnValue });
11: }
12:
13: protected EdmFunction CreateAndAddFunction(EdmModel item, String name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues)
14: {
15: var payload = new EdmFunctionPayload { StoreFunctionName = name, Parameters = parameters, ReturnParameters = returnValues, Schema = this.GetDefaultSchema(item), IsBuiltIn = true };
16: var function = EdmFunction.Create(name, this.GetDefaultNamespace(item), item.DataSpace, payload, null);
17:
18: item.AddItem(function);
19:
20: return (function);
21: }
22:
23: protected EdmType GetStorePrimitiveType(DbModel model, PrimitiveTypeKind typeKind)
24: {
25: return (model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType);
26: }
27:
28: protected String GetDefaultNamespace(EdmModel layerModel)
29: {
30: return (layerModel.GlobalItems.OfType<EdmType>().Select(t => t.NamespaceName).Distinct().Single());
31: }
32:
33: protected String GetDefaultSchema(EdmModel layerModel)
34: {
35: return (layerModel.Container.EntitySets.Select(s => s.Schema).Distinct().SingleOrDefault());
36: }
37: }
I got some of the metadata code from Diego Vega’s (@divega) repository: https://github.com/divega/, but changed it slightly.
Now we have the FORMAT function available to LINQ:
1: var projectDates = ctx.Projects.Select(p => p.Start.Format("D", "pt-PT")).ToList();
Now, I hear you ask: why for SOUNDEX we just need to add a simple attribute and for FORMAT we need so much more? Well, it just happens that SOUNDEX is defined in the Entity Framework SQL Server provider manifest - see it here. All of the functions in SqlFunctions are present in the manifest, but the opposite is not true - not all functions in the manifest are in SqlFunctions, but that's the way it is! Thanks to @divega for the explanation.
Some things worth mentioning:
-
The namespace and name used in the DbFunctionAttribute attribute must match those passed to EdmFunction.Create (CodeFirstDatabaseSchema is what GetDefaultNamespace returns;
-
You cannot specify two functions with the same name and different parameters.
There is an open request to add the FORMAT function to the list of functions supported out of the box by Entity Framework Code First: https://entityframework.codeplex.com/workitem/2586 through the SqlFunctions class, but in the meantime, this might be useful!