CODEWITHSUNDEEP
c#jsonsql-serverasp.net-core
chobo2
chobo2

Reputation: 85795

How to do a json query in C#?

I am using SQL Server 2017 which has Json support. However I am using EF core which cannot query json.

How am I to do queries then?

Example

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

How would I query this in C# / ASP.NET Core? ADO.NET?

Upvotes: 2

Views: 340

Answers (1)

Edward
Edward

Reputation: 29996

I assume your items is a model like below: 

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

If so and you do not need Year, you could try FromSql like below: 

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();

For this way, you could only return columns which is defined in Customer model.

If you want to return columns in Customer and Year column, I suggest you try Dapper like: 

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);                           
        }

With Dapper, define new model with Year column: 

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

Update

Avoid SQL inection.

Try passing SQL parameters. 

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();

Upvotes: 1

Related Questions