Collection Parameters (temp-tables light)

Required Nuget Package:

Requires SQL Server with JSON support (SQL Server 2016 or later, Azure SQL Database)

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

Configuration
Usage
Behind the scenes

Configuration

Enable support for collection parameters

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

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();

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

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

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

// 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 ConfigureComplexCollectionParameter are serialized to JSON using the library System.Text.Json. The JSON-string is sent as a SqlParameter to the database and is processed using OPENJSON.

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);
                                                            })