OPENJSON function in OrmLite?

ServiceStack.OrmLite
1

In SQL Server 2016+, I have a table that has a JSON field that contains an array of Ids. (note I have told OrmLite to store in JSON not JSV)

I would like to do a query server side using the new JSON functions to find a match, whereas currently now I am pulling the entire dataset onto the server and then doing a Contains with Linq.

image.png1800×512 56.2 KB

The query I would like to do is:

 SELECT * from Media
 where 3 in (select value from OPENJSON(Media.Regions, '$'))

Which will return:

image.png1766×110 13.3 KB

Is this possible in OrmLite or must the SQL be written manually? Or is there a different way to go about this?

2

Without having to resort to manual sql as I much prefer strongly typed queries.

This works, but does not survive refactoring or table changes:

var query = db.Select<Media>($"SELECT * FROM MEDIA WHERE {regionId} in (SELECT value FROM OPENJSON(Media.Regions, '$'))");
3

You can use the Table<T> and Column<T> API’s in Custom SQL Fragments for referencing Typed names, e.g:

var q = db.From<Media>();
q.Where($$"{q.Column<Media>(x => x.RegionId)} IN (SELECT value from OPENJSON({q.Column<Media>(x => x.Regions, tablePrefix:true)}, '$$'))");

var results = db.Select(q.Select("*"));

This makes use of any naming conventions or aliases on the properties, if you don’t have any you can use nameof instead:

var q = db.From<Media>()
  .Where($$"{nameof(Media.RegionId)} IN (SELECT value from OPENJSON({nameof(Media)}.{nameof(Media.Regions}}, '$$'))")
  .Select("*");

var results = db.Select(q);
4

Excellent suggestions.