### Test Entity Manipulation using Arrange and Act DbContext

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Isolation-of-tests

Example demonstrating the use of `ArrangeDbContext` for setup and `ActDbContext` for performing operations and assertions within a test case.

```csharp
// Example that uses "ArrangeDbContext" and "ActDbContext" only
   [Fact]
   public void Generates_RowNumber_with_orderby_and_one_column()
   {
      // Arrange
      dbContextProvider.ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("4883F7E0-FC8C-45FF-A579-DF351A3E79BF"), Name = "1" });
      dbContextProvider.ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("18C13F68-0981-4853-92FC-FB7B2551F70A"), Name = "2" });
      dbContextProvider.ArrangeDbContext.SaveChanges();

      // Act
      var result = dbContextProvider.ActDbContext.TestEntities
                            .Select(e => new
                                         {
                                            e.Name,
                                            RowNumber = EF.Functions.RowNumber(EF.Functions.OrderBy(e.Name))
                                         })
                            .ToList();

      // Assert
      result.First(t => t.Name == "1").RowNumber.Should().Be(1);
      result.First(t => t.Name == "2").RowNumber.Should().Be(2);
   }
```

--------------------------------

### Install Thinktecture.EntityFrameworkCore Agent Skill

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/AI-Coding-Assistants

Installs the AI agent skill for Thinktecture.EntityFrameworkCore using the skills CLI. This skill provides guidance on consuming EF Core extension packages.

```bash
npx skills@latest add PawelGerr/Thinktecture.EntityFrameworkCore
```

--------------------------------

### Activate Table Hint Support

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Table-Hints-(SQL-Server)

Activates the support for table hints when configuring the DbContext using SQL Server. This is a one-time setup for the application.

```csharp
var services = new ServiceCollection()
                       .AddDbContext<DemoDbContext>(builder => builder
                               .UseSqlServer("conn-string",
                                             options => options.AddTableHintSupport()));
```

--------------------------------

### Minimal DbContext Provider Setup for SQL Server and SQLite

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Isolation-of-tests

Shows the basic builder pattern for creating DbContext providers for SQL Server and SQLite. Use `BuildFactory` for SQLite when setting up an assembly fixture.

```csharp
// SQL Server
var dbContextProvider = new SqlServerTestDbContextProviderBuilder<DemoDbContext>("...", ITestIsolationOptions.SharedTablesAmbientTransaction)
       .Build();

// SQLite
var dbContextProvider = new SqliteTestDbContextProviderBuilder<DemoDbContext>()
       .Build();

// SQLite
var dbContextProviderFactory = new SqliteTestDbContextProviderBuilder<DemoDbContext>()
       .BuildFactory();

var dbContextProvider = dbContextProviderFactory.Create();
```

--------------------------------

### Handling NOT NULL Constraints with Subset Inserts (Working Example)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Shows the correct way to insert a subset of properties while avoiding NOT NULL constraint violations by setting `UsePropertiesToInsertForTempTableCreation` to true. This ensures the temp table only contains the columns being inserted.

```csharp
// This works because the temp table only has the "Id" column.
var options = new SqlServerTempTableBulkInsertOptions
{
   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => c.Id),
   Advanced = { UsePropertiesToInsertForTempTableCreation = true }
};

await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(customers, options);
```

--------------------------------

### Handling NOT NULL Constraints with Subset Inserts (Failing Example)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Demonstrates a scenario where inserting a subset of properties fails due to NOT NULL constraints when `UsePropertiesToInsertForTempTableCreation` is false. The temp table includes all columns, and excluded columns receive NULL, violating constraints.

```csharp
// This will FAIL with a NOT NULL constraint violation because the temp table
// has all columns (including the NOT NULL "Count" column) but only "Id" is inserted.
var options = new SqlServerTempTableBulkInsertOptions
{
   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => c.Id),
   Advanced = { UsePropertiesToInsertForTempTableCreation = false } // default
};

// Throws: "Cannot insert the value NULL into column 'Count'..." (SQL Server)
//         "NOT NULL constraint failed..." (SQLite)
//         "null value in column ... violates not-null constraint" (PostgreSQL)
await ctx.BulkInsertIntoTempTableAsync(customers, options);
```

--------------------------------

### PostgreSQL Conditional DDL Operation Example

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/If-(Not-)Exists-checks-in-migrations

Example of how PostgreSQL wraps DDL operations in a PL/pgSQL block with an explicit existence check for operations like CREATE TABLE.

```sql
DO $$
BEGIN
   IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'Products') THEN
      CREATE TABLE ...;
   END IF;
END
$$;
```

--------------------------------

### Test with ArrangeDbContext and ActDbContext

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Isolation-of-tests

This example demonstrates using ArrangeDbContext to set up data and ActDbContext to perform an action involving EF.Functions.RowNumber. It then asserts the generated row numbers.

