Table Hints (SQL Server)

Required Nuget Package:

Usage

1. Activate the support for table hints

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

2. Use extension method WithTableHints

WithTableHints lives in the root Thinktecture namespace, while SqlServerTableHint lives in Thinktecture.EntityFrameworkCore — so you typically need both usings:

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

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

There are two overloads:

IQueryable<T> WithTableHints<T>(this IQueryable<T> source, params ITableHint[] hints);
IQueryable<T> WithTableHints<T>(this IQueryable<T> source, IReadOnlyList<ITableHint> hints);

Available table hints (SqlServerTableHint)

Member	Emitted SQL
NoExpand	NOEXPAND
ForceScan	FORCESCAN
ForceSeek	FORCESEEK
HoldLock	HOLDLOCK
NoLock	NOLOCK
NoWait	NOWAIT
PagLock	PAGLOCK
ReadCommitted	READCOMMITTED
ReadCommittedLock	READCOMMITTEDLOCK
ReadPast	READPAST
ReadUncommitted	READUNCOMMITTED
RepeatableRead	REPEATABLEREAD
RowLock	ROWLOCK
Serializable	SERIALIZABLE
Snapshot	SNAPSHOT
TabLock	TABLOCK
TabLockx	TABLOCKX
UpdLock	UPDLOCK
XLock	XLOCK
Index(string name)	INDEX([name]) (the name is delimited as an identifier)
Spatial_Window_Max_Cells(int value)	SPATIAL_WINDOW_MAX_CELLS = value

// force a specific index
var products = await myDbContext.Products
                                .WithTableHints(SqlServerTableHint.Index("IX_Products_Name"))
                                .ToListAsync();

NOLOCK allows dirty reads — use it deliberately.

Table hints for bulk MERGE (SqlServerTableHintLimited)

The hints accepted by WithTableHints (SqlServerTableHint) are not the same set allowed on a MERGE statement. Bulk insert-or-update (upsert) therefore uses a separate type, SqlServerTableHintLimited, exposed via the MergeTableHints property of SqlServerBulkInsertOrUpdateOptions / SqlServerBulkUpdateOptions. SqlServerTableHintLimited is not accepted by WithTableHints.

Available members: KeepIdentity, KeepDefaults, HoldLock, IgnoreConstraints, IgnoreTriggers, NoLock, NoWait, PagLock, ReadCommitted, ReadCommittedLock, ReadPast, RepeatableRead, RowLock, Serializable, Snapshot, TabLock, TabLockx, UpdLock, XLock.

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