SQL Server’s OpenJson function is a really nice way to expose data hidden in a SQL column as JSON. Without going into the ethical arguments I have found it very useful. Today I was faced with combining it with Entity Framework Code First, or to put it another way, how do you expose the OpenJson functionality to IQuerable Linq? Here is a quick workaround (read hack), use a view.
1. Create a View to wrap your JSON e.g.;
CREATE VIEW MyJsonView AS SELECT M.*, J.* FROM MySqlTable M CROSS APPLY OPENJSON(M.MyJson) WITH (Id int '$.Id', Name nvarchar(255) '$.Name' ) AS J
2. Jump through a few CF hoops; USING SQL VIEWS WITH ENTITY FRAMEWORK CODE FIRST
3. You can now access the JSON that’s exposed via the View.