```csharp
   // Example that uses "ArrangeDbContext" and "ActDbContext" only
   [Fact]
   public void Generates_RowNumber_with_orderby_and_one_column()
   {
      // Arrange
      ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("4883F7E0-FC8C-45FF-A579-DF351A3E79BF"), Name = "1" });
      ArrangeDbContext.TestEntities.Add(new TestEntity { Id = new Guid("18C13F68-0981-4853-92FC-FB7B2551F70A"), Name = "2" });
      ArrangeDbContext.SaveChanges();

      // Act
      var result = ActDbContext.TestEntities
                            .Select(e => new
                                         {
                                            e.Name,
                                            RowNumber = EF.Functions.RowNumber(EF.Functions.OrderBy(e.Name))
                                         })
                            .ToList();

      // Assert
      result.First(t => t.Name == "1").RowNumber.Should().Be(1);
      result.First(t => t.Name == "2").RowNumber.Should().Be(2);
   }
```
```

--------------------------------

### Generated SQL for SQL Server

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Example of the SQL generated for bulk insert from query on SQL Server, showing INSERT INTO ... SELECT FROM structure.

```sql
-- SQL Server
INSERT INTO [Schema].[Table] ([Col1], [Col2])
SELECT s.[SrcCol1], s.[SrcCol2]
FROM (
  <source_query>
) AS s;
```

--------------------------------

### Introduce and Configure Scalar Collection Parameters

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Register primitive types like Guid, string, etc., for collection parameter usage. Allows for additional property configuration.

```csharp
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
                               {
                                  // ...
                               });
   }
}
```

--------------------------------

### Bulk Update with Table/Schema Override (SQLite)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Example of how to override the target table name for a bulk update operation on SQLite.

```csharp
// SQLite
// new SqliteBulkUpdateFromQueryOptions { TableName = "ArchiveCustomers" }
```

--------------------------------

### Define a Record with Two Properties

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Defines a simple C# record named MyParameter with two properties, Column1 (Guid) and Column2 (int). This serves as an example for complex object serialization.

```csharp
public record MyParameter(Guid Column1, int Column2);
```

--------------------------------

### Create Scalar Collection Parameter for Guids

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Converts a list of Guids into an IQueryable for use in LINQ queries. Recommended to use with applyDistinct: true for better execution plans.

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

--------------------------------

### Bulk Update with Table/Schema Override (PostgreSQL)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Example of how to override the target table name or schema for a bulk update operation on PostgreSQL.

```csharp
// PostgreSQL
// new NpgsqlBulkUpdateFromQueryOptions { TableName = "ArchiveCustomers", Schema = "archive" }
```

--------------------------------

### Generated SQL for Bulk Update (SQL Server)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Example of SQL generated for a bulk update operation on SQL Server, including a filter condition.

```sql
UPDATE t SET [Score] = s.[Score]
FROM [Customers] AS t
INNER JOIN (<source>) AS s ON (t.[Id] = s.[Id])
WHERE t.[Score] < s.[Score];
```

--------------------------------

### Generated SQL for PostgreSQL/SQLite

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Example of the SQL generated for bulk insert from query on PostgreSQL or SQLite, showing INSERT INTO ... SELECT FROM structure with quoted identifiers.

```sql
-- PostgreSQL / SQLite
INSERT INTO "Schema"."Table" ("Col1", "Col2")
SELECT s."SrcCol1", s."SrcCol2"
FROM (
  <source_query>
) AS s;
```

--------------------------------

### Register Schema Respecting Components

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Changing-default-schema-at-runtime

Use the AddSchemaRespectingComponents extension method when configuring your DbContext to register necessary components for schema handling. This example shows configuration for SQL Server.

```csharp
ServiceCollection services = ...;

services
   .AddDbContext<DemoDbContext>(builder => builder
                                              //.UseSqlite("...")
                                              .UseSqlServer("...")
                                              //.UseNpgsql("...")
                                              ...
                                              .AddSchemaRespectingComponents());
```

--------------------------------

### Generated SQL for Bulk Update (PostgreSQL / SQLite)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Example of SQL generated for a bulk update operation on PostgreSQL or SQLite, including a filter condition.

```sql
UPDATE "Customers" AS t SET "Score" = s."Score"
FROM (<source>) AS s
WHERE t."Id" = s."Id" AND t."Score" < s."Score";
```

--------------------------------

### Add Custom IEvaluatableExpressionFilter Plugin

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Adding-custom-IEvaluatableExpressionFilter

Use the `AddEvaluatableExpressionFilterPlugin<T>` extension method to register your custom plugin implementation. Ensure the `Thinktecture.EntityFrameworkCore.Relational` NuGet package is installed.

```csharp
services
   .AddDbContext<DemoDbContext>(builder => builder
                                           //.UseSqlite("...")
                                           .UseSqlServer("...")
                                           //.UseNpgsql("...")
                                           .AddEvaluatableExpressionFilterPlugin<MyPlugin>()
```

--------------------------------

### Configure Custom Temp Table Entities

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

For custom temp table types, use `ConfigureTempTableEntity<TEntity>`. This method removes the keyless marker but does not infer a key. You must define the key yourself using the `builder` action, for example, `builder.HasKey(e => e.Id)`.

```csharp
public class DemoDbContext : DbContext
{
   ...

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

      /* Registrations without further configuration */

      modelBuilder.ConfigureTempTable<Guid>();                // introduces a table with 1 column of type Guid
      modelBuilder.ConfigureTempTable<Guid, Guid>();          // introduces a table with 2 columns
      modelBuilder.ConfigureTempTableEntity<MyTempTable>();   // custom type with N columns

      /* Registrations with further configuration */

      modelBuilder.ConfigureTempTable<decimal>(builder =>
                                           {
                                              builder.Property(e => e.Column1).HasPrecision(10, 5);
                                           });
      modelBuilder.ConfigureTempTableEntity<MyTempTable>(builder =>
                                           {
                                              builder.Property(e => e.MyDecimalProperty).HasPrecision(10, 5);
                                              builder.Property(e => e.MyStringProperty).HasMaxLength(200);
                                           });
   }
}
```

--------------------------------

### Define Default Values in EF Core Model

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Upsert-(Insert-or-Update)

Example of defining default values for properties using HasDefaultValueSql and HasDefaultValue in the EF Core model configuration. The Entity Framework Core handles these individually during bulk updates.

```csharp
modelBuilder.Entity<Customer>(builder =>
{
    builder.Property(e => e.StringProperyWithSqlDefaultValue).HasDefaultValueSql("'foo'");
    builder.Property(e => e.StringPropertyWithDefaultValue).HasDefaultValue("bar");
});
```

--------------------------------

### Add Identity Column to Migration

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Migrations-Identity-column

Use this snippet to add an integer identity column named 'Id' to the 'Customer' table during migration. Ensure the Thinktecture.EntityFrameworkCore.SqlServer or Thinktecture.EntityFrameworkCore.PostgreSQL NuGet package is installed.

```csharp
migrationBuilder.AddColumn<int>("Id", "Customer")
                .AsIdentityColumn();
