Skip to content

Collection Parameters (temp tables light)

Jump to bottom
Pawel Gerr edited this page Jun 18, 2026 · 7 revisions

Required Nuget Package:
Thinktecture.EntityFrameworkCore.SqlServer
Thinktecture.EntityFrameworkCore.PostgreSQL

SQL Server requires JSON support (SQL Server 2016 or later, Azure SQL Database).

Converts a collection of values or complex objects to an IQueryable<T>.

Configuration

Enable support for collection parameters

var services = new ServiceCollection()
                       .AddDbContext<DemoDbContext>(builder => builder
                               // SQL Server
                               .UseSqlServer("conn-string", sqlOptions =>
                                                            {
                                                                 sqlOptions.AddCollectionParameterSupport();
                                                            })
                               // PostgreSQL
                               //.UseNpgsql("conn-string", npgsqlOptions =>
                               //                          {
                               //                                npgsqlOptions.AddCollectionParameterSupport();
                               //                          })

AddCollectionParameterSupport (same signature on SQL Server and PostgreSQL) accepts a few optional arguments:

AddCollectionParameterSupport(
   JsonSerializerOptions? jsonSerializerOptions = null,         // custom JSON serialization options
   bool addCollectionParameterSupport = true,                  // set to false to disable the feature
   bool configureCollectionParametersForPrimitiveTypes = true, // auto-register primitives (int, Guid, string, ...)
   bool useDeferredSerialization = false)                      // see below
Parameter Default Effect
jsonSerializerOptions null Custom System.Text.Json options used to serialize the collection.
addCollectionParameterSupport true Enables/disables the feature.
configureCollectionParametersForPrimitiveTypes true When true, primitive types (int, Guid, bool, decimal, string, ...) are registered automatically.
useDeferredSerialization false When false, the collection is serialized when the parameter is created (i.e. in CreateScalarCollectionParameter/CreateComplexCollectionParameter). When true, serialization is deferred until the query is executed.

Introduce Types to be convertible

The types have to be introduced to EF before using them. Use ConfigureScalarCollectionParameter for introduction and configuration of primitive values like Guid, string, etc. Use ConfigureComplexCollectionParameter for introduction and configuration of (complex) objects.

Remarks: Collection parameters for primitive types like int, Guid, bool, decimal, string, etc. are registered automatically.

public record MyParameter(Guid Column1, int Column2); // A complex object with 2 properties

public class DemoDbContext : DbContext
{
   ...

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      ...

      /* Registrations without further configuration */
      modelBuilder.ConfigureScalarCollectionParameter<Guid>(); // scalar values

      modelBuilder.ConfigureComplexCollectionParameter<MyParameter>(); // complex objects

      /* Registrations with further configuration */
      modelBuilder.ConfigureScalarCollectionParameter<decimal>(builder =>   // scalar values
                               {
                                  builder.Property(e => e.Value).HasPrecision(10, 5);
                               });

      modelBuilder.ConfigureComplexCollectionParameter<MyParameter>(builder => // complex object
                               {
                                  // ...
                               });
   }
}

Usage

Use CreateScalarCollectionParameter or CreateComplexCollectionParameter to convert a collection into an IQueryable.

An example with a collection of Guids, which is scalar (primitive) value.

List<Guid> customerIds = ...;

IQueryable<Guid> customerIdsQuery = ctx.CreateScalarCollectionParameter(customerIds);

// Use the "IQueryable<Guid>" as you need to
var customers = await ctx.Customers.Where(c => customerIdsQuery.Contains(c.Id)).ToListAsync();

Both CreateScalarCollectionParameter and CreateComplexCollectionParameter take an optional applyDistinct parameter (default true), which applies DISTINCT on the provided values. Keeping it true is highly recommended — duplicate-free input produces better execution plans. Set it to false only if you need duplicates preserved:

ctx.CreateScalarCollectionParameter(customerIds, applyDistinct: false);

An example with a collection of complex objects having 2 properties.

var parameters = new[] { new MyParameter(customerId, 42) };

IQueryable<MyParameter> parametersQuery = ctx.CreateComplexCollectionParameter(parameters);

// Use the "IQueryable<MyParameter>" as you need to
var customers = await ctx.Customers
                         .Join(parametersQuery,
                               c => c.Id,
                               t => t.Column1,
                               (c, t) => new { Customer = c, Number = t.Column2 })
                         .ToListAsync();

Behind the scenes

The provided values to CreateScalarCollectionParameter and objects to CreateComplexCollectionParameter are serialized to JSON using the library System.Text.Json. On SQL Server the JSON string is sent as a SqlParameter and processed using OPENJSON. On PostgreSQL the JSON string is processed using PostgreSQL's JSON functions.

This approach usually don't require any adjustments on anything when working with scalar values.

When working with complex objects then the JSON properties must match the property names of the .NET type. Here is an example. Given is a record with 2 properties:

public record MyParameter(Guid Column1, int Column2);

Default JSON serialization is:

{ "Column1": "...", "Column2": 42 }

As we can see the JSON properties Column1 and Column2 match the .NET property names Column1 and Column2. With default configurations of System.Text.Json the names should match automatically.

If not (for some reason), then you can adjust the names with JsonPropertyNameAttribute.

public record MyParameter(
   [property: JsonPropertyName("Column1")] Guid Column1,
   int Column2);

Alternatively, you can control virtually the whole JSON serialization by passing in your JsonSerializerOptions with custom JsonConverters.

var services = new ServiceCollection()
                       .AddDbContext<DemoDbContext>(builder => builder
                               // SQL Server
                               .UseSqlServer("conn-string", sqlOptions =>
                                                            {
                                                                 var jsonSerializerOptions = new JsonSerializerOptions();

                                                                 sqlOptions.AddCollectionParameterSupport(jsonSerializerOptions);
                                                            })
                               // PostgreSQL
                               //.UseNpgsql("conn-string", npgsqlOptions =>
                               //                          {
                               //                                var jsonSerializerOptions = new JsonSerializerOptions();
                               //
                               //                                npgsqlOptions.AddCollectionParameterSupport(jsonSerializerOptions);
                               //                          })

Performance

Features

Convenience

Best Practices

Integration Testing

Extensibility

AI Coding Assistants

Clone this wiki locally