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

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s