```

--------------------------------

### Customized DbContext Provider Configuration for SQLite

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Isolation-of-tests

Illustrates extensive configuration options for SQLite DbContext Provider, covering logging, migration strategy, custom options, and SQL command collection.

```csharp
// SQLite
var dbContextProvider = new SqliteTestDbContextProviderBuilder<DemoDbContext>()
       .UseLogging(testOutputHelper)
       .UseMigrationExecutionStrategy(IMigrationExecutionStrategy.EnsureCreated)
       .UseMigrationLogLevel(LogLevel.Warning)
       .ConfigureOptions(builder => builder.AddNestedTransactionSupport()
                                           .AddSchemaRespectingComponents()
                                           .EnableSensitiveDataLogging())
       .ConfigureSqliteOptions(builder => builder.AddBulkOperationSupport()
                                                 .AddWindowFunctionsSupport())
       .CollectExecutedCommands()
       .Build();
```

--------------------------------

### Customized DbContext Provider Configuration for SQL Server

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Isolation-of-tests

Demonstrates advanced configuration options for SQL Server DbContext Provider, including logging, migration settings, custom options, and command collection.

```csharp
// SQL Server
var dbContextProvider = new SqlServerTestDbContextProviderBuilder<DemoDbContext>("...", ITestIsolationOptions.SharedTablesAmbientTransaction)
       .UseLogging(testOutputHelper)
       .UseMigrationLogLevel(LogLevel.Warning)
       .ConfigureOptions((builder, schema) => builder.AddNestedTransactionSupport()
                                                     .AddSchemaRespectingComponents()
                                                     .EnableSensitiveDataLogging())
       .ConfigureSqlServerOptions((builder, schema) => builder.AddBulkOperationSupport()
                                                              .AddWindowFunctionsSupport()
                                                              .AddCollectionParameterSupport())
       .UseSharedTablesIsolationLevel(IsolationLevel.ReadCommitted)
       .CollectExecutedCommands()
       .Build();
```

--------------------------------

### Activate Tenant Database Support in DbContext Options

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Queries-accross-multiple-databases-(SQL-Server)

This snippet shows how to configure DbContext options to add tenant database support. Ensure the tenant database provider factory is registered correctly.

```csharp
var services = new ServiceCollection() 
                       .AddDbContext<DemoDbContext>(builder => builder
                               .UseSqlServer("conn-string", 
                                             options => options.AddTenantDatabaseSupport<DemoTenantDatabaseProviderFactory>()));
```

--------------------------------

### Get Number of Inserted Rows

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

The ITempTableQuery<T> exposes NumberOfInsertedRows to report how many rows were inserted into the temp table after creation and insertion.

```csharp
List<Customer> customersToInsert = ...;
await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(customersToInsert);

int insertedCount = tempTable.NumberOfInsertedRows;
```

--------------------------------

### Configure Bulk Update with Options

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Use `SqlServerBulkUpdateOptions` to specify which properties to update and which properties to use for matching. This provides fine-grained control over the update process.

```csharp
var options = new SqlServerBulkUpdateOptions
{
   KeyProperties = IEntityPropertiesProvider.Include<Customer>(c => c.Id),
   PropertiesToUpdate = IEntityPropertiesProvider.Include<Customer>(c => c.FirstName)
   // use "IEntityPropertiesProvider.Exclude" to exclude properties
};

await ctx.BulkUpdateAsync(customersToInsert, options);
```

--------------------------------

### Sum, Average, Min, Max with PARTITION BY and ORDER BY

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Window-Functions-Support-(RowNumber,-Sum,-Average,-Min,-Max)

Calculates aggregate values over partitions, ordered by specified columns. Available on SQL Server and PostgreSQL.

```csharp
DbContext.OrderItems.Select(e => new
                     {
                        // SUM([o].[Count] * 2) OVER (PARTITION BY [o].[Name] ORDER BY [o].[Id])
                        Sum = EF.Functions.Sum(e.Count * 2,
                                               e.Name,
                                               EF.Functions.OrderBy(e.Id))
                     })
