CODEWITHSUNDEEP
c#entity-framework-coreado.net
user3466681
user3466681

Reputation: 49

Calling Database.GetDbConnection() twice in a row causes a System.NullReferenceException

public async Task<IEnumerable<Bar>> GetFoo()
{
    using var connection = this.Database.GetDbConnection();         
    var bars = new List<Bar>();
    try{

        var cmd = new SqlCommand
        {
            CommandText = "select * from GetFoo()",
            CommandType = System.Data.CommandType.Text,
            Connection = connection as SqlConnection
        };
       
    
        await connection.OpenAsync();
        using var reader = await cmd.ExecuteReaderAsync();           
        while (await reader.ReadAsync())
        {
            var bar = new Bar
            {
                Foo = (string) reader["FOO"],
            };
            bars.Add(bar);
        }
    }
    finally
    {
        //await connection.CloseAsync();
        //await connection.DisposeAsync();
    }

    return bars;
}

When running this method twice I get:

System.NullReferenceException: 'Object reference not set to an instance of an object.' at line await connection.OpenAsync();

This is what I know:

If EF creates the DbConnection object, then EF will ensure it is disposed when the DbContext is disposed. On the other hand, if some other code creates the DbConnection object and passes it to EF, then it is the responsibility of the other code to also dispose the connection appropriately.

So what am I doing wrong here? Is there any way to overcome this issue? Can this.Datbase.GetDbConnection() be called twice in the same instance?

Upvotes: 1

Views: 1124

Answers (1)

PaoloTa
PaoloTa

Reputation: 478

As you said:

If EF creates the DbConnection object, then EF will ensure it is disposed when the DbContext is disposed.

So when EF creates the connection, instead to explicitly dispose the connection you get from this.Database.GetDbConnection(), you can just take it and open/close it:

public async Task<IEnumerable<Bar>> GetFoo()
{
    var connection = this.Database.GetDbConnection();

    using var cmd = new SqlCommand
    {
        CommandText = "select * from GetFoo()",
        CommandType = System.Data.CommandType.Text,
        Connection = connection
    };

    var bars = new List<Bar>();

    try
    {
        await connection.OpenAsync();
        using var reader = await cmd.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            var bar = new Bar
            {
                Foo = (string)reader["FOO"],
            };
            bars.Add(bar);
        }

        return bars;
    }
    finally
    {
        await connection.CloseAsync();
    }
}

This way you will not get a NullReferenceException and your connection will be handled correctly because every time you call connection.CloseAwait(), ADO.Net will release the connection to the connection pool, or close the connection if connection pooling is disabled (reference). Later EF will dispose the connection when the DbContext is disposed.

In addition, if your sql query is pretty standard, you can also replace SqlCommand with DbCommand and make your code provider-independent:

    using var cmd = connection.CreateCommand();
    cmd.CommandText = "select * from GetFoo()";
    //you can remove this line, it's by default set to Text
    cmd.CommandType = System.Data.CommandType.Text;

Upvotes: 3

Related Questions