Extending EF Core to handle SQL functions and JSON

Entity Framework Core covers a lot of ground. Yes, there are some queries I have not been able to make it do, but for 99% of my data needs, it does a great job.  When I first started with it, I had the need to handle two more advanced things:

1. Convert an nvarchar field to another type and query against it.
2. Query JSON data in an nvarchar(MAX) field

Since nvarchar fields convert to string fields, you can't directly include it in a WHERE clause using a type without first applying some magic.  Similarly, if you wanted to query against a property of a JSON object in an nvarchar field, you'd have to just fake it with a CONTAINS match, or again, find another way.  

Luckily, EF Core handles extending the framework in those instances by mapping custom functions.  

Let's start by going into your Context definition and add in a reference to our new custom functions:

protected override void OnModelCreating(ModelBuilder modelBuilder) {	
   modelBuilder.AddCustomFunctions();
}

Now create a new file called CustomFunctions.cs and toss in the following.  You can drop this snippet directly into your project and it should work as is.  I'll describe what's happening inline:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using System;
using System.Linq;
/*
* These functions allow mappings of SQL functions to EF.  
*/
namespace RainstormTech.Components
{
   public static class CustomFunctions
   {
       /*
		 * Define extension functions to handle basic data type conversions
		 */
       public static DateTime? ToSqlDateTime(this string s, int format) => throw new NotSupportedException();
       public static int? ToSqlInt(this string s) => throw new NotSupportedException();
       public static decimal? ToSqlDecimal(this string s) => throw new NotSupportedException();
       /*
		 * Define functions to handle working with JSON.  These map to the functions directly in TSQL
		 */
       [DbFunction("JSON_VALUE", IsBuiltIn = true)]
       public static string JsonValue(string column, [NotParameterized] string path) => throw new NotSupportedException();
       [DbFunction("JSON_QUERY", IsBuiltIn = true)]
       public static string JsonQuery(string source, [NotParameterized] string path) => throw new NotSupportedException();
       [DbFunction("OPENJSON", IsBuiltIn = true)]
       public static string OpenJson(string source, [NotParameterized] string path) => throw new NotSupportedException();
       /// <summary>
       /// Registers tsql functions to EF
       /// </summary>
       /// <param name="modelBuilder"></param>
       /// <returns></returns>
       public static ModelBuilder AddCustomFunctions(this ModelBuilder modelBuilder)
       {
		  // the first 3 functions map a string to the TRY_CONVERT function in TSQL
           modelBuilder.HasDbFunction(() => ToSqlDateTime(default, default))
               .HasTranslation(args =>
                   new SqlFunctionExpression(
                       functionName: "TRY_CONVERT",
                       arguments: args.Prepend(new SqlFragmentExpression("datetime2")),
                       nullable: true,
                       argumentsPropagateNullability: new[] { false, true, false },
                       type: typeof(DateTime),
                       typeMapping: null
                   )
               );
           modelBuilder.HasDbFunction(() => ToSqlInt(default))
               .HasTranslation(args =>
                   new SqlFunctionExpression(
                       functionName: "TRY_CONVERT",
                       arguments: args.Prepend(new SqlFragmentExpression("int")),
                       nullable: true,
                       argumentsPropagateNullability: new[] { false, true },
                       type: typeof(int),
                       typeMapping: null
                   )
               );
           modelBuilder.HasDbFunction(() => ToSqlDecimal(default))
                .HasTranslation(args =>
                    new SqlFunctionExpression(
                        functionName: "TRY_CONVERT",
                        arguments: args.Prepend(new SqlFragmentExpression("decimal")),
                        nullable: true,
                        argumentsPropagateNullability: new[] { false, true },
                        type: typeof(decimal),
                        typeMapping: null
                    )
                );
           // add support for Json functions
           modelBuilder.HasDbFunction(() => CustomFunctions.JsonValue(default(string), default(string)));
           modelBuilder.HasDbFunction(() => CustomFunctions.JsonQuery(default(string), default(string)));
           modelBuilder.HasDbFunction(() => CustomFunctions.OpenJson(default(string), default(string)));
           return modelBuilder;
       }
   }
}

Examples

Assuming I have models such as the following:

public class Content {
	[Key]
   	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int Id { get; set; }
	public string Title { get; set; }
	public string Json { get; set; } // this is a json field
	public ICollection<Meta> Meta { get; set; } 
}
public class Meta {
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int Id { get; set; }
	public int ContentId { get; set;}
	public string Name { get; set; }
	public string Value { get; set; } // this could fill strings, dates, ints 
	public virtual Content Content { get; set; }
}

Let's try to get meta records that match a variety of content types

var content = dbContext.Content
   .Include(o => o.Meta)
   .WHERE(o => o.Meta.Value.ToSqlDateTime(102) > DateTime.UtcNow);
  
// Or 
  
var content = dbContext.Content
   .Include(o => o.Meta)
   .WHERE(o => o.Meta.Value.ToInt() > 100);
   

Now if we want to query by JSON, let's assume the Json field holds a JSON object similar to this:

{ id: 100, status: "paid" }

Then let's query against it using our custom functions:

var content = dbContext.Content.WHERE(o => CustomFunctions.JsonValue(o.Value, "$.status") == "paid");

You can apply the custom function logic to any built-in SQL functions, UDFs (user-defined functions) or even CLR functions.