```

--------------------------------

### Sum, Average, Min, Max with PARTITION BY

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Window-Functions-Support-(RowNumber,-Sum,-Average,-Min,-Max)

Calculates aggregate values (Sum, Average, Min, Max) over partitions of rows. The first parameter is the expression to aggregate, followed by PARTITION BY columns.

```csharp
DbContext.OrderItems.Select(e => new
                     {
                        // SUM([o].[Count] * 2) OVER (PARTITION BY [o].[Name]) AS [Sum]
                        Sum = EF.Functions.Sum(e.Count * 2,
                                               e.Name)
                     })
```

--------------------------------

### Bulk Insert with PostgreSQL-Specific Options

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Configure PostgreSQL-specific options for bulk inserts using `NpgsqlBulkInsertOptions`. This includes setting `Freeze` for performance, `CommandTimeout`, and specifying properties to insert.

```csharp
var options = new NpgsqlBulkInsertOptions
{
   // FREEZE option: rows are frozen immediately, bypassing MVCC visibility checks.
   // Significantly improves performance for bulk loads into newly created/truncated tables.
   Freeze = true,

   CommandTimeout = TimeSpan.FromSeconds(60),

   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => new { c.Id, c.FirstName, c.LastName })
};

await ctx.BulkInsertAsync(customersToInsert, options);
```

--------------------------------

### Bulk Insert with Options - Subset of Properties

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Configure bulk insert to include only specific properties using `SqlServerBulkInsertOptions`. Use `IEntityPropertiesProvider.Include` to specify columns or `IEntityPropertiesProvider.Exclude` to omit them.

```csharp
var options = new SqlServerBulkInsertOptions
{
   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => new { c.Id, c.FirstName, c.LastName })
   // use "IEntityPropertiesProvider.Exclude" to exclude properties
};

await ctx.BulkInsertAsync(customersToInsert, options);
```

--------------------------------

### Bulk Insert with Typical SqlBulkCopy Options (SQL Server)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Configure common `SqlBulkCopy` options for SQL Server bulk inserts, such as `BatchSize`, `EnableStreaming`, and `BulkCopyTimeout`, using `SqlServerBulkInsertOptions`.

```csharp
var options = new SqlServerBulkInsertOptions
{
  BatchSize = 5_000,
  EnableStreaming = true,
  BulkCopyTimeout = TimeSpan.FromSeconds(5),
  SqlBulkCopyOptions = SqlBulkCopyOptions.Default
};
```

--------------------------------

### Set Column to a Captured Variable

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

This example shows how to update a column with the value of a variable captured from the surrounding scope. This is useful for dynamic updates based on runtime values.

```csharp
// Set a column to a captured variable
var newStatus = "Archived";

var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder
      .Set(e => e.Status, (e, f) => newStatus)       // captured variable
      .Set(e => e.Score, (e, f) => f.Score));          // source property
```

--------------------------------

### Activate Window Functions Support in DbContext

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Window-Functions-Support-(RowNumber,-Sum,-Average,-Min,-Max)

Configure your DbContext to use window functions by adding support via the UseSqlServer, UseNpgsql, or UseSqlite extension methods. This is required before you can use window functions in your queries.

```csharp
var services = new ServiceCollection()
                       .AddDbContext<DemoDbContext>(builder => builder
                               .UseSqlServer("conn-string",
                                             options => options.AddWindowFunctionsSupport())
                               // or PostgreSQL
                               //.UseNpgsql("conn-string",
                               //           options => options.AddWindowFunctionsSupport())
                               // or SQLite (RowNumber and NTile)
                               //.UseSqlite("conn-string",
                               //           options => options.AddWindowFunctionsSupport())
```

--------------------------------

### Configure Typical SQL Server Bulk Copy Options

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Sets common SQL Server Bulk Copy options for temporary table insertions, such as batch size, streaming, and timeout.

```csharp
var options = new SqlServerTempTableBulkInsertOptions
{
  BatchSize = 5_000,
  EnableStreaming = true,
  BulkCopyTimeout = TimeSpan.FromSeconds(5),
  SqlBulkCopyOptions = SqlBulkCopyOptions.Default
};
```

--------------------------------

### Create Empty Temp Table (SQLite)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Creates an empty temporary table for the Customer entity using SQLite, which relies on the base `TempTableCreationOptions`. The `ITempTableReference` obtained can be used for inserting data.

```csharp
// SQLite: uses the base TempTableCreationOptions
var options = new TempTableCreationOptions();

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);
```

--------------------------------

### Change Migrations History Table Schema (SQL Server)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Changing-default-schema-at-runtime

Customize the schema for the EF Core migration history table using the MigrationsHistoryTable method. This example is for SQL Server.

```csharp
ServiceCollection services = ...;

services
   .AddDbContext<DemoDbContext>(builder => builder
          // SQL Server
          .UseSqlServer("conn-string", sqlOptions =>
                                      {
                                         if (schema != null)
                                            sqlOptions.MigrationsHistoryTable("__EFMigrationsHistory", schema);
                                       })
          // PostgreSQL
          //.UseNpgsql("conn-string", npgsqlOptions =>
          //                          {
          //                             if (schema != null)
          //                                npgsqlOptions.MigrationsHistoryTable("__EFMigrationsHistory", schema);
          //                          })
    ...)
