.NET Core EF，清理 SqlConnection.CreateCommand [英] .NET Core EF, cleaning up SqlConnection.CreateCommand

问题描述

我正在使用 .NET Core DI 来获取 DbContext 并且在我的逻辑中，我还需要在 DB 上执行原始 SQL 命令，因此为此我创建了 DbCommand像这样执行SQL(只是一个示例查询，实际的查询有点复杂，所以为了简单起见不写在这里):

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;
}

我的问题是，我在 DbContext 上使用 GetDbConnection() 和 CreateCommand()，所以我是否需要显式处理任何这些命令的结果(或将它们括在 using 语句中)?

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)?

还有用于检查 cmd.Connection.State 是否需要 ConnectionState.Open 的 if 块，如果 DI 提供 DbContext，则连接是否已经打开?

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?

顺便说一句，我们正在使用 AddDbContextPool 注册 DbContext 以启用 DbContext 池(如果有的话).

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

推荐答案

我的问题是，我在 DbContext 上使用 GetDbConnection() 和 CreateCommand()，所以我是否需要显式处理任何这些命令的结果(或将它们括在 using 语句中)?

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(如Get 一词所示)不创建DbConnection 对象，而是返回由 创建和使用的对象>DbContext 实例在其生命周期内.在这种情况下，DbContext 拥有 DbConnection，因此您不应处置该对象(这样做可能会破坏所有者功能).

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).

另一方面，CreateCommand 确实创建新的 DbCommand 对象，所以现在您的代码拥有它并负责在以下情况下处理它不再需要了.

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.

同样的原则适用于Open/Close.同样，您的代码不拥有 DbConnection 对象，因此您必须将其保留在检索它时的状态.EF Core 在处理需要打开连接的命令时在内部执行此操作 - 在开始时打开它，完成后关闭它.除非它是从外部打开的，在这种情况下他们什么都不做.这正是上述原则 - 如果您的代码执行 Open，那么它应该执行 Close，否则什么都不做.

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.

所以有问题的代码应该是这样的(注意你的代码的关闭逻辑有一个错误——调用 Close 的条件应该是 !isOpen，同样用于 Open 调用):

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();
        }
    }
} 

这篇关于.NET Core EF，清理 SqlConnection.CreateCommand的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！