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.
IT Bytes 