.NET Core EF, cleaning up SqlConnection.CreateCommand

.net-core asp.net-core c# entity-framework entity-framework-core

Question

I am using .NET Core DI to get DbContext and in my logic I need to execute raw SQL commands also on DB, so for that purpose I am creating DbCommand to execute SQL like this(just an example query, actual one is little complex so not writing here for simplicity):

public string GetId()
{
    var cmd = _context.Database.GetDbConnection().CreateCommand();

    bool isOpen = cmd.Connection.State == ConnectionState.Open;
    if (!isOpen)
    {
         cmd.Connection.Open();
    }

    cmd.CommandText = "Select TOP 1 ID from ABC;";
    var result = (string)cmd.ExecuteScalar();

    if (isOpen)
    {
         cmd.Connection.Close();
    }

    return result;
}

My question here is, that I am using GetDbConnection() and CreateCommand() on DbContext, so Do I need to explicitly dispose result of any of those commands(or enclose those in using statement)?

Also the if block to check if cmd.Connection.State is ConnectionState.Open required, if DI is providing with DbContext, that connection will already be open?

BTW we are using AddDbContextPool to register DbContext to enable DbContext pooling if that matters.

1
3
8/25/2019 9:59:52 AM

Accepted Answer

My question here is, that I am using GetDbConnection() and CreateCommand() on DbContext, so Do I need to explicitly dispose result of any of those commands(or enclose those in using statement)?

These are different, and the answer is yes for the later, no for the former.

All you need is to follow to simple principle - the code which allocates resource is responsible for cleaning it up.

GetDbConnection (as indicated by the word Get) does not create DbConnection object, but returns the one created and used by the DbContext instance during its lifetime. In this case the DbContext owns the DbConnection, so you shouldn't dispose that object (doing so could break the owner functionality).

From the other side, CreateCommand does create new DbCommand object, so now your code is owning it and is responsible for disposing it when not needed anymore.

The same principle applies to Open / Close. Again, your code is not owning the DbConnection object, so you have to leave it in the same state as it was when you retrieved it. EF Core internally does that when processing commands which need open connection - open it at the beginning, close it when done. Except if it was opened externally, in which case they do nothing. Which is exactly the aforementioned principle - if your code does Open, then it should do Close, do nothing otherwise.

So the code in question should be something like this (note that there is a bug in close logic of your code - the condition for calling Close should be !isOpen, the same used for Open call):

public string GetId()
{
    using (var cmd = _context.Database.GetDbConnection().CreateCommand())
    {
        bool wasOpen = cmd.Connection.State == ConnectionState.Open;
        if (!wasOpen) cmd.Connection.Open();
        try
        {
            cmd.CommandText = "Select TOP 1 ID from ABC;";
            var result = (string)cmd.ExecuteScalar();
            return result;
        }
        finally
        {
            if (!wasOpen) cmd.Connection.Close();
        }
    }
} 
5
8/25/2019 12:39:54 PM

Popular Answer

It's good practice to use the using statement so it will automatically dispose of the object when you no longer need it.

However, in your scenario, if you are using this as part of a web application, I believe that the _context scope will be created and disposed off after every HTTP Request, so it will automatically dispose any objects created within the _context scope.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow