Using SQL Server OpenJson with Code First Entity Framework

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.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s