Adding custom SQL statements to Entity Framework code first Linq queries

Adding custom SQL statements to your SQL Linq queries can be really convenient. Though the System.Data.Entity.DbFunctions features many built in functions, sometimes you may need more functionality. 

One common example is type conversion. Considering a string property which can be converted to an int, one would want to write something like :

 

var myResult = myContext.DbSet<myentity>().Where(entity => SqlFunctions.ParseInt(entity.StringProperty) > 0);

One can register custom database functions using CodeFirstStoreFunctions. But sometimes you can't or don't want to alter the database schema to add your sql functions.

Here is how you can achieve using custom SQL expressions in your linq queries with Entity Framework code first (version 6.1.3)

1- Create the c# ParseInt method


namespace CustomSqlFunctions.DataBase
{
    using System;
    using System.Data.Entity;

    public class SqlFunctions
    {
        // The namespace has to exactly match the namespace of the DbContext
        [DbFunction("CustomSqlFunctions", "ParseInt")]
        public static int ParseInt(string value)
        {
           // providing an implementation is useless as long as the function will be used in the context of an Entity Framework query
            throw new NotImplementedException();
        }
    }
}

2- Create the convention

namespace CustomSqlFunctions
{
    using System;
    using System.Data.Entity.Core.Metadata.Edm;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.ModelConfiguration.Conventions;
    using System.Linq;

    public class CustomFunctionConvention : IConceptualModelConvention<edmmodel>
    {
        public void Apply(EdmModel item, DbModel model)
        {
            var functionParseInt = new EdmFunctionPayload()
            {
                CommandText = String.Format("CAST(strValue AS {0})", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32)),
                Parameters = new[] {
                    FunctionParameter.Create("strValue", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), ParameterMode.In),
                },

                ReturnParameters = new[] {
                    FunctionParameter.Create("ReturnValue", PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32), ParameterMode.ReturnValue),
                },
                IsComposable = true
            };

            var function = EdmFunction.Create("ParseInt", model.ConceptualModel.EntityTypes.First().NamespaceName, DataSpace.CSpace, functionParseInt, null);
            model.ConceptualModel.AddItem(function);
        }
    }
}

It is worth noting that the CommandText is not SQL language, it is in fact Entity SQL


3- Register the convention in your DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
      base.OnModelCreating(modelBuilder);
      modelBuilder.Conventions.Add<CustomFunctionConvention>();}

You can then use your ParseInt method as shown at the beginning of this post.
Note also that, as far as I know, this method will only work with NullDatabaseInitializer

Loading