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.

blazartech.queryablevalues's Issues

ArgumentException when writing strings with control characters

The XML 1.0 spec which is used by the underline XmlWriter does not allow some control characters and may be others.

I don't think that the current runtime exception provides a good UX.

Some options:

• Translate these invalid XML characters to a common placeholder.
• Remove these invalid XML characters from the string.

I believe this is a corner case though but is still a limitation because these characters can be stored using the varchar and nvarchar data types in SQL Server.

Relevant info: https://stackoverflow.com/a/28152666/2206145

Better execution plans

It has caught my attention that the use of the OPENJSON function is causing performance regressions in EF8, as reported in dotnet/efcore#32394. This seems to be caused by the opaque nature of the function to the query engine, which could result in suboptimal execution plans.

QueryableValues currently depends on both OPENJSON and XML methods for functionality, and thus, it is impacted to some degree by these issues.

Here are some potential solutions to address this problem:

• For large collections, project the deserialized values to a temp table and use the temp table instead. This should enable statistics, which in turn should generate optimal execution plans. However, it may cause execution plan re-compilations and result in higher latency.
• For small collections, avoid the use of deserialization functions and instead switch to a multi-parameter approach. This approach should generate optimal execution plans and maintain low latency.

These two approaches aim to provide the query engine with sufficient information to generate optimal execution plans, which is a priority. Consequently, this behavior should be enabled by default, with an option for users to opt out if desired.

Incompatibility with Microsoft.IO.RecyclableMemoryStream 3.0.0

On .NET 8, using latest non-preview version (8.1.0) says it depends on
Microsoft.IO.RecyclableMemoryStream >= 2.3.2
which allows 3.0.0. However, when run in Json mode, you end up with

