Can Entity Framework (LINQ) select rows based on JSON where clause?

Question

I have a column in a SQL database table of JSON data. Can I use Entity Framework and LINQ to query and filter based on a field/value in the SQL JSON column?

I am using SQL 2016, VS 2017, EF Core 2.0, .NET Core 2.0.

Answer 1

Brian provides some links to a few good options. I think these all still require you to fully receive the SQL data, then apply filtering in .NET code; I'd really like to filter on the SQL server side of things and avoid pulling back all the rows, then filtering.

In addition, because my JSON data can have different properties for each row, filtering with SQL Server would be best.

I'm going to opt for using a SQL statement in LINQ per Brian's Code Project reference:

var blogs = _context.Blogs
  .FromSql<Blog>(@"SELECT * FROM Blogs WHERE JSON_VALUE(Owner, '$.Name') = {0}", Owner)
  .ToList();