Maintenance Release

What's Changed

• Fix for #15
• Minor enhancements and fixes
• Docs

Maintenance Release

Includes fix for #12.

Maintenance Release

Includes fix for #9.

A special thanks to @michlns for this contribution! 🙌

Fast & Furious (JSON Support)

Summary

QueryableValues achieves its performance by relying on parameterized T-SQL queries. This is achieved by taking advantage of the XML data type support in SQL Server, which is available in all the supported versions of SQL Server to date. JSON support was introduced in SQL Server 2016, and it provides better query performance when compared to its XML counterpart.

This version adds support for JSON as an alternative to XML. When available, it provides a significant performance boost on certain scenarios as shown in the benchmarks below.

JSON support is available when the following is true:

• The SQL Server instance is 2016 and above.
• The database has its compatibility level set to 130 or higher.

Configuration

By default, this version of QueryableValues automatically detects if JSON can be used, otherwise it falls back to XML. This behavior can be configured via the QueryableValuesConfigurator class:

QueryableValuesConfigurator
    .Configure()
    .Serialization(SerializationOptions.Auto);

The Configure method also allows you to specify the configuration on a per DbContext fashion:

QueryableValuesConfigurator
    .Configure<MyDbContext>()
    .Serialization(SerializationOptions.Auto);

You may want to use SerializationOptions.UseJson if you know that your environment does support JSON, or SerializationOptions.UseXml if it does not. By not using SerializationOptions.Auto you can avoid a one-time initial roundtrip that is done to detect JSON support (this only happens once per connection string).

Benchmarks

These benchmarks are using XML as the baseline for the Int32, String, and Guid data types. They consist of a query that does a JOIN between a table and a set of values via the AsQueryableValues extension method. The benchmarks can be found in the benchmarks directory of this repository.

The most dramatic performance gains can be observed on the Int32 data type, particularly between 1024 and 16384 values. At 16384 we can see that JSON is up to 13.25x faster than XML.

System

BenchmarkDotNet=v0.13.2, OS=Windows 11 (10.0.22621.521)
AMD Ryzen 9 6900HS Creator Edition, 1 CPU, 16 logical and 8 physical cores
.NET SDK=6.0.400
  [Host]     : .NET 6.0.8 (6.0.822.36306), X64 RyuJIT AVX2
  Job-CHWPLZ : .NET 6.0.8 (6.0.822.36306), X64 RyuJIT AVX2

Runtime=.NET 6.0  Server=True  InvocationCount=12  
IterationCount=10  RunStrategy=Monitoring  UnrollFactor=1  
WarmupCount=1  

SQL Server Instance (running on the same computer)

Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64)   Jul 19 2021 15:37:34   Copyright (C) 2019 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22621: ) (Hypervisor) 