System.MissingMethodException: Method not found: 'System.IO.MemoryStream Microsoft.IO.RecyclableMemoryStreamManager.GetStream()'.
   at BlazarTech.QueryableValues.Serializers.JsonSerializer.<Serialize>g__GetJson|2_2[T](IEnumerable`1 values, Action`2 writeValue, Func`2 mustSkipValue)
   at BlazarTech.QueryableValues.Serializers.JsonSerializer.Serialize[T](IEnumerable`1 values, IReadOnlyList`1 propertyMappings)
   at BlazarTech.QueryableValues.DeferredValues`3.ToString(IFormatProvider provider)

with a csproj with

<PackageReference Include="Microsoft.IO.RecyclableMemoryStream" Version="3.0.0" />

in it. Unfortunately I have another dependency that requires Microsoft.IO.RecyclableMemoryStream >= 3.0.0 so I can't revert to 2.3.x, so I'll have to drop AsQueryableValues for now. Figured I'd let you know so you can consume latest or constrain that dependency at least!

Should this library be creating tables in a migration?

I just noticed in my last EF Core migration it picked up these table properties not sure why but didn't see any documentation if this is required

I have multiple contexts that are registering UseQueryableValues

Of the 9 contexts, when I generate migrations - one of them (ConfigurationContext) - is creating tables that the others are not

Startup.cs

            builder.Services.AddDbContext<AccumulationContext>(options =>
                options.UseSqlServer(appSettings.ConnectionStrings.Accumulation,
                    sqlServerOptionsBuilder => { sqlServerOptionsBuilder.UseQueryableValues(); }));

            builder.Services.AddDbContext<ConfigurationContext>(options =>
                options.UseSqlServer(appSettings.ConnectionStrings.Configuration,
                    sqlServerOptionsBuilder => { sqlServerOptionsBuilder.UseQueryableValues(); }));

            builder.Services.AddDbContext<DispenseCaptureContext>(options =>
                options.UseSqlServer(appSettings.ConnectionStrings.DispenseCapture,
                    sqlServerOptionsBuilder => { sqlServerOptionsBuilder.UseQueryableValues(); }));

            etc.

ConfigurationContextModelSnapshot.cs

        protected override void BuildModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("ProductVersion", "6.0.4")
                .HasAnnotation("Relational:MaxIdentifierLength", 128);

            SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder, 1L, 1);

            modelBuilder.Entity("BlazarTech.QueryableValues.QueryableValuesEntity", b =>
                {
                    b.Property<bool?>("Bool0")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool1")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool2")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool3")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool4")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool5")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool6")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool7")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool8")
                        .HasColumnType("bit");

                    b.Property<bool?>("Bool9")
                        .HasColumnType("bit");

                    b.Property<byte?>("Byte0")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte1")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte2")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte3")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte4")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte5")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte6")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte7")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte8")
                        .HasColumnType("tinyint");

                    b.Property<byte?>("Byte9")
                        .HasColumnType("tinyint");

                    b.Property<string>("Char0")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char1")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char2")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char3")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char4")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char5")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char6")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char7")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char8")
                        .HasColumnType("nvarchar(1)");

                    b.Property<string>("Char9")
                        .HasColumnType("nvarchar(1)");

                    b.Property<DateTime?>("DateTime0")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime1")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime2")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime3")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime4")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime5")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime6")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime7")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime8")
                        .HasColumnType("datetime2");

                    b.Property<DateTime?>("DateTime9")
                        .HasColumnType("datetime2");

                    b.Property<DateTimeOffset?>("DateTimeOffset0")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset1")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset2")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset3")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset4")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset5")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset6")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset7")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset8")
                        .HasColumnType("datetimeoffset");

                    b.Property<DateTimeOffset?>("DateTimeOffset9")
                        .HasColumnType("datetimeoffset");

                    b.Property<decimal?>("Decimal0")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal1")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal2")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal3")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal4")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal5")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal6")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal7")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal8")
                        .HasColumnType("decimal(18,6)");

                    b.Property<decimal?>("Decimal9")
                        .HasColumnType("decimal(18,6)");

                    b.Property<double?>("Double0")
                        .HasColumnType("float");

                    b.Property<double?>("Double1")
                        .HasColumnType("float");

                    b.Property<double?>("Double2")
                        .HasColumnType("float");

                    b.Property<double?>("Double3")
                        .HasColumnType("float");

                    b.Property<double?>("Double4")
                        .HasColumnType("float");

                    b.Property<double?>("Double5")
                        .HasColumnType("float");

                    b.Property<double?>("Double6")
                        .HasColumnType("float");

                    b.Property<double?>("Double7")
                        .HasColumnType("float");

                    b.Property<double?>("Double8")
                        .HasColumnType("float");

                    b.Property<double?>("Double9")
                        .HasColumnType("float");

                    b.Property<float?>("Float0")
                        .HasColumnType("real");

                    b.Property<float?>("Float1")
                        .HasColumnType("real");

                    b.Property<float?>("Float2")
                        .HasColumnType("real");

                    b.Property<float?>("Float3")
                        .HasColumnType("real");

                    b.Property<float?>("Float4")
                        .HasColumnType("real");

                    b.Property<float?>("Float5")
                        .HasColumnType("real");

                    b.Property<float?>("Float6")
                        .HasColumnType("real");

                    b.Property<float?>("Float7")
                        .HasColumnType("real");

                    b.Property<float?>("Float8")
                        .HasColumnType("real");

                    b.Property<float?>("Float9")
                        .HasColumnType("real");

                    b.Property<Guid?>("Guid0")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid1")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid2")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid3")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid4")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid5")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid6")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid7")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid8")
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid?>("Guid9")
                        .HasColumnType("uniqueidentifier");

                    b.Property<int?>("Int0")
                        .HasColumnType("int");

                    b.Property<int?>("Int1")
                        .HasColumnType("int");

                    b.Property<int?>("Int2")
                        .HasColumnType("int");

                    b.Property<int?>("Int3")
                        .HasColumnType("int");

                    b.Property<int?>("Int4")
                        .HasColumnType("int");

                    b.Property<int?>("Int5")
                        .HasColumnType("int");

                    b.Property<int?>("Int6")
                        .HasColumnType("int");

                    b.Property<int?>("Int7")
                        .HasColumnType("int");

                    b.Property<int?>("Int8")
                        .HasColumnType("int");

                    b.Property<int?>("Int9")
                        .HasColumnType("int");

                    b.Property<long?>("Long0")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long1")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long2")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long3")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long4")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long5")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long6")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long7")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long8")
                        .HasColumnType("bigint");

                    b.Property<long?>("Long9")
                        .HasColumnType("bigint");

                    b.Property<short?>("Short0")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short1")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short2")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short3")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short4")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short5")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short6")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short7")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short8")
                        .HasColumnType("smallint");

                    b.Property<short?>("Short9")
                        .HasColumnType("smallint");

                    b.Property<string>("String0")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String1")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String2")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String3")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String4")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String5")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String6")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String7")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String8")
                        .HasColumnType("nvarchar(max)");

                    b.Property<string>("String9")
                        .HasColumnType("nvarchar(max)");

                    b.ToTable("QueryableValuesEntity");

                    b.ToView("QueryableValuesEntity");
                });

