EFCore使用JSON_VALUE查询json对象的值

• Intro

  SqlServer 从2016开始支持 JSON 操作，可以使用 JSON_VALUE 查询 JSON 对象的某个属性值，更多介绍，现在公司的一些项目主要是使用 EF Core，手写sql较少，针对比较简单的 JSON_VALUE 查询想通过 DbFunction 来实现，于是就有了这篇文章的探索。

  定义 JSON_VALUE DbFunction

      public static class DbFunctions
      {
          [DbFunction("JSON_VALUE", "")]
          public static string JsonValue(string column, [NotParameterized] string path)
          {
              throw new NotSupportedException();
          }
      }
  

  在 DbContext 中注册 DbFunction

  重写 DbContext 的 OnModelCreating 方法，在 OnModelCreating 方法中注册 DbFunction

      public class TestDbContext : DbContext
      {
          public TestDbContext(DbContextOptions<TestDbContext> options) : base(options)
          {
          }
  
          public DbSet<TestEntity> TestEntities { get; set; }
  
          protected override void OnModelCreating(ModelBuilder modelBuilder)
          {
              modelBuilder.HasDbFunction(() => DbFunctions.JsonValue(default(string), default(string)));
          }
      }
  
      public class TestEntity
      {
          [Key]
          [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
          public int Id { get; set; }
  
          public string Extra { get; set; }
  
          public DateTime CreatedAt { get; set; }
      }
  

  使用注册的 DbFunction 查询 JSON_VALUE

  数据库中添加了三条测试数据，测试数据如下：

  

  var loggerFactory = new LoggerFactory();
  loggerFactory.AddLog4Net();
  
  var optionsBuilder = new DbContextOptionsBuilder<TestDbContext>()
                  .UseLoggerFactory(loggerFactory)
                  .UseSqlServer("server=.;database=Test;Integrated Security=True");
  
  var db = new TestDbContext(optionsBuilder.Options);
  
  var names = db.TestEntities.AsNoTracking().Select(t => DbFunctions.JsonValue(t.Extra, "$.Name")).ToArray();
  

  监控生成的Sql语句

  我这里通过 log4net 记录执行的 sql 语句，监控到执行的sql语句如下：

  SELECT JSON_VALUE([t].[Extra], N'$.Name')
  FROM [TestEntities] AS [t]
  

  Source

  示例代码： https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/master/samples/WeihanLi.EntityFramework.Samples/Program.cs

  Reference

  • https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-2017
  • https://docs.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server?view=sql-server-2017
  • https://stackoverflow.com/questions/52017204/expression-tree-to-sql-with-ef-core
  • https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.0#database-scalar-function-mapping

   

   

  转 https://www.jianshu.com/p/34b77a0fba6d