Deftflux.EntityFrameworkCore.SqlServer.Extensions 10.0.0.19

Deftflux.EntityFrameworkCore.SqlServer.Extensions

Load in-memory collections into SQL Server session temp tables and query them as IQueryable<T> with Entity Framework Core. Filter, join, and use Contains against your DbSet data in a single database round trip—without huge IN parameter lists or hand-written SqlBulkCopy code.

Description

This package adds two extension methods on IEnumerable<T>:

• AsQueryableTempTableAsync — recommended for async apps (ASP.NET Core, services)
• AsQueryableTempTable — synchronous overload

Each call creates a #TempTable_{guid} on the same connection as your DbContext, loads your values, and returns an IQueryable<T> backed by SqlQueryRaw. Compose that queryable with normal LINQ on your entities (Where, Join, Contains, Any, and so on); EF Core translates it to SQL that references the temp table.

Use it when you already have data in memory (API payloads, cache, imports, prior query results) and need to query SQL Server against that set.

Typical scenarios

• Thousands of IDs from a client → filter DbSet rows
• Multi-column filter DTOs → join to OrderLines, Products, etc.
• Allowed status codes, SKUs, or region IDs → Contains in LINQ
• Select-list or lookup payloads → Any / join against reference data

Requirements

NuGet selects the correct assembly for your project’s TargetFramework. A .NET 8 app pulls the net8.0 build (EF Core 8); .NET 9 → net9.0 (EF Core 9); .NET 10 → net10.0 (EF Core 10).

SQL Server 2016+ is required for the optional OPENJSON fast path on small scalar lists.

Installation

dotnet add package Deftflux.EntityFrameworkCore.SqlServer.Extensions

Register SQL Server on your DbContext as usual:

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(configuration.GetConnectionString("DefaultConnection")));

Getting started

Use the same DbContext instance to create the temp table and run the query that uses it.

using Deftflux.EntityFrameworkCore.SqlServer.Extensions;
using Microsoft.EntityFrameworkCore;

await using var context = await dbContextFactory.CreateDbContextAsync(cancellationToken);

var accountIds = new List<int> { 101, 205, 309 };

var accountFilter = await accountIds.AsQueryableTempTableAsync(context, cancellationToken);

var orders = await context.Orders
    .Where(o => accountFilter.Contains(o.AccountId))
    .ToListAsync(cancellationToken);

Usage examples

Filter by many IDs (Contains)

Avoid large WHERE Id IN (@p0, @p1, …) parameter lists:

var selectedOrderIds = new[] { 1001, 1002, 1003, 4500 };

await using var context = await dbContextFactory.CreateDbContextAsync(cancellationToken);

var idTempTable = await selectedOrderIds.AsQueryableTempTableAsync(context, cancellationToken);

var orders = await context.Orders
    .Where(o => idTempTable.Contains(o.Id))
    .ToListAsync(cancellationToken);

Join in-memory rows to a DbSet

Use a small DTO for multi-column filters. Apply [TempTableColumnLength] on string properties when the default nvarchar(450) is too short:

using Deftflux.EntityFrameworkCore.SqlServer.Extensions;

var filterRows = new List<ProductFilterDto>
{
    new() { Sku = "ABC-001", MinQuantity = 10 },
    new() { Sku = "XYZ-999", MinQuantity = 5 },
};

var filters = await filterRows.AsQueryableTempTableAsync(context, cancellationToken);

var results = await (
    from line in context.OrderLines
    join f in filters on line.Sku equals f.Sku
    where line.Quantity >= f.MinQuantity
    select line
).ToListAsync(cancellationToken);

public class ProductFilterDto
{
    [TempTableColumnLength(64)]
    public string Sku { get; set; } = null!;
    public int MinQuantity { get; set; }
}

Complex object (Person) with nested JSON properties

Scalar properties become normal columns. Complex properties (for example Supervisor) and collections of complex types (for example List<Address>) are stored automatically as nvarchar(max) JSON in {PropertyName}Json columns (AddressesJson, SupervisorJson).

Add matching string? properties so EF can read JSON from SQL, then hydrate with ToListWithJsonPropertiesAsync or HydrateJsonProperties:

using Deftflux.EntityFrameworkCore.SqlServer.Extensions;

var peopleFromApi = new List<Person>
{
    new()
    {
        Id = 1,
        Email = "ada@example.com",
        FirstName = "Ada",
        Addresses =
        [
            new Address { Street = "1 Analytical St", City = "London", Zip = "SW1" }
        ],
        Supervisor = new Supervisor { Id = 99, Name = "Byron", Title = "Lord" }
    }
};

