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.

This article states that Sql Server can query JSON value column but LINQ cannot: codeproject.com/Articles/1166099/… — Brian Ogden
– Brian Ogden, Commented Aug 27, 2017 at 0:42
Brian, the reference on Code Project was helpful. Because my JSON can actually be different for each row, just addressing this with a T-SQL using .FromSql (leverage SQL JSON_VALUE) seems like the best choice. — paultechguy
– paultechguy, Commented Aug 27, 2017 at 13:12

paultechguy · Accepted Answer · 2017-08-27 13:17:40Z

9

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

answered Aug 27, 2017 at 13:17

paultechguy

2,5885 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jayasurya_j Over a year ago

Is there a way to check if an item exists in array for JSON data in SQL?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related