```

--------------------------------

### Configure Keyless and Keyed Temp Tables

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Configure temp tables using `ConfigureTempTable` on the `ModelBuilder`. By default, temp tables are keyless. To create a keyed temp table, set `isKeyless: false`. The primary key will be automatically created on `Column1` (or `Column1`, `Column2` for two-column tables) and configured as `ValueGeneratedNever()`.

```csharp
modelBuilder.ConfigureTempTable<Guid>(isKeyless: false);          // PK on Column1
modelBuilder.ConfigureTempTable<Guid, Guid>(isKeyless: false);    // composite PK on Column1, Column2

// combine with further configuration:
modelBuilder.ConfigureTempTable<decimal>(isKeyless: false, builder =>
                                     {
                                        builder.Property(e => e.Column1).HasPrecision(10, 5);
                                     });
```

--------------------------------

### Add Clustered Primary Key in SQL Server Migration

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Migrations-(Non-)Clustered-PK-(SQL-Server)

Use this method to define a clustered primary key for a table during database migrations. Ensure the Thinktecture.EntityFrameworkCore.SqlServer NuGet package is installed.

```csharp
migrationBuilder.AddPrimaryKey("PK_Customers", "Customers", "Id")
                .IsClustered(true);
```

--------------------------------

### Create and Insert into Temp Table (Custom Entity)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Use BulkInsertIntoTempTableAsync with a list of custom entities to create a temp table and insert the data in one call. The returned ITempTableQuery can be used to query the temp table and will be disposed to drop the table.

```csharp
List<Customer> customersToInsert = ...;
await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(customersToInsert);

IQueryable<Customer> query = tempTable.Query;
```

--------------------------------

### Add Index with Include Columns

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Migrations-Include-columns

Use the `IncludeColumns` extension method on `CreateIndex` to specify columns that should be included in the index. This is useful for covering indexes.

```csharp
migrationBuilder.CreateIndex("IX_OrderItems_ProductId", "OrderItems", "ProductId")
                .IncludeColumns("OrderId", "Count");
```

--------------------------------

### Using Nested Transactions in DbContext

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Nested-(virtual)-Transactions

Demonstrates the usage of BeginTransaction for both root and virtual child transactions. Shows different commit and rollback scenarios and their outcomes, including potential TransactionAbortedException.

```csharp
// starts a database transaction
using var rootTx = myDbContext.Database.BeginTransaction();
...
// creates a virtual child transaction
using var childTx = myDbContext.Database.BeginTransaction();

// case 1: both transactions are committed
childTx.Commit();
rootTx.Commit(); // commits the database transaction

// case 2: outer transaction is rolled back
childTx.Commit();
rootTx.Rollback(); // the database transaction is rolled back

// case 3: both transactions are rolled back
childTx.Rollback();
rootTx.Rollback(); // the database transaction is rolled back

// case 4: inner transactions is rolled back
childTx.Rollback();
rootTx.Commit(); // throws TransactionAbortedException, later, e.g. on Dispose the database transaction is rolled back

```

--------------------------------

### Register Thinktecture PostgreSQL Migrations Generator

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/If-(Not-)Exists-checks-in-migrations

Register the ThinktectureNpgsqlMigrationsSqlGenerator with EF Core for PostgreSQL.

```csharp
var services = new ServiceCollection()
   .AddDbContext<DemoDbContext>(builder => builder
                                           ...
                                           .UseNpgsql("conn-string", npgsqlOptions =>
                                                                                    {
                                                                                       npgsqlOptions.UseThinktectureNpgsqlMigrationsSqlGenerator();
                                                                                    })
```

--------------------------------

### Configure Collection Parameter Support Options

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Customize the behavior of collection parameter support using various options like JSON serialization, primitive type registration, and deferred serialization.

```csharp
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
```

--------------------------------

### Create Empty Temp Table (PostgreSQL)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Creates an empty temporary table for the Customer entity with PostgreSQL-specific options, controlling collation component splitting. The returned `ITempTableReference` is suitable for later data insertion.

```csharp
// PostgreSQL: collation handling
var options = new NpgsqlTempTableCreationOptions
{
   SplitCollationComponents = false
};

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);
```

--------------------------------

### Create and Insert into Temp Table (1 Column)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Use BulkInsertValuesIntoTempTableAsync to create a temp table and insert a list of single-valued items in one call. The returned ITempTableQuery can be used to query the temp table and will be disposed to drop the table.

```csharp
List<Guid> ids = ...;
await using var tempTable = await ctx.BulkInsertValuesIntoTempTableAsync(ids);

IQueryable<Guid> query = tempTable.Query;
```

--------------------------------

### Specify Moment of Primary Key Creation (SQL Server)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Defines when the primary key for a temporary table should be created on MS SQL Server. The default is AfterBulkInsert.

```csharp
var options = new SqlServerTempTableBulkInsertOptions
{
    MomentOfPrimaryKeyCreation = MomentOfSqlServerPrimaryKeyCreation.AfterBulkInsert // default
};

await ctx.BulkInsertIntoTempTableAsync(customersToInsert, options);
```

--------------------------------

### Create and Insert into Temp Table (2 Columns)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Use BulkInsertValuesIntoTempTableAsync with tuples to create a temp table and insert multiple values in one call. The returned ITempTableQuery can be used to query the temp table and will be disposed to drop the table.

```csharp
List<(Guid customerId, Guid productId)> tuples = ...;
await using var tempTable = await ctx.BulkInsertValuesIntoTempTableAsync(tuples);

