Using SQL Server OpenJson with Code First Entity Framework

pauliom

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.

Leave a comment