EFE - Temporary Table - UsePermanentTable (EF Core)

CSharp · Console

/Program.cs

// Entity Framework Extensions
// Doc: https://entityframework-extensions.net/temporary-table#usepermanenttable

// @nuget: Microsoft.EntityFrameworkCore.SqlServer
// @nuget: Z.EntityFramework.Extensions.EFCore

/*
## 🏷️ UsePermanentTable

Creates a **permanent table** instead of a temporary one.
By default, the library uses temporary tables that are automatically dropped once the operation is finished.

When `UsePermanentTable = true;`, the staging table is created as a regular permanent table.
If you also want to **keep the data after the operation**, combine it with `TemporaryTablePersist = true;`.
In this case, the permanent table will **not be dropped**, allowing you to reuse the data until you decide to remove it manually

### 💡 Why it can be useful

* **Persist data beyond the connection** → Unlike temp tables, permanent tables are not tied to the connection lifecycle.
* **Reuse data across sessions** → Keep the staging table available even if the connection closes or the application restarts.
* **Integration workflows** → Useful when another process, job, or service needs to consume the staged data later.
* **Flexible cleanup** → You decide when to drop the table, instead of SQL Server doing it automatically.

⚠️ **Important note**: Since the table is permanent, you are responsible for eventually **dropping it manually** if you don’t need it anymore, otherwise it will stay in your database.
*/

using System;
using System.Linq;
using System.Text;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;

public class Program
{
	public static void Main()
	{
		using (var context = new EntityContext())
		{
			context.Database.EnsureCreated();
		}
		
		using (var context = new EntityContext())
		{
			// minimum 11 customers to use a temporary table
			var customers = GenerateCustomer(11);

			string permanentTableName = "";

			context.BulkMerge(customers, options =>
			{
				options.UsePermanentTable = true;
				options.TemporaryTablePersist = true;

				options.BulkOperationExecuted = op =>
				{
					// Capture the resolved temporary table name after execution
					permanentTableName = op.ResolvedTemporaryTableName;
				};
			});
			
			Console.WriteLine($"Table: {permanentTableName}");
			
			FiddleHelper.WriteTable(context.Customers.AsNoTracking());
		}
	}
		 	
	public static List<Customer> GenerateCustomer(int max)
	{
		var list = new List<Customer>();
		for (int i = 0; i < max; i++)
		{
			list.Add(new Customer() { Name ="Customer_" + i});
		}
		
		return list;
	}

	public class EntityContext : DbContext
	{
		public EntityContext() 
		{
		}

		protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
		{
			optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

			base.OnConfiguring(optionsBuilder);
		}
		
		public DbSet<Customer> Customers { get; set; }
	}
	
	
	public class Customer
	{
		public int CustomerID { get; set; }
		public string Name { get; set; }
		public Boolean IsActive { get; set; }
	}
}