search

yv989c / BlazarTech.QueryableValues

This library allows you to efficiently compose an IEnumerable<T> in your Entity Framework Core queries when using the SQL Server database provider.
Other
93 stars 6 forks source link

Json Serialization Support #19

Closed yv989c closed 1 year ago

yv989c commented 2 years ago

Background

As of now, QueryableValues relies on the XML capabilities of SQL Server in order to efficiently project the in-memory values in a T-SQL query. These capabilities are available in all supported versions of SQL Server to date.

JSON support was introduced in SQL Server 2016. Early benchmarks shows that JSON performs close to 1 order of magnitude faster than its XML counterpart, therefore, I'm considering providing support for it. This can be achieved via OPENJSON.

Caveats

OPENJSON is only available under the following conditions:

Configuration API Proposal

...
sqlServerOptionsBuilder.
    UseQueryableValues(options => options.UseJson(Auto|Always|Never));
...
willnationsdev commented 1 year ago

With EF Core 7 introducing built-in support for JSON operations, I have to wonder whether transitioning to using JSON operations might promote this library to be usable in a cross-compatible manner with other relational databases rather than being exclusive to SQL Server (since evidently, the core lib found ways of reproducing JSON-like behavior with all supported ones).

yv989c commented 1 year ago

Hi @willnationsdev, that's an interesting question. I created this library with the main goal of emitting efficient queries for SQL Server in a particular use case where EF currently does a poor job for this provider. It relies on technology that's specific to SQL Server to make it performant (behind the scenes it uses XML serialization and #19 is to also support JSON when possible, which performs better).

I saw that the JSON column support added to EF Core 7 now allows us to retrieve and manipulate JSON data on the server natively, but I haven't played with it to see if somehow I could leverage it in this library. This would be something I'd explore when implementing #19, but I doubt that it will be better than using the OPENJSON function.

Having said that, why are you interested on having this library support other providers? What QueryableValues feature are you interested in that currently EF doesn't support?