IQueryable<TempTable<Guid, Guid>> query = tempTable.Query;
```

--------------------------------

### Configure DbContext with Bulk Operation Support

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Truncate-Tables

Add support for bulk operations, including TRUNCATE TABLE, to your DbContext. Ensure to configure temp tables for primitive types appropriately if using Lazy Loading.

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

--------------------------------

### Create Empty Temp Table (SQL Server)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Creates an empty temporary table for the Customer entity with SQL Server-specific options, enabling the use of default database collation for string columns. The returned `ITempTableReference` can be used for subsequent data insertion.

```csharp
// SQL Server: adds "COLLATE database_default" to string columns
var options = new SqlServerTempTableCreationOptions
{
   UseDefaultDatabaseCollation = true
};

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);
```

--------------------------------

### Create Empty Temp Table (Generic)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Creates an empty temporary table for the Customer entity using generic options. The returned `ITempTableReference` can be used to insert data later. It implements `IAsyncDisposable` to ensure the table is dropped.

```csharp
var options = new TempTableCreationOptions();
await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

string tableName = tempTable.Name; // the actual name of the temp table in the database
```

--------------------------------

### Configuring JsonSerializerOptions for SQL Server

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Illustrates how to add collection parameter support to a DbContext using SQL Server, passing custom JsonSerializerOptions to control JSON serialization.

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

                                                                 sqlOptions.AddCollectionParameterSupport(jsonSerializerOptions);
                                                            }))
```

--------------------------------

### Using WithTableHints Extension Method

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Table-Hints-(SQL-Server)

Applies table hints like RowLock and UpdLock to a query. Ensure you have the necessary usings for `Thinktecture` and `Thinktecture.EntityFrameworkCore`.

```csharp
using Thinktecture;                   // WithTableHints
using Thinktecture.EntityFrameworkCore; // SqlServerTableHint
```

```csharp
// starts a database transaction
using var rootTx = myDbContext.Database.BeginTransaction();

var product = await myDbContext.Products
                               .WithTableHints(SqlServerTableHint.RowLock, SqlServerTableHint.UpdLock)
                               .FirstOrDefaultAsync(p => p.Id == id);
```

--------------------------------

### Arithmetic on Source Properties

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Perform arithmetic operations, such as addition, using values from source properties and constants. This allows for incremental updates based on existing data.

```csharp
// Arithmetic on source properties
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => e.Id,
   f => f.Id,
   builder => builder
      .Set(e => e.Score, (e, f) => f.Score + 10));    // source + constant
```

--------------------------------

### Configure MERGE Table Hints (SQL Server)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Upsert-(Insert-or-Update)

Specify table hints for the MERGE command used in SQL Server bulk upserts. This allows fine-grained control over table locking and access.

```csharp
var options = new SqlServerBulkInsertOrUpdateOptions
{
  MergeTableHints = { SqlServerTableHintLimited.HoldLock, SqlServerTableHintLimited.RowLock }
};
```

--------------------------------

### Basic Bulk Insert

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Insert a list of entities using the default `BulkInsertAsync` overload. The method returns the number of rows inserted.

```csharp
List<Customer> customersToInsert = ...;

// insert entities as a whole
int numberOfInsertedRows = await ctx.BulkInsertAsync(customersToInsert);
```

--------------------------------

### Enable Bulk Insert Support for SQL Server

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Enable bulk-insert support by using the extension method `AddBulkOperationSupport` when configuring the DbContext for SQL Server. If using Lazy Loading, consider disabling temp tables for primitive types.

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

--------------------------------

### Enable Collection Parameter Support for SQL Server

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Configure DbContext to support collection parameters for SQL Server. This requires SQL Server 2016 or later.

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

--------------------------------

### Configure SqlBulkCopy Options for Temp Table

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Control the creation and population of the temporary table used during SQL Server bulk updates. This includes settings like batch size, streaming, and timeouts.

```csharp
var options = new SqlServerBulkUpdateOptions
{
  TempTableOptions =
  {
     BatchSize = 5_000,
     EnableStreaming = true,
     BulkCopyTimeout = TimeSpan.FromSeconds(5),
     SqlBulkCopyOptions = SqlBulkCopyOptions.Default
  }
};
```

--------------------------------

### Create Temp Table with Subset of Columns

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Creates a temporary table containing only specified columns from the Customer entity using `PropertiesToInclude`. The `ITempTableReference` can then be used to insert data into this subset.

```csharp
var options = new TempTableCreationOptions
{
   PropertiesToInclude = IEntityPropertiesProvider.Include<Customer>(c => new { c.Id, c.FirstName })
};

await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);
```

--------------------------------

### Create Scalar Collection Parameter with applyDistinct: false

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Demonstrates how to create a scalar collection parameter while explicitly disabling the DISTINCT clause. Use with caution, as preserving duplicates can lead to less optimal execution plans.

```csharp
ctx.CreateScalarCollectionParameter(customerIds, applyDistinct: false);
```

--------------------------------

### Bulk Update with Composite Key Join

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Use anonymous types to join on multiple columns for bulk updates. Keys are matched by position, and the number of target and source keys must be the same.

```csharp
var affectedRows = await ctx.Set<Customer>().BulkUpdateAsync(
   sourceQuery,
   e => new { e.Id, e.Region },
   f => new { f.Id, f.Region },
   builder => builder.Set(e => e.Score, (e, f) => f.Score));
```

