CODEWITHSUNDEEP
c#entity-frameworkazure-sql-databasedbcontextsqlconnection
feili
feili

Reputation: 1

Why my query timeout after use the existing SqlConnection to new DbContext(DbConnection existingConnection, bool contextOwnsConnection)?

the DBContext support several method to initiate, previously I pass the connection string with user/password, and everything works well.

public DbContext(string nameOrConnectionString)
{
    Check.NotEmpty(nameOrConnectionString, "nameOrConnectionString");
    InitializeLazyInternalContext(new **LazyInternalConnection**(this, nameOrConnectionString));
}

recently, I try to use one SqlConnection as the parameter to the DBContext, the contextOwnsConnection is always true. the SqlConnection.AccessToken is from one certificated based Azure Application.

public DbContext(DbConnection existingConnection, bool contextOwnsConnection)
{
    Check.NotNull(existingConnection, "existingConnection");
    InitializeLazyInternalContext(new **EagerInternalConnection**(this, existingConnection, contextOwnsConnection));
}

but I got two kinds of errors:

  1. the failed connection count increased fast, it seems connection leak. but I checked the usage for the context, it always uses the "using" statement like using(var context = createDbContext()), so I have no idea where the connection leak.

  2. I have few methods to query Database, it only take 1 min with the user/password connection string, but about 1 hour with the SqlConnection with AAD access token.

I wonder what's the difference between DbContext(DbConnection existingConnection, bool contextOwnsConnection) and DbContext(string nameOrConnectionString)? the only difference I could see is that the different InternalConnection was initialized. and my project is using lazing loading and has lots of query, and update operation frequently.

Upvotes: 0

Views: 179

Answers (1)

Sampath
Sampath

Reputation: 3639

The difference between DbContext(DbConnection existingConnection, bool contextOwnsConnection) and DbContext(string nameOrConnectionString) is that the first constructor allows you to pass an existing connection to the context, while the second constructor uses the connection string specified in the nameOrConnectionString parameter to create a new connection.

The contextOwnsConnection parameter in the first constructor specifies whether the context should dispose of the connection when it is disposed. If contextOwnsConnection is true, the context will dispose of the connection. If contextOwnsConnection is false, the context will not dispose of the connection.

The DbConnection class is an abstract class that represents a connection to a database. The SqlConnection class is a concrete implementation of the DbConnection class that represents a connection to a Microsoft SQL Server database.

The AccessToken property on the SqlConnection class allows you to specify an access token to use when connecting to the database. This is required when using Azure Active Directory (AAD) to authenticate to the database.

The error that you are seeing is likely caused by a connection leak. When you use the DbContext(DbConnection existingConnection, bool contextOwnsConnection) constructor, you are responsible for disposing of the connection if contextOwnsConnection is false. If you do not dispose of the connection, it will remain open and will eventually be timed out by the database server.

I following code DbContext(DbConnection existingConnection, bool contextOwnsConnection) constructor:

using (var connection = new SqlConnection("..."))
{
    connection.Open();
    // Create the DbContext using the existing connection.    var context = new DbContext(connection, contextOwnsConnection: false);

    // Do something using the DbContext.

    // Close the connection.
    connection.Close();
}

enter image description here

enter image description here

Code reference taken from and another method is to use context.Configuration.AutoDetectChangesEnabled = true;from this reference.

The using statement ensures that the connection is disposed of, even if an exception is thrown.

The performance difference that you are seeing between the two connection methods is likely due to the fact that the SqlConnection with AAD access token needs to make an additional call to the Azure Active Directory service to obtain the access token.

I recommend that you use the DbContext(string nameOrConnectionString) constructor unless you have a specific need to use an existing connection. The DbContext(string nameOrConnectionString) constructor is simpler to use and is less likely to lead to connection leaks.

If you do need to use the DbContext(DbConnection existingConnection, bool contextOwnsConnection) constructor, be sure to dispose of the connection when the DbContext is disposed.

Upvotes: 0

Related Questions