Serializer	Type	No. of Values	Mean	Error	StdDev	Ratio	RatioSD
XML	Int32	2	898.4 μs	309.83 μs	204.93 μs	1.00	0.00
JSON	Int32	2	727.2 μs	95.11 μs	62.91 μs	0.84	0.16
XML	Int32	4	864.1 μs	67.88 μs	44.90 μs	1.00	0.00
JSON	Int32	4	729.5 μs	56.51 μs	37.38 μs	0.85	0.07
XML	Int32	8	897.2 μs	68.45 μs	45.27 μs	1.00	0.00
JSON	Int32	8	733.9 μs	46.96 μs	31.06 μs	0.82	0.06
XML	Int32	16	1,031.7 μs	358.94 μs	237.41 μs	1.00	0.00
JSON	Int32	16	725.5 μs	34.74 μs	22.98 μs	0.73	0.12
XML	Int32	32	1,042.6 μs	52.87 μs	34.97 μs	1.00	0.00
JSON	Int32	32	732.8 μs	47.17 μs	31.20 μs	0.70	0.04
XML	Int32	64	1,295.3 μs	81.40 μs	53.84 μs	1.00	0.00
JSON	Int32	64	736.5 μs	47.40 μs	31.35 μs	0.57	0.03
XML	Int32	128	1,845.9 μs	408.16 μs	269.98 μs	1.00	0.00
JSON	Int32	128	785.5 μs	54.61 μs	36.12 μs	0.43	0.04
XML	Int32	256	2,771.4 μs	370.15 μs	244.83 μs	1.00	0.00
JSON	Int32	256	814.3 μs	46.68 μs	30.87 μs	0.30	0.03
XML	Int32	512	4,887.4 μs	491.57 μs	325.14 μs	1.00	0.00
JSON	Int32	512	1,038.1 μs	371.97 μs	246.03 μs	0.21	0.06
XML	Int32	1024	8,213.6 μs	430.26 μs	284.59 μs	1.00	0.00
JSON	Int32	1024	1,276.8 μs	72.12 μs	47.70 μs	0.16	0.01
XML	Int32	2048	14,809.4 μs	420.00 μs	277.81 μs	1.00	0.00
JSON	Int32	2048	1,817.0 μs	93.85 μs	62.08 μs	0.12	0.00
XML	Int32	4096	27,859.6 μs	224.83 μs	148.71 μs	1.00	0.00
JSON	Int32	4096	2,836.3 μs	282.18 μs	186.65 μs	0.10	0.01
XML	Int32	8192	53,160.4 μs	623.33 μs	412.29 μs	1.00	0.00
JSON	Int32	8192	4,769.1 μs	383.36 μs	253.57 μs	0.09	0.00
XML	Int32	16384	106,420.7 μs	624.87 μs	413.31 μs	1.00	0.00
JSON	Int32	16384	8,028.5 μs	439.35 μs	290.60 μs	0.08	0.00
XML	Int32	32768	211,579.2 μs	4,253.48 μs	2,813.41 μs	1.00	0.00
JSON	Int32	32768	45,521.2 μs	571.41 μs	377.95 μs	0.22	0.00
XML	Int32	65536	415,989.6 μs	2,353.38 μs	1,556.62 μs	1.00	0.00
JSON	Int32	65536	85,472.2 μs	696.38 μs	460.61 μs	0.21	0.00
XML	Int32	131072	827,467.1 μs	2,622.26 μs	1,734.46 μs	1.00	0.00
JSON	Int32	131072	172,388.8 μs	2,185.62 μs	1,445.65 μs	0.21	0.00
XML	String	2	838.1 μs	38.12 μs	25.21 μs	1.00	0.00
JSON	String	2	781.2 μs	107.94 μs	71.39 μs	0.93	0.10
XML	String	4	850.8 μs	44.99 μs	29.76 μs	1.00	0.00
JSON	String	4	740.0 μs	35.80 μs	23.68 μs	0.87	0.04
XML	String	8	855.5 μs	54.26 μs	35.89 μs	1.00	0.00
JSON	String	8	766.6 μs	47.56 μs	31.45 μs	0.90	0.05
...

Small Refactor

Highlights

• Moved IQueryableValuesEnabledDbContext extension methods to their own class.
  • This provides flexibility when deciding what extensions method to make available via the using directive (e.g. using static ...).
• Minor tweaks to the docs.

✌

Ready for the world!

Highlights

• Works with all versions of EF6 (non-core) to date (v6.0-6.4).
• Targets .NET 4.5.2, .NET 4.7.2, .NET Standard 2.1, and .NET 6.0.
• Many integration tests (over 2800 test cases) for high reliability.
• Works with the Database First and Code First strategies.
• It supports the following types: Byte, Int16, Int32, Int64, Guid, and String.
• Uses caching strategies to reduce CPU usage and memory allocations.
• The internal serializer is further optimized when running on .NET 6.
• All the public APIs are documented.

A special thanks to @jacobsmidt.

✌

RC 2!

Second release candidate of QueryableValues for EF6.

RC 1!

First release candidate of QueryableValues for EF6.