Offer JSON access via database

[JornWildt]

Assume a custom entity booking like this:

public class BookingDataModel : ICustomEntityDataModel
{
  public DateTime ArrivalDate { get; set; }
}

The above serialized data can be ordered by the arrival data using the following SQL query (which lacks multi-version handling, but otherwise works for me and my tests - even on SQL Server Express):

select *
from Cofoundry.CustomEntity e
join Cofoundry.CustomEntityVersion v
  on v.CustomEntityId = e.CustomEntityId
where e.CustomEntityDefinitionCode = 'BOOKNG'
ORDER BY JSON_VALUE(v.SerializedData, '$.arrivalDate') desc

The same idea can of course be applied for WHERE filters, not just ORDER BY expressions. See for instance https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

I think it should be fairly straight forward to convert C# interface stuff to SQL expressions like above.

Today I can create a paged query like this:

public class SearchBookingSummariesQuery
    : SimplePageableQuery
    , IQuery<PagedQueryResult<BookingSummary>>
{
}

Maybe, if I also implemented something like ISortableQuery, I could get my return data sorted by a list of the model data fields:

interface ISortableQueryField<TDataModel, TField>
{
  Expression<Func<TDataModel, TField>> Reference { get; }
}

By inspection of the expression it would be possible to do what ASP.NET does with HTML helpers and calculate the referenced field and then create the needed JSON_VALUE SQL expression.

Other ways to express the request is of course also possible.

[JornWildt]

Performance may not be fantastic for large data sets, but it seems like a better solution than to read everything into memory and then sort it.

[HeyJoel]

I'm interested in this. JSON querying has been mentioned before, but I can't seem to track down where (possibly lost in gitter). As you say, the performance might not be brilliant, but custom entities will always be a trade off between speed and convenience, and you can always build a custom index or progress to a bespoke domain model if necessary - I'm keen to make that easier too.

It's related to #12 and possible #31 so that needs to be considered.