--------------------------------

### Configure Primary Key Creation for Temp Tables

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Sets the primary key creation strategy for temporary tables. The default is EntityTypeConfiguration.

```csharp
var options = new SqlServerTempTableBulkInsertOptions
{
   PrimaryKeyCreation = IPrimaryKeyPropertiesProvider.EntityTypeConfiguration // default
};

await ctx.BulkInsertIntoTempTableAsync(customersToInsert, options);
```

--------------------------------

### Register Thinktecture SQL Server Migrations Generator

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/If-(Not-)Exists-checks-in-migrations

Register the ThinktectureSqlServerMigrationsSqlGenerator with EF Core for SQL Server.

```csharp
var services = new ServiceCollection()
   .AddDbContext<DemoDbContext>(builder => builder
                                           ...
                                           .UseSqlServer("conn-string", sqlOptions =>
                                                                                    {
                                                                                       sqlOptions.UseThinktectureSqlServerMigrationsSqlGenerator();
                                                                                    })
```

--------------------------------

### NTILE Bucketing

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Window-Functions-Support-(RowNumber,-Sum,-Average,-Min,-Max)

Distributes ordered rows into a specified number of buckets and returns the bucket number. Available on SQL Server, PostgreSQL, and SQLite. SQL Server returns long, while PostgreSQL and SQLite return int. Requires ORDER BY on SQL Server.

```csharp
// SQL Server: NTILE([n]) OVER (ORDER BY [o].[Price])
DbContext.OrderItems.Select(o => new
                     {
                        Bucket = EF.Functions.NTile(4, EF.Functions.OrderBy(o.Price))
                     })
```

```csharp
// with PARTITION BY
DbContext.OrderItems.Select(o => new
                     {
                        Bucket = EF.Functions.NTile(4, o.ProductId, EF.Functions.OrderBy(o.Price))
                     })
```

--------------------------------

### Bulk Update Entire Entities

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Update

Update entities by sending all their properties to the database. The primary key columns are used for matching by default.

```csharp
List<Customer> customersToUpdate = ...;

// update entities as a whole
await ctx.BulkUpdateAsync(customersToUpdate);
```

--------------------------------

### Bulk Update from Query

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/DbContext-Lifetime-and-Change-Tracking

Performs a bulk update on entities selected by a source query. The source query is translated to SQL and executed server-side, meaning no entities are materialized or tracked by the context.

```csharp
await ctx.Orders.BulkUpdateAsync(
   ctx.Orders.Where(o => o.IsActive),                       // source IQueryable
   target => target.Id, source => source.Id,                // key selectors
   setter => setter.Set(t => t.Count, (t, s) => t.Count + 1));
```

--------------------------------

### RowNumber with PARTITION BY and ORDER BY

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Window-Functions-Support-(RowNumber,-Sum,-Average,-Min,-Max)

Calculates the row number within partitions defined by the first parameters and ordered by the subsequent parameters. Supports up to 16 PARTITION BY columns.

```csharp
// partition by "ProductId"
// order by "OrderId" and "Count"
DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(o.ProductId,
                                                           EF.Functions.OrderBy(o.OrderId)
                                                                       .ThenBy(o.Count))
                      })
```

```csharp
// partition by "ProductId" and "OrderId"
// order by "Count"
DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(o.ProductId, o.OrderId,
                                                           EF.Functions.OrderBy(o.Count))
                      })
```

--------------------------------

### Insert into Temp Table and Query

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/DbContext-Lifetime-and-Change-Tracking

Inserts entities into a temp table and returns a query over it. If the temp-table entity has a primary key, AsNoTracking() is applied automatically.

```csharp
await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(entities);

var joined = await ctx.Orders
                      .Join(tempTable.Query, o => o.Id, t => t.Id, (o, t) => o)
                      .ToListAsync();
```

--------------------------------

### Fetch Prioritized Translations using RowNumber (Separate Select)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Window-Functions-Support-(RowNumber,-Sum,-Average,-Min,-Max)

Retrieves product translations with prioritized languages using a two-step Select approach. This separates the priority logic into a distinct step, potentially improving readability for complex conditions.

```csharp
var productList = await ctx.ProductTranslations
                            .Where(pt=> pt.Field == ProductTranslation.FieldEnum.Name)
                            .Select(pt=> new
                            {
                                Translation = pt,
                                Priority =  pt.LanguageId == userLanguage ? 0 //user's language has priority 0
                                            : pt.LanguageId == applicationLanguage ? 1 //application language has priority 1
                                            : 2 //Any other language has priority 2
                            })
                            .Select(i=> new
                            {
                                i.Translation.ProductId,
                                i.Translation.Product,
                                Name = i.Translation.Text,
                                LangOrder = EF.Functions.RowNumber(
                                    i.Translation.ProductId, // partition by ProductId
                                    EF.Functions.OrderBy(i.Priority))
                            })
                            .AsSubQuery()
                            .Where(i => i.LangOrder == 1)
                            .OrderBy(l => l.Name)
                            .ToListAsync();
```

--------------------------------

### Add IfNotExists Check to CreateIndex Operation

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/If-(Not-)Exists-checks-in-migrations

Use the IfNotExists extension method to ensure an index is only created if it does not already exist.

```csharp
migrationBuilder.CreateIndex("IX_OrderItems_ProductId", "OrderItems", "ProductId").IfNotExists();
```