Json Serialization Support

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:

• SQL Server 2016 or higher.
• Database compatibility mode 130 or higher.

Configuration API Proposal

...
sqlServerOptionsBuilder.
    UseQueryableValues(options => options.UseJson(Auto|Always|Never));
...

The generic AsQueryableValues throws if options are provided..

Hi,

Thanks for the great work on this library. I just tested the latest release with Json serialization and performance improvements are great!

I'm working on some extensions so I can support the in-memory provider (details here), and I noticed a small issue. I'm unable to use properly the generic overload.

This works

using var dbContext = new AppDbContextQueryableJSON();
var names = new string[] { "abc", "def" };
var query = dbContext.Customers.Where(x => dbContext.AsQueryableValues(names, true).Contains(x.Name));
var result = await query.ToListAsync();

This fails

using var dbContext = new AppDbContextQueryableJSON();
var names = new string[] { "abc", "def" };
var query = dbContext.Customers.Where(x => dbContext.AsQueryableValues(names, opt => opt.DefaultForIsUnicode(true)).Contains(x.Name));
var result = await query.ToListAsync();

Stack trace:

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression '__8__locals2_dbContext_0
    .AsQueryableValues(
        values: __names_1, 
        configure: opt => opt.DefaultForIsUnicode(True))
    .Contains(NavigationTreeExpression
        Value: EntityReference: Customer
        Expression: c.Name)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandNavigationsForSource(NavigationExpansionExpression source, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessLambdaExpression(NavigationExpansionExpression source, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessWhere(NavigationExpansionExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()

Any feedback is appreciated.

Support for DateOnly/TimeOnly?

Will be available on v8.x only.

Naming for Entity Framework 7 and above

Avoid using EF Core when referring to Entity Framework 7 (EF7) and above per https://devblogs.microsoft.com/dotnet/announcing-ef7-rc1/#ef7-prerequisites

Missing types

Add support for:

• System.Byte
• System.Int16
• System.Single
• System.Boolean (on complex type only)
• System.Char

Enum Support

I've a service that builds a dynamic query WHERE condition based on some filters.
I'm trying to use your library to build one of those filter, related to an array of enum values, but I'm getting this Exception at runtime:

The type MyProject.Api.Data.Models.OperationType must have at least one public property.

The user can select one or more of those values and so the WHERE condition has to be built using OR.

The MyProject.Api.Data.Models.OperationType is an enum type defined as follows:

public enum OperationType : byte
{
    DT = 1,
    NP = 2,
    Tx = 3,
    RE = 4,
    I = 5,
    AT = 6,
    M = 7,
    TR = 8,
    TxAT = 9
}

My actual code for building the query is this:

...
if (filter.OperationTypes?.Any() == true)
{
    // Conversion from API enum model to EF enum model
    var operationTypes = filter.OperationTypes.Select(c => Enum.Parse<Data.Models.OperationType>(c.ToString(), true)).ToArray();
	
    // Call to AsQueryableValues that causes the Exception
    var queryableValues = _dbContext.AsQueryableValues(operationTypes);

    query = query.Where(x => x.Movements.OperationType != null && queryableValues.Contains(x.Movements.OperationType.Value));
}
...

Am I doing something wrong? How am I supposed to use the library with enums?

EFCore InternalServiceProvider is not supported

Background

QueryableValues are not working when using EFCore InternalServiceProvider due to missing service registration in the DI container.

Unable to resolve service for type 'BlazarTech.QueryableValues.IQueryableFactory'.
This is often because no database provider has been configured for this DbContext.
A provider can be configured by overriding the 'DbContext.OnConfiguring' method
or by using 'AddDbContext' on the application service provider.
If 'AddDbContext' is used, then also ensure that your DbContext type accepts a DbContextOptions<TContext> object in its constructor and passes it to the base constructor for DbContext.

EF Core registration

DI registration of EF is given this way:

services.AddDbContext<TestDbContext>((provider, builder) =>
        {
            builder.UseSqlServer(connectionString, opt =>
            {
                opt.UseQueryableValues();
            });
            builder.UseInternalServiceProvider(EntityFrameworkServices.Build(provider));
        });

public static class EntityFrameworkServices
{
    private static IServiceProvider _efServices;

    public static IServiceProvider Build(IServiceProvider serviceProvider)
    {
        if (_efServices != null)
            return _efServices;

        var efServices = new ServiceCollection()
            .AddEntityFrameworkSqlServer();

        return _efServices = efServices.BuildServiceProvider();
    }
}

Test environment

.NET 6 runtime

<PackageReference Include="BlazarTech.QueryableValues.SqlServer" Version="6.5.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.5" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.5" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.5" />

Initial Investigation

Looks like the method QueryableValuesSqlServerExtension.ApplyServices is called on the instance of IServiceCollection that is not really taking part in the creation of the ServiceProvider since the instance of the ServiceProvider has already been built manually.

QueryableValues fails when joining against a query that uses the Include method

As reported in #30 (comment)

When materializing the query, it fails with a System.InvalidOperationException exception:

Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.

Is there a way to compose a query that uses a condition of contains in two lists (AND not OR)?

Hey @yv989c question for you,

We have a service that is querying to find packages that contain items from list 1 and list 2,

This ends up pulling back packages that satisfy list 1 or list 2,

Is there a way to write this so we get a list of packages back that contain both using the queryable values? (They can be large lists)

Thanks!

    public async Task<List<Package>> GetPackagesAsync(List<int> list1, List<string> list2)
    {
        return await _context.Package
            .Where(p => _context
                .AsQueryableValues(list1)
                .Contains(p.p1))
            .Where(p => _context
                .AsQueryableValues(list2, true)
                .Contains(p.p2))
            .ToListAsync();
    }

Joining entities with table

I frequently have a list of entities that I want to "join" with a database table.

I combined this project with LinqKit and created https://gist.github.com/andrewkittredge/ce322944caf5781eb7f4efade5c1266e.

I am interested in you opinion. Is there a better way?

Does/Can this work with enum values?

I notice there's no overloads specifically for them, so i tried the generic overload

public static IQueryable AsQueryableValues(this DbContext dbContext, IEnumerable values, Action<EntityOptionsBuilder>? configure = null) where TSource : notnull

but that resulted in an exception.

Is this a supported scenario already?

Non-intrusive Mode

Description

Automatically treats IEnumerable<T> types composed in a LINQ expression as if they were provided via the AsQueryableValues method. I'm assuming that the direct use of the IEnumerable<T> type is likely to have a non-constant sequence of values.

There's also the legitime case of not wanting to use QueryableValues. Like having a small list of constant values that I want hardcoded in the T-SQL, so I can use T[] or List<T> for these.

Some desired attributes:

• Must be opt-in via configuration.
• Must allow the registration of other types that also implement IEnumerable<T> so they can also be treated this way per user needs.

Examples

With non-intrusive mode On, the following two queries will use QueryableValues (in both cases the values will be parameterized in the T-SQL query):

IEnumerable<int> values = Enumerable.Range(1, 10);

var myQuery1 = 
    from i in dbContext.MyEntities
    where dbContext
        .AsQueryableValues(values)
        .Contains(i.MyEntityID)
    select new
    {
        i.MyEntityID,
        i.PropA
    };

var myQuery2 = 
    from i in dbContext.MyEntities
    where values.Contains(i.MyEntityID)
    select new
    {
        i.MyEntityID,
        i.PropA
    };

With non-intrusive mode On, the first query will use QueryableValues and the second will not (the values will be hardcoded in the T-SQL instead of being parameterized):

List<int> values = Enumerable.Range(1, 10).ToList();

var myQuery1 = 
    from i in dbContext.MyEntities
    where dbContext
        .AsQueryableValues(values)
        .Contains(i.MyEntityID)
    select new
    {
        i.MyEntityID,
        i.PropA
    };

var myQuery2 = 
    from i in dbContext.MyEntities
    where values.Contains(i.MyEntityID)
    select new
    {
        i.MyEntityID,
        i.PropA
    };

Motivation

Not having to introduce an alien method (AsQueryableValues) in our EF queries is better for portability.

Ideas

Maybe be something around rewriting the original LINQ expression at some point in the EF query processing pipeline. Find IEnumerable<T> and replace with AsQueryableValues(DbContext, IEnumerable<T>).

Unable to cast object of type 'BlazarTech.QueryableValues.DeferredInt32Values' to type 'System.String'.

Hi,

Started using this library, thank you for writing it!

When we have non-performant queries our production logging services try to record the query using EFCore's .ToQueryString() method. Once we add QueryableValues to the query however, any attempt to view the generated SQL fail with this exception. This fails for .ToQueryString() and within the debugger's DebugView.Query property as well.

take care
jasen

Question: Nullable columns

Hi,
I'm trying to convert a few of my existing queries to use your library and one of them is causing an issue.

I've got a Guid? column in my table that I'm attempting to do a Contains on.

Using .Value will throw an error because it causes client side evaluation.

I get the below error:

Error	CS1929	'IQueryable<Guid>' does not contain a definition for 'Contains' and the best extension method overload 'MemoryExtensions.Contains<Guid?>(ReadOnlySpan<Guid?>, Guid?)' requires a receiver of type 'System.ReadOnlySpan<System.Guid?>'

Is there any alternative/recommended approach?

Thanks in advance.

EDIT:

I think I've resolved it by using the Join function instead.
Thanks for the useful tool.

MySql support?

Hey,

just wanted to ask you if MySql is also supported, because I couldn´t find any information about it.

Entity Framework Core 8 (.NET 8) Support

Do you plan to release a version with native support of Entity Framework Core 8 ?

Thank you very much

EF.Property<T> throws exception with list used in AsQueryableValues() extension

EF.Property throws exception with list used in AsQueryableValues() extension,

Details:
EF Core Version: 8.0.1
Database Engine: SQL Server
Exception: "The EF.Property method may only be used within Entity Framework LINQ queries"
Note: Reverse engineering "scaffolding" was used to automatically generate the dbcontext

There're some cases where we cannot read properties directly and we need to use EF.Property instead, this extension does not work with AsQueryableValues list and throws the exception, I understand this exception is normally raised when client evaluation happens instead of server evaluation, so maybe when we use EF.Property along with AsQueryableValues it forces client evaluation for some reason, example with temporal tables below:

var modificationLogs = dbContext.Set<ObjectName>().TemporalAll().AsQueryable();
var properties = new List<string>() { "Name", "Code", "ValidFrom", "ValidTo" };
var query = from modificationLog1 in modificationLogs
            join modificationLog2 in modificationLogs on EF.Property<DateTime>(modificationLog1, "ValidTo") equals EF.Property<DateTime>(modificationLog2, "ValidFrom")
            from property in dbContext.AsQueryableValues(properties, true)
            select new ()
            {
                Date = EF.Property<DateTime>(modificationLog1, "ValidTo").ToString(),                     // this one works correctly,
                Field = property,                        // this one works correctly,
                From = EF.Property<object>(modificationLog1, "Name").ToString()                     // this one works correctly,
                // From = EF.Property<object>(modificationLog1, property).ToString()                     // this one "when enabled" does not work and throws the exception
            };

var items = await query.ToListAsync();                            // exception thrown here

Edit #1: Fixed Typo

Target net6.0 to take advantage of TryGetNonEnumeratedCount

Versions 3 and 5 of the library can benefit from the new TryGetNonEnumeratedCount available in .net 6.

It's used here:

BlazarTech.QueryableValues/src/QueryableValues.SqlServer/Extensions.cs

Line 19 in d942096

if (System.Linq.Enumerable.TryGetNonEnumeratedCount(source, out count))

Support for the In-Memory Database Provider

https://docs.microsoft.com/en-us/ef/core/providers/in-memory/
Will be useful in test scenarios.

ToQueryString does not work

I got an issue, while trying to read a query plain text

Min example:

using BlazarTech.QueryableValues;
using Microsoft.EntityFrameworkCore;

var dbContext = new ApplicationContext();
var codes = new[] { 1, 2 };

var query = dbContext.Docs
    .Where(x => dbContext.AsQueryableValues(codes).Contains(x.Id));

Console.WriteLine(query.ToQueryString());
public class doc
{
    public int Id { get; set; }
}
public class ApplicationContext : DbContext
{
    public DbSet<doc> Docs { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            "..........",
            o => o.UseQueryableValues());
    }
}

The example above throws an exception:

System.InvalidCastException: Unable to cast object of type 'BlazarTech.QueryableValues.DeferredInt32Values' to type 'System.String'.

Enviroment:
BlazarTech.QueryableValues 1.0.9
.net 7.0.202
ef core 7.0.4

Doesn't work with compiled models

If we use this with compiled models, the special types used behind the scenes breaks the build.

After rebuilding/regenerating our compiled models we got a generated model that references the internal types SimpleQueryableValuesEntity, ComplexQueryableValuesEntity and QueryableValuesEntity (and possibly some more that I didn't catch).

CompiledModels/ComplexQueryableValuesEntityEntityType.cs(3432,35): error CS0122: 'ComplexQueryableValuesEntity' is inaccessible due to its protection level

Got almost 400 variations of that error in different generated files. And since it breaks the build, I need to manually clean this up since rebuilding these files requires the project to build in the first place.

The simplest solution would be to just make these classes public, maybe put into a Internal namespace or something to signal that they shouldn't be used directly by the end user.

Possibility of using implicit version of OpenJson?

Currently the SQL generated by this library invokes OpenJson with explicit mappings, for example:

declare @p0 nvarchar(max) = '[{"X":0,"I":123},...,{"X:"99","I":987}]' // 100 integer values
declare @__p_1 int = 3000
declare @__p_2 int = 100

SELECT [r].[Id], [r].[Amount], [r.AccountId]
FROM [Transactions] AS [r]
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT TOP(@p1) [X], [I]
        FROM OPENJSON(@p0) WITH ([X] int, [I] int)
        ORDER BY [X]
    ) AS [b]
    WHERE [b].[I] = [r].[AccountId]) // AccountId is an integer
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

From my testing the above is slower than using the implicit mappings version:

declare @p0 nvarchar(max) = '[123,...,987]' // 100 integer values
declare @__p_1 int = 3000
declare @__p_2 int = 100

SELECT [r].[Id], [r].[Amount], [r.AccountId]
FROM [Transactions] AS [r]
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(@p0) AS [b]
    WHERE CONVERT(INT, [b].[value]) = [r].[AccountId])
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

because the former query incurs Sort and Top operations, versus only a Compute Scalar for the former. The latter also sends a smaller amount of data to the server in the JSON-serialised parameter.

What is the reason for using the explicit mappings version instead of implicit? And would you be willing to add functionality to control whether implicit or explicit is used?