Class RelationalDatabaseFacadeExtensions

Namespace

Microsoft.EntityFrameworkCore

Assembly

Microsoft.EntityFrameworkCore.Relational.dll

Extension methods for the Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade returned from Microsoft.EntityFrameworkCore.DbContext.Database that can be used only with relational database providers.

public static class RelationalDatabaseFacadeExtensions

Inheritance

object

RelationalDatabaseFacadeExtensions

Inherited Members

object.GetType()

object.MemberwiseClone()

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

Methods

BeginTransaction(DatabaseFacade, IsolationLevel)

Starts a new transaction with a given IsolationLevel.

public static IDbContextTransaction BeginTransaction(this DatabaseFacade databaseFacade, IsolationLevel isolationLevel)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

isolationLevel IsolationLevel

The IsolationLevel to use.

Returns

IDbContextTransaction

A Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction that represents the started transaction.

BeginTransactionAsync(DatabaseFacade, IsolationLevel, CancellationToken)

Asynchronously starts a new transaction with a given IsolationLevel.

public static Task<IDbContextTransaction> BeginTransactionAsync(this DatabaseFacade databaseFacade, IsolationLevel isolationLevel, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

isolationLevel IsolationLevel

The IsolationLevel to use.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task<IDbContextTransaction>

A task that represents the asynchronous transaction initialization. The task result contains a Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction that represents the started transaction.

CloseConnection(DatabaseFacade)

Closes the underlying DbConnection.

public static void CloseConnection(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

CloseConnectionAsync(DatabaseFacade)

Closes the underlying DbConnection.

public static Task CloseConnectionAsync(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

Returns

Task

A task that represents the asynchronous operation.

ExecuteSqlInterpolated(DatabaseFacade, FormattableString)

Executes the given SQL against the database and returns the number of rows affected.

Note that this method does not start a transaction. To use this method with a transaction, first call BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy is not used by this method since the SQL may not be idempotent and does not run in a transaction. An Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy can be used explicitly, making sure to also use a transaction if the SQL is not idempotent.

As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter:

var userSuppliedSearchTerm = ".NET";
context.Database.ExecuteSqlInterpolated($"SELECT * FROM [dbo].[SearchBlogs]({userSuppliedSearchTerm})")

public static int ExecuteSqlInterpolated(this DatabaseFacade databaseFacade, FormattableString sql)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

sql FormattableString

The interpolated string representing a SQL query with parameters.

Returns

int

The number of rows affected.

ExecuteSqlInterpolatedAsync(DatabaseFacade, FormattableString, CancellationToken)

Executes the given SQL against the database and returns the number of rows affected.

Note that this method does not start a transaction. To use this method with a transaction, first call BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy is not used by this method since the SQL may not be idempotent and does not run in a transaction. An Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy can be used explicitly, making sure to also use a transaction if the SQL is not idempotent.

As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter:

var userSuppliedSearchTerm = ".NET";
context.Database.ExecuteSqlInterpolatedAsync($"SELECT * FROM [dbo].[SearchBlogs]({userSuppliedSearchTerm})")

public static Task<int> ExecuteSqlInterpolatedAsync(this DatabaseFacade databaseFacade, FormattableString sql, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

sql FormattableString

The interpolated string representing a SQL query with parameters.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task<int>

A task that represents the asynchronous operation. The task result is the number of rows affected.

ExecuteSqlRaw(DatabaseFacade, string, IEnumerable<object>)

Executes the given SQL against the database and returns the number of rows affected.

Note that this method does not start a transaction. To use this method with a transaction, first call BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy is not used by this method since the SQL may not be idempotent and does not run in a transaction. An Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy can be used explicitly, making sure to also use a transaction if the SQL is not idempotent.

As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter:

context.Database.ExecuteSqlRawAsync("SELECT * FROM [dbo].[SearchBlogs]({0})", userSuppliedSearchTerm)

However, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into this method. Doing so may expose your application to SQL injection attacks. To use the interpolated string syntax, consider using ExecuteSqlInterpolated(DatabaseFacade, FormattableString) to create parameters.

public static int ExecuteSqlRaw(this DatabaseFacade databaseFacade, string sql, IEnumerable<object> parameters)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

sql string

The SQL to execute.

parameters IEnumerable<object>

Parameters to use with the SQL.

Returns

int

The number of rows affected.

ExecuteSqlRaw(DatabaseFacade, string, params object[])

Executes the given SQL against the database and returns the number of rows affected.

Note that this method does not start a transaction. To use this method with a transaction, first call BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy is not used by this method since the SQL may not be idempotent and does not run in a transaction. An Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy can be used explicitly, making sure to also use a transaction if the SQL is not idempotent.

As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter:

context.Database.ExecuteSqlRaw("SELECT * FROM [dbo].[SearchBlogs]({0})", userSuppliedSearchTerm)

However, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into this method. Doing so may expose your application to SQL injection attacks. To use the interpolated string syntax, consider using ExecuteSqlInterpolated(DatabaseFacade, FormattableString) to create parameters.

public static int ExecuteSqlRaw(this DatabaseFacade databaseFacade, string sql, params object[] parameters)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

sql string

The SQL to execute.

parameters object[]

Parameters to use with the SQL.

Returns

int

The number of rows affected.

ExecuteSqlRawAsync(DatabaseFacade, string, IEnumerable<object>, CancellationToken)

Executes the given SQL against the database and returns the number of rows affected.

Note that this method does not start a transaction. To use this method with a transaction, first call BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy is not used by this method since the SQL may not be idempotent and does not run in a transaction. An ExecutionStrategy can be used explicitly, making sure to also use a transaction if the SQL is not idempotent.

As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter:

context.Database.ExecuteSqlRawAsync("SELECT * FROM [dbo].[SearchBlogs]({0})", userSuppliedSearchTerm)

However, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into this method. Doing so may expose your application to SQL injection attacks. To use the interpolated string syntax, consider using ExecuteSqlInterpolated(DatabaseFacade, FormattableString) to create parameters.

public static Task<int> ExecuteSqlRawAsync(this DatabaseFacade databaseFacade, string sql, IEnumerable<object> parameters, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

sql string

The SQL to execute.

parameters IEnumerable<object>

Parameters to use with the SQL.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task<int>

A task that represents the asynchronous operation. The task result is the number of rows affected.

ExecuteSqlRawAsync(DatabaseFacade, string, params object[])

Executes the given SQL against the database and returns the number of rows affected.

Note that this method does not start a transaction. To use this method with a transaction, first call BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy is not used by this method since the SQL may not be idempotent and does not run in a transaction. An Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy can be used explicitly, making sure to also use a transaction if the SQL is not idempotent.

As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter:

context.Database.ExecuteSqlRawAsync("SELECT * FROM [dbo].[SearchBlogs]({0})", userSuppliedSearchTerm)

However, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into this method. Doing so may expose your application to SQL injection attacks. To use the interpolated string syntax, consider using ExecuteSqlInterpolated(DatabaseFacade, FormattableString) to create parameters.

public static Task<int> ExecuteSqlRawAsync(this DatabaseFacade databaseFacade, string sql, params object[] parameters)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

sql string

The SQL to execute.

parameters object[]

Parameters to use with the SQL.

Returns

Task<int>

A task that represents the asynchronous operation. The task result is the number of rows affected.

ExecuteSqlRawAsync(DatabaseFacade, string, CancellationToken)

Executes the given SQL against the database and returns the number of rows affected.

Note that this method does not start a transaction. To use this method with a transaction, first call BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Note that the current Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy is not used by this method since the SQL may not be idempotent and does not run in a transaction. An Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy can be used explicitly, making sure to also use a transaction if the SQL is not idempotent.

Never pass a concatenated or interpolated string ($"") with non-validated user-provided values into this method. Doing so may expose your application to SQL injection attacks.

public static Task<int> ExecuteSqlRawAsync(this DatabaseFacade databaseFacade, string sql, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

sql string

The SQL to execute.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task<int>

A task that represents the asynchronous operation. The task result is the number of rows affected.

GenerateCreateScript(DatabaseFacade)

Generates a script to create all tables for the current model.

public static string GenerateCreateScript(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

Returns

string

A SQL script.

GetAppliedMigrations(DatabaseFacade)

Gets all migrations that have been applied to the target database.

public static IEnumerable<string> GetAppliedMigrations(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

Returns

IEnumerable<string>

The list of migrations.

GetAppliedMigrationsAsync(DatabaseFacade, CancellationToken)

Asynchronously gets all migrations that have been applied to the target database.

public static Task<IEnumerable<string>> GetAppliedMigrationsAsync(this DatabaseFacade databaseFacade, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task<IEnumerable<string>>

A task that represents the asynchronous operation.

GetCommandTimeout(DatabaseFacade)

Returns the timeout (in seconds) set for commands executed with this Microsoft.EntityFrameworkCore.DbContext.

Note that the command timeout is distinct from the connection timeout, which is commonly set on the database connection string.

public static int? GetCommandTimeout(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

Returns

int?

The timeout, in seconds, or null if no timeout has been set.

GetConnectionString(DatabaseFacade)

Gets the underlying connection string configured for this Microsoft.EntityFrameworkCore.DbContext.

public static string GetConnectionString(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

Returns

string

The connection string.

GetDbConnection(DatabaseFacade)

Gets the underlying ADO.NET DbConnection for this Microsoft.EntityFrameworkCore.DbContext.

public static DbConnection GetDbConnection(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

Returns

DbConnection

The DbConnection

GetMigrations(DatabaseFacade)

Gets all the migrations that are defined in the configured migrations assembly.

public static IEnumerable<string> GetMigrations(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

Returns

IEnumerable<string>

The list of migrations.

GetPendingMigrations(DatabaseFacade)

Gets all migrations that are defined in the assembly but haven't been applied to the target database.

public static IEnumerable<string> GetPendingMigrations(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

Returns

IEnumerable<string>

The list of migrations.

GetPendingMigrationsAsync(DatabaseFacade, CancellationToken)

Asynchronously gets all migrations that are defined in the assembly but haven't been applied to the target database.

public static Task<IEnumerable<string>> GetPendingMigrationsAsync(this DatabaseFacade databaseFacade, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task<IEnumerable<string>>

A task that represents the asynchronous operation.

IsRelational(DatabaseFacade)

Returns true if the database provider currently in use is a relational database.

public static bool IsRelational(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The facade from Microsoft.EntityFrameworkCore.DbContext.Database.

Returns

bool

true if a relational database provider is being used; false otherwise.

Migrate(DatabaseFacade)

Applies any pending migrations for the context to the database. Will create the database if it does not already exist.

Note that this API is mutually exclusive with Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated. EnsureCreated does not use migrations to create the database and therefore the database that is created cannot be later updated using migrations.

public static void Migrate(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

MigrateAsync(DatabaseFacade, CancellationToken)

Asynchronously applies any pending migrations for the context to the database. Will create the database if it does not already exist.

Note that this API is mutually exclusive with Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated. Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated does not use migrations to create the database and therefore the database that is created cannot be later updated using migrations.

public static Task MigrateAsync(this DatabaseFacade databaseFacade, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task

A task that represents the asynchronous migration operation.

OpenConnection(DatabaseFacade)

Opens the underlying DbConnection.

public static void OpenConnection(this DatabaseFacade databaseFacade)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

OpenConnectionAsync(DatabaseFacade, CancellationToken)

Opens the underlying DbConnection.

public static Task OpenConnectionAsync(this DatabaseFacade databaseFacade, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

cancellationToken CancellationToken

A CancellationToken to observe while waiting for the task to complete.

Returns

Task

A task that represents the asynchronous operation.

SetCommandTimeout(DatabaseFacade, int?)

Sets the timeout (in seconds) to use for commands executed with this Microsoft.EntityFrameworkCore.DbContext.

Note that the command timeout is distinct from the connection timeout, which is commonly set on the database connection string.

public static void SetCommandTimeout(this DatabaseFacade databaseFacade, int? timeout)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

timeout int?

The timeout to use, in seconds.

SetCommandTimeout(DatabaseFacade, TimeSpan)

Sets the timeout to use for commands executed with this Microsoft.EntityFrameworkCore.DbContext.

Note that the command timeout is distinct from the connection timeout, which is commonly set on the database connection string.

public static void SetCommandTimeout(this DatabaseFacade databaseFacade, TimeSpan timeout)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

timeout TimeSpan

The timeout to use.

SetConnectionString(DatabaseFacade, string)

Sets the underlying connection string configured for this Microsoft.EntityFrameworkCore.DbContext.

It may not be possible to change the connection string if existing connection, if any, is open.

public static void SetConnectionString(this DatabaseFacade databaseFacade, string connectionString)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

connectionString string

The connection string.

SetDbConnection(DatabaseFacade, DbConnection)

Sets the underlying ADO.NET DbConnection for this Microsoft.EntityFrameworkCore.DbContext.

The connection can only be set when the existing connection, if any, is not open.

Note that the given connection must be disposed by application code since it was not created by Entity Framework.

public static void SetDbConnection(this DatabaseFacade databaseFacade, DbConnection connection)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

connection DbConnection

The connection.

UseTransaction(DatabaseFacade, DbTransaction)

Sets the DbTransaction to be used by database operations on the Microsoft.EntityFrameworkCore.DbContext.

public static IDbContextTransaction UseTransaction(this DatabaseFacade databaseFacade, DbTransaction transaction)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

transaction DbTransaction

The DbTransaction to use.

Returns

IDbContextTransaction

A Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction that encapsulates the given transaction.

UseTransaction(DatabaseFacade, DbTransaction, Guid)

Sets the DbTransaction to be used by database operations on the Microsoft.EntityFrameworkCore.DbContext.

public static IDbContextTransaction UseTransaction(this DatabaseFacade databaseFacade, DbTransaction transaction, Guid transactionId)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

transaction DbTransaction

The DbTransaction to use.

transactionId Guid

The unique identifier for the transaction.

Returns

IDbContextTransaction

A Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction that encapsulates the given transaction.

UseTransactionAsync(DatabaseFacade, DbTransaction, Guid, CancellationToken)

Sets the DbTransaction to be used by database operations on the Microsoft.EntityFrameworkCore.DbContext.

public static Task<IDbContextTransaction> UseTransactionAsync(this DatabaseFacade databaseFacade, DbTransaction transaction, Guid transactionId, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

transaction DbTransaction

The DbTransaction to use.

transactionId Guid

The unique identifier for the transaction.

cancellationToken CancellationToken

A token to observe while waiting for the task to complete.

Returns

Task<IDbContextTransaction>

A Task containing the Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction for the given transaction.

UseTransactionAsync(DatabaseFacade, DbTransaction, CancellationToken)

Sets the DbTransaction to be used by database operations on the Microsoft.EntityFrameworkCore.DbContext.

public static Task<IDbContextTransaction> UseTransactionAsync(this DatabaseFacade databaseFacade, DbTransaction transaction, CancellationToken cancellationToken = default)

Parameters

databaseFacade DatabaseFacade

The Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade for the context.

transaction DbTransaction

The DbTransaction to use.

cancellationToken CancellationToken

A token to observe while waiting for the task to complete.

Returns

Task<IDbContextTransaction>

A Task containing the Microsoft.EntityFrameworkCore.Storage.IDbContextTransaction for the given transaction.