--------------------------------

### Table Hints for Bulk MERGE Operations

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Table-Hints-(SQL-Server)

Configures table hints for bulk MERGE operations using `SqlServerTableHintLimited`. These hints are specific to MERGE statements and differ from those used with `WithTableHints`.

```csharp
var options = new SqlServerBulkInsertOrUpdateOptions
              {
                 MergeTableHints = { SqlServerTableHintLimited.HoldLock }
              };
await myDbContext.BulkInsertOrUpdateAsync(entities, options);
```

--------------------------------

### Configure Temp Table Bulk Insert Options

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Set DropTableOnDispose to false to reuse the temp table within the same connection. TruncateTableIfExists ensures data is cleared before new inserts.

```csharp
var options = new SqlServerTempTableBulkInsertOptions
{
  DropTableOnDispose = false,
  TruncateTableIfExists = true
};
```

--------------------------------

### Customizing JSON Property Names with JsonPropertyNameAttribute

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Demonstrates how to explicitly control JSON property names for a record's properties using the JsonPropertyNameAttribute when default naming is insufficient.

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

--------------------------------

### Insert Subset of Properties into Temp Table

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Configure bulk insert to use only a subset of entity properties for the temp table. Ensure `UsePropertiesToInsertForTempTableCreation` is true to match the temp table structure to the selected properties.

```csharp
var options = new SqlServerTempTableBulkInsertOptions
{
   PropertiesToInsert = IEntityPropertiesProvider.Include<Customer>(c => new { c.Id, c.FirstName, c.LastName }),
   // use "IEntityPropertiesProvider.Exclude" to exclude properties

   // required so the temp table is created with selected properties only
   Advanced = { UsePropertiesToInsertForTempTableCreation = true }
};

await using var tempTable = await ctx.BulkInsertIntoTempTableAsync(customersToInsert, options);

// we may access included properties only
var query = await tempTable.Query.Select(c => new { c.Id, c.FirstName, c.LastName });
```

--------------------------------

### Configure Temp Table Options for Bulk Upsert (SQL Server)

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Upsert-(Insert-or-Update)

Control the creation and population of temporary tables used during SQL Server bulk upserts. This includes batch size, streaming, and timeout settings.

```csharp
var options = new SqlServerBulkInsertOrUpdateOptions
{
  TempTableOptions =
  {
     BatchSize = 5_000,
     EnableStreaming = true,
     BulkCopyTimeout = TimeSpan.FromSeconds(5),
     SqlBulkCopyOptions = SqlBulkCopyOptions.Default
  }
};
```

--------------------------------

### Implement IDbDefaultSchema in DbContext

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Changing-default-schema-at-runtime

Implement the IDbDefaultSchema interface in your DbContext to provide a Schema property. The schema can be set by injecting IDbDefaultSchema into the constructor.

```csharp
public class DemoDbContext : DbContext, IDbDefaultSchema
{
   public string? Schema { get; }

   public DemoDbContext(DbContextOptions<DemoDbContext> options, IDbDefaultSchema? schema = null)
      : base(options)
   {
      Schema = schema?.Schema;
   }
}
```

--------------------------------

### Override Table and Schema for PostgreSQL

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Insert

Specifies custom table name and schema for bulk insert operations on PostgreSQL. Ensure the schema and table exist.

```csharp
// PostgreSQL
var options = new NpgsqlBulkInsertFromQueryOptions
{
    TableName = "ArchiveCustomers",
    Schema = "archive"
};

await ctx.Set<Customer>().BulkInsertAsync(sourceQuery, mapBuilder, options);
```

--------------------------------

### Insert Data into Existing Temp Table

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Temp-Tables

Inserts data into a pre-existing temporary table referenced by `ITempTableReference`. This method supports inserting data in multiple batches and returns a `Task`. It also shows how to insert primitive values using `BulkInsertValuesIntoTempTableAsync`.

```csharp
var options = new TempTableCreationOptions();
await using var tempTable = await ctx.CreateTempTableAsync<Customer>(options);

// Insert data (returns Task, not ITempTableQuery<T>)
List<Customer> batch1 = ...;
await ctx.BulkInsertIntoTempTableAsync(batch1, tempTable);

// Insert more data into the same temp table
List<Customer> batch2 = ...;
await ctx.BulkInsertIntoTempTableAsync(batch2, tempTable);

// For values (primitives / tuples)
List<Guid> ids = ...;
await using var valueTable = await ctx.CreateTempTableAsync<TempTable<Guid>>(new TempTableCreationOptions());
await ctx.BulkInsertValuesIntoTempTableAsync(ids, valueTable);
```

--------------------------------

### Default JSON Serialization of MyParameter

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Collection-Parameters-(temp-tables-light)

Shows the default JSON output for the MyParameter record, where property names match the .NET type's property names.

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

--------------------------------

### Insert-Only Bulk Operation

Source: https://github.com/pawelgerr/thinktecture.entityframeworkcore/wiki/Bulk-Upsert-(Insert-or-Update)

Perform a bulk insert operation without any update part by setting 'PropertiesToUpdate' to an empty provider. This skips the UPDATE clause entirely.

```csharp
var options = new SqlServerBulkInsertOrUpdateOptions
{
  PropertiesToUpdate = IEntityPropertiesProvider.Empty
};

await ctx.BulkInsertOrUpdateAsync(customersToUpsert, options);
```