如何在C#中进行json查询？

Question

我使用的是SQL Server 2017，它支持Json。然而，我使用的是EF内核，它不能查询json。

那我该怎么做查询呢？

示例

  SELECT
      *, JSON_VALUE (Attributes, '$.year') AS Year
  FROM 
      items
  WHERE 
      JSON_VALUE(Attributes, '$.year') = '2017'

如何在C# / ASP.NET核心中查询？ADO.NET？

Answer 1

我假设您的items是如下所示的模型：

    public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Attributes { get; set; }
}

如果是这样，并且您不需要Year，您可以尝试FromSql，如下所示：

string sQuery = "SELECT *, JSON_VALUE (Attributes, '$.year') AS Year FROM  Customer WHERE JSON_VALUE(Attributes, '$.year') = '2018'";               

var customerEF = await _context.Customer.FromSql(sQuery).ToListAsync();

这样，您只能返回在Customer模型中定义的列。

如果您想返回Customer和Year列中的列，我建议您尝试Dapper，如下所示：

using (var connection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
        {                
            string sQuery = "SELECT *, JSON_VALUE (Attributes, '$.year') AS Year FROM  Customer WHERE JSON_VALUE(Attributes, '$.year') = '2018'";
            var customerDapper = connection.QueryFirstOrDefault<CustomerVM>(sQuery);                           
        }

使用Dapper，使用Year列定义新模型：

    public class CustomerVM: Customer
{
    public string Year { get; set; }
}

更新

避免SQL内联。

尝试传递SQL参数。

string sQuery = "SELECT *, JSON_VALUE (Attributes, '$.year') AS Year FROM  Customer WHERE JSON_VALUE(Attributes, '$.year') = @Year";
var year = new SqlParameter("Year", "2018");
var customerEF = await _context.Customer.FromSql(sQuery, year).ToListAsync();