BlazarTech.QueryableValues.SqlServer 7.3.0

There is a newer version of this package available.
See the version list below for details.
dotnet add package BlazarTech.QueryableValues.SqlServer --version 7.3.0
NuGet\Install-Package BlazarTech.QueryableValues.SqlServer -Version 7.3.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="BlazarTech.QueryableValues.SqlServer" Version="7.3.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add BlazarTech.QueryableValues.SqlServer --version 7.3.0
#r "nuget: BlazarTech.QueryableValues.SqlServer, 7.3.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install BlazarTech.QueryableValues.SqlServer as a Cake Addin
#addin nuget:?package=BlazarTech.QueryableValues.SqlServer&version=7.3.0

// Install BlazarTech.QueryableValues.SqlServer as a Cake Tool
#tool nuget:?package=BlazarTech.QueryableValues.SqlServer&version=7.3.0

QueryableValues

MIT License GitHub Stars Nuget Downloads

🤔💭 TLDR; By using QueryableValues, you can incorporate in-memory collections into your EF queries with outstanding performance and flexibility.

This library allows you to efficiently compose an IEnumerable<T> in your Entity Framework Core queries when using the SQL Server Database Provider. You can accomplish this by using the AsQueryableValues extension method that's available on the DbContext class. The query is processed in a single round trip to the server, in a way that preserves its execution plan, even when the values within the IEnumerable<T> are changed on subsequent executions.

Highlights

  • ✨ Enables the composition of in-memory data within your queries, utilizing both simple and complex types.
  • 👌 Works with all versions of SQL Server supported by Entity Framework Core.
  • ⚡ Automatically uses the most efficient strategy compatible with your SQL Server instance and configuration.
  • ✅ Boasts over 140 tests for reliability and compatibility, giving you added confidence.

For a detailed explanation of the problem solved by QueryableValues, please continue reading here.

💡 Still on Entity Framework 6 (non-core)? Then QueryableValues EF6 Edition is what you need.

When Should You Use It?

The AsQueryableValues extension method is intended for queries that are dependent upon a non-constant sequence of external values. It provides a solution to the following EF Core issue and enables other currently unsupported scenarios; like the ability to efficiently create joins with in-memory data.

Your Support is Appreciated!

If you feel that this solution has provided you some value, please consider buying me a ☕.

Buy me a coffee

Your ⭐ on this repository also helps! Thanks! 🖖🙂

Getting Started

Installation

QueryableValues is distributed as a NuGet Package. The major version number of this library is aligned with the version of Entity Framework Core by which it's supported (e.g. If you are using EF Core 5, then you must use version 5 of QueryableValues).

Configuration

Look for the place in your code where you are setting up your DbContext and calling the UseSqlServer extension method, then use a lambda expression to access the SqlServerDbContextOptionsBuilder provided by it. It is on this builder that you must call the UseQueryableValues extension method as shown in the following simplified examples:

When using the OnConfiguring method inside your DbContext:

using BlazarTech.QueryableValues;

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            "MyConnectionString",
            sqlServerOptionsBuilder =>
            {
                sqlServerOptionsBuilder.UseQueryableValues();
            }
        );
    }
}

When setting up the DbContext at registration time using dependency injection:

using BlazarTech.QueryableValues;

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<MyDbContext>(optionsBuilder => {
            optionsBuilder.UseSqlServer(
                "MyConnectionString",
                sqlServerOptionsBuilder =>
                {
                    sqlServerOptionsBuilder.UseQueryableValues();
                }
            );
        });
    }
}

💡 UseQueryableValues offers an optional options delegate for additional configurations.

How Do You Use It?

The AsQueryableValues extension method is provided by the BlazarTech.QueryableValues namespace; therefore, you must add the following using directive to your source code file for it to appear as a method of your DbContext instance:

using BlazarTech.QueryableValues;

💡 If you access your DbContext via an interface, you can also make the AsQueryableValues extension methods available on it by inheriting from the IQueryableValuesEnabledDbContext interface.

Below are a few examples composing a query using the values provided by an IEnumerable<T>.

Simple Type Examples

💡 Supports Byte, Int16, Int32, Int64, Decimal, Single, Double, DateTime, DateTimeOffset, Guid, Char, and String.

Using the Contains LINQ method:

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

// Example #1 (LINQ method syntax)
var myQuery1 = dbContext.MyEntities
    .Where(i => dbContext
        .AsQueryableValues(values)
        .Contains(i.MyEntityID)
    )
    .Select(i => new
    {
        i.MyEntityID,
        i.PropA
    });