await using var context = await dbContextFactory.CreateDbContextAsync(cancellationToken);

var people = await peopleFromApi.AsQueryableTempTableAsync(context, cancellationToken);

var matches = await (
    from employee in context.Employees
    join person in people on employee.Email equals person.Email
    select employee.Id
).ToListAsync(cancellationToken);

// When you need full Person graphs back from the temp table:
var hydrated = await people.OrderBy(p => p.Id).ToListWithJsonPropertiesAsync(cancellationToken);
// hydrated[0].Addresses and hydrated[0].Supervisor are populated from JSON.

public class Person
{
    public int Id { get; set; }

    [TempTableColumnLength(256)]
    public string Email { get; set; } = null!;

    public string FirstName { get; set; } = null!;

    // Stored as JSON in the temp table; [NotMapped] is required for SqlQueryRaw.
    [NotMapped]
    public List<Address> Addresses { get; set; } = [];

    [NotMapped]
    public Supervisor? Supervisor { get; set; }

    // Mapped from SQL; use ToListWithJsonPropertiesAsync to populate the properties above.
    public string? AddressesJson { get; set; }
    public string? SupervisorJson { get; set; }
}

public class Address
{
    public string Street { get; set; } = null!;
    public string City { get; set; } = null!;
    public string Zip { get; set; } = null!;
}

public class Supervisor
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public string Title { get; set; } = null!;
}

Use [TempTableExcludeJson] to skip a property, or [TempTableAsJson] to force JSON serialization. Mark JSON-backed complex properties with [NotMapped] so EF Core accepts SqlQueryRaw (the library still serializes them into {PropertyName}Json columns). Scalar collections such as List<int> are not stored (use a separate scalar temp table for those IDs).

Scalar lists (strings, ints, GUIDs, nullables)

Primitives are stored in a single Value column. Use Contains in predicates:

var allowedStatuses = new List<string> { "Active", "Pending", "OnHold" };
var statusFilter = await allowedStatuses.AsQueryableTempTableAsync(context, cancellationToken);

var customers = await context.Customers
    .Where(c => statusFilter.Contains(c.Status))
    .ToListAsync(cancellationToken);

var regionIds = new List<int?> { 1, 3, 5, null };
var regionFilter = regionIds.AsQueryableTempTable(context);

var sites = context.Sites
    .Where(s => regionFilter.Contains(s.RegionId))
    .ToList();

Exclude IDs (sync)

var blockedUserIds = GetBlockedUserIdsFromCache();
var blocked = blockedUserIds.AsQueryableTempTable(context);

var activeUsers = context.Users
    .Where(u => !blocked.Contains(u.Id))
    .ToList();

Service method (end-to-end)

public async Task<List<OrderSummaryDto>> GetOrdersForAccountsAsync(
    IEnumerable<int> accountIds,
    CancellationToken cancellationToken)
{
    await using var context = await _dbContextFactory.CreateDbContextAsync(cancellationToken);

    var accounts = await accountIds.AsQueryableTempTableAsync(context, cancellationToken);

    return await context.Orders
        .Where(o => accounts.Contains(o.AccountId))
        .Select(o => new OrderSummaryDto
        {
            OrderId = o.Id,
            AccountId = o.AccountId,
            Total = o.Total
        })
        .ToListAsync(cancellationToken);
}

API reference

Input shapes

Supported property types (complex DTOs): primitives, string, decimal, double, float, DateTime, DateTimeOffset, Guid, byte[], and nullable versions. Nested objects and complex collections are stored as JSON. Mark app-only properties with [NotMapped].

How it works

1. The extension opens the DbContext connection.
2. Values are loaded into #TempTable_{guid} on that connection.
3. An IQueryable<T> is returned from SqlQueryRaw.
4. You compose LINQ against DbSets; EF generates SQL that references the temp table.

Load strategies

For very large ID lists (100k+), pass a materialized List<T> or array so bulk copy can stream without building a large in-memory table.

Tips and limitations

• Use one DbContext for both creating the temp table and executing the final query.
• SQL Server only — session #temp tables, SqlBulkCopy, and OPENJSON.
• Do not use EF InMemory for validation; use LocalDB, Docker, or a real SQL Server instance.
• Monitor tempdb under very heavy or very large workloads.
• CancellationToken is honored during connection open, DDL, bulk copy, and OPENJSON insert.

Feedback

Source and issues: Deftflux.EntityFrameworkCore (Azure DevOps).