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! Winking smile). 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!

                             

11 Comments

  • Thanks for the great post! One question though: How would you specify user defined db functions? I tried it the same way, but it cannot find my function because the generated sql code is lacking the dbo.prefix.

  • Hi, Adrian!
    You probably haven't read https://weblogs.asp.net/ricardoperes/entity-framework-pitfalls-%E2%80%93-registering-custom-database-functions-for-linq... Unfortunately, it isn't easy. See an explanation from the EF team here: https://entityframework.codeplex.com/discussions/572518.
    You can also try https://codefirstfunctions.codeplex.com/.

  • Dang Ricardo, you're the man!! This saved my bacon today :)

  • Great! Thanks, Paul! Keep dropping by!

  • Yes, that is exactly what I needed. I had to use the format function in my project for decimal and date fields. Unfortunately you can not define two functions with different parameter. But you can define one function with the name "FORMAT" and a decimal parameter and a second function with the name "FORMAT " (with a trailing space) and a date parameter.

  • Peres,

    You've just saved my bacon with that FORMAT extension.

    Thanks

  • Great, João, thanks!

  • The specified method 'System.String Format(System.DateTime, System.String)' on the type 'ProjName.EntityDAL.DBExtentions' cannot be translated into a LINQ to Entities store expression.

  • Kate: have you followed *exactly* my code? It seems to be working, nobody else complained.

  • Hello,
    I got some issues while implementing it but after removing the schema it works like a charm.
    But I tried the same approach with the CAST, with not luck
    Here is the code I wrote

    public class CastFunctionConvention : IStoreModelConvention<EdmModel>
    {
    public void Apply(EdmModel item, DbModel model)
    {
    Func<PrimitiveTypeKind, EdmType> GetStorePrimitiveType = (typeKind) => model.ProviderManifest.GetStoreType(TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(typeKind))).EdmType;

    var valueParameter = FunctionParameter.Create("expression", GetStorePrimitiveType(PrimitiveTypeKind.DateTime), ParameterMode.In);
    var dataTypeParameter = FunctionParameter.Create("data_type", GetStorePrimitiveType(PrimitiveTypeKind.String), ParameterMode.In);
    var returnValue = FunctionParameter.Create("result", GetStorePrimitiveType(PrimitiveTypeKind.Int32), ParameterMode.ReturnValue);

    var name = "CAST";
    var parameters = new[] { valueParameter, dataTypeParameter };
    var returnValues = new[] { returnValue };

    var payload = new EdmFunctionPayload
    {
    StoreFunctionName = name,
    Parameters = parameters,
    ReturnParameters = returnValues,
    IsBuiltIn = true
    };

    item.AddItem(EdmFunction.Create(name, "CodeFirstDatabaseSchema", item.DataSpace, payload, null));
    }
    }

    public static class SqlFunctions
    {
    [DbFunction("CodeFirstDatabaseSchema", "CAST")]
    public static Int32 Cast(this String expression, string data_type)
    {
    return Convert.ToInt32(expression);
    }
    }

    and I tring to write it like that
    var projectDates = ctx.Projects.Select(p => p.DisplayOrderString.Cast("INT")).ToList();

    what is wrong with what I did?

  • Sorry I change the 'PrimitiveTypeKind.DateTime' to 'PrimitiveTypeKind.String'

Add a Comment

As it will appear on the website

Not displayed

Your website