// Example #2 (LINQ query syntax)
var myQuery2 = 
    from i in dbContext.MyEntities
    where dbContext
        .AsQueryableValues(values)
        .Contains(i.MyEntityID)
    select new
    {
        i.MyEntityID,
        i.PropA
    };

Using the Join LINQ method:

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

// Example #1 (LINQ method syntax)
var myQuery1 = dbContext.MyEntities
    .Join(
        dbContext.AsQueryableValues(values),
        i => i.MyEntityID,
        v => v,
        (i, v) => new
        {
            i.MyEntityID,
            i.PropA
        }
    );

// Example #2 (LINQ query syntax)
var myQuery2 = 
    from i in dbContext.MyEntities
    join v in dbContext.AsQueryableValues(values) on i.MyEntityID equals v 
    select new
    {
        i.MyEntityID,
        i.PropA
    };

Complex Type Example

💡 Must be an anonymous or user-defined type with one or more simple type properties, including Boolean.

// Performance Tip:
// If your IEnumerable<T> item type (T) has many properties, project only 
// the ones you need to a new variable and use it in your query.
var projectedItems = items.Select(i => new { i.CategoryId, i.ColorName });

// Example #1 (LINQ method syntax)
var myQuery1 = dbContext.Product
    .Join(
        dbContext.AsQueryableValues(projectedItems),
        p => new { p.CategoryId, p.ColorName },
        pi => new { pi.CategoryId, pi.ColorName },
        (p, pi) => new
        {
            p.ProductId,
            p.Description
        }
    );

// Example #2 (LINQ query syntax)
var myQuery2 = 
    from p in dbContext.Product
    join pi in dbContext.AsQueryableValues(projectedItems) on new { p.CategoryId, p.ColorName } equals new { pi.CategoryId, pi.ColorName }
    select new
    {
        p.ProductId,
        p.Description
    };

About Complex Types

⚠️ All the data provided by this type is transmitted to the server; therefore, ensure that it only contains the properties you need for your query. Not following this recommendation will degrade the query's performance.

⚠️ There is a limit of up to 10 properties for any given simple type (e.g. cannot have more than 10 Int32 properties). Exceeding that limit will cause an exception and may also suggest that you should rethink your strategy.

Do You Want To Know More? 📚

Please take a look at the repository.

Product Compatible and additional computed target framework versions.
.NET net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
8.1.1 7,618 2/8/2024
8.1.0 6,997 12/7/2023
8.0.0 1,601 11/26/2023
7.4.3 473 2/8/2024
7.4.2 1,065 12/7/2023
7.4.1 5,831 11/26/2023
7.4.0 33,077 6/25/2023
7.3.0 3,447 5/20/2023
7.2.0 3,622 3/27/2023
7.1.0 2,273 3/14/2023
7.0.0 14,961 11/13/2022
7.0.0-preview.2 163 9/3/2022
7.0.0-preview.1 129 8/7/2022
6.9.3 150 2/8/2024
6.9.2 2,132 12/7/2023
6.9.1 118 11/26/2023
6.9.0 7,569 6/25/2023
6.8.0 85,473 5/20/2023
6.7.0 2,529 3/27/2023
6.6.0 557 3/14/2023
6.5.0 77,665 9/3/2022
6.4.0 4,656 7/14/2022
6.3.0 11,491 1/4/2022
6.2.0 323 12/27/2021
6.1.0 299 12/9/2021
6.0.0 982 12/6/2021
5.9.3 84 2/8/2024
5.9.2 120 12/7/2023
5.9.1 106 11/26/2023
5.9.0 140 6/25/2023
5.8.0 147 5/20/2023
5.7.0 201 3/27/2023
5.6.0 273 3/14/2023
5.5.0 1,014 9/3/2022
5.4.0 917 7/14/2022
5.3.0 28,497 1/4/2022
5.2.0 300 12/27/2021
5.1.0 319 12/9/2021
5.0.0 311 12/6/2021
3.9.3 70 2/8/2024
3.9.2 95 12/7/2023
3.9.1 88 11/26/2023
3.9.0 171 6/25/2023
3.8.0 142 5/20/2023
3.7.0 227 3/27/2023
3.6.0 223 3/14/2023
3.5.0 432 9/3/2022
3.4.0 438 7/14/2022
3.3.0 1,388 1/4/2022
3.2.0 267 12/27/2021
3.1.0 297 12/9/2021
3.0.0 379 12/6/2021