实体框架核心 - 执行SQL/SQLITE的原始代码

Question

我当前正在使用两种不同的连接类型（用于正常使用的SQLConnection和用于集成测试的SQLiteConnection），当在我的logService中插入新的异常时。

因为我无法访问数据库上下文类，因此我必须使用RAW SQL插入新实体（我只有数据库ConnectionsTring）。

运行集成测试时，由于实体关系，我必须在内存数据库中使用sqlite（SQL不支持内存）。

protected async Task InsertException(DateTime utcNow, LogLevel logLevel, EventId eventId, string state, Exception exception, CategoryAppSettings categoryAppSettings, string caller, string ipAddress, int? userId, int? organisationId, string requestId)
{
    string stackTrace = exception?.StackTrace;
    string query =
        "INSERT INTO [Exception] " +
        "(" +
            "[Created]" +
            ",[EventId]" +
            ",[IpAddress]" +
            ",[LogLevel]" +
            ",[Message]" +
            ",[Source]" +
            ",[StackTrace]" +
            ",[State]" +
            ",[UserId]" +
            ",[OrganisationId]" +
            ",[RequestId]" +
        ")" +
        "VALUES" +
        "(" +
            "@Created" +
            ",@EventId" +
            ",@IpAddress" +
            ",@LogLevel" +
            ",@Message" +
            ",@Source" +
            ",@StackTrace" +
            ",@State" +
            ",@UserId" +
            ",@OrganisationId" +
            ",@RequestId" +
        ")";
    Dictionary<string, object> parameters = new Dictionary<string, object>
    {
        { "@Created", utcNow },
        { "@EventId", eventId.ToString() ?? (object)DBNull.Value },
        { "@IpAddress", ipAddress ?? (object)DBNull.Value },
        { "@LogLevel", logLevel.ToString() ?? (object)DBNull.Value },
        { "@Message", exception?.Message ?? (object)DBNull.Value },
        { "@Source", caller ?? (object)DBNull.Value },
        { "@StackTrace", stackTrace?.Trim() ?? (object)DBNull.Value },
        { "@State", state ?? (object)DBNull.Value },
        { "@UserId", userId ?? (object)DBNull.Value },
        { "@OrganisationId", organisationId ?? (object)DBNull.Value },
        { "@RequestId", requestId ?? (object)DBNull.Value },
    };
    try
    {
        if (_hostingEnvironment.EnvironmentName == "Test")
        {
            using (SqliteConnection sqlConnection = new SqliteConnection($"Data Source={_logAppSettings.ConnectionStrings.Database};Mode=Memory;Cache=Shared;"))
            {
                using (SqliteCommand sqlCommand = new SqliteCommand(query, sqlConnection))
                {
                    foreach (var parameter in parameters)
                    {
                        sqlCommand.Parameters.Add(new SqliteParameter(parameter.Key, parameter.Value));
                    }
                    sqlConnection.Open();
                    await sqlCommand.ExecuteNonQueryAsync();
                }
            }
        }
        else
        {
            using (SqlConnection sqlConnection = new SqlConnection(_logAppSettings.ConnectionStrings.Database))
            {
                using (SqlCommand sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    foreach (var parameter in parameters)
                    {
                        sqlCommand.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
                    }
                    sqlConnection.Open();
                    await sqlCommand.ExecuteNonQueryAsync();
                }
            }
        }
    }
    catch (Exception ex)
    {
        ExceptionModel exceptionModel = new ExceptionModel()
        {
            StackTrace = ex?.StackTrace,
            Message = ex?.Message,
            InnerException = ex?.InnerException.ToString()
        };
        LogModel log = new LogModel()
        {
            Created = $"{utcNow:yyyy-MM-dd HH:mm}",
            LogLevel = LogLevel.Error.ToString(),
            EventId = LogEvents.Log,
            Source = ex.Source,
            IpAddress = ipAddress,
            UserId = userId,
            OrganisationId = organisationId,
            State = "An unexpected error occured while trying to insert new exception.",
            Exception = exceptionModel,
            RequestId = requestId
        };
        InsertLog(utcNow, log, categoryAppSettings);
    }
}

您可以看到我使用的是两种不同的数据库类型，两种不同的连接类型。当您必须自定义代码以制作测试果岭时，我真的不喜欢它！

是否可以使用两个数据库具有一种连接类型？

我已经看过"实体sql"，但我认为它在实体框架核心中没有支持，它是遗产的。

Answer 1

也许不完全是您想要的，但是您可以通过使用DBConnection而不是SQLConnection避免加倍代码。您可以在其他任何地方定义DBConnectionType：

Type connectionType = _hostingEnvironment.EnvironmentName == "Test" ? typeof(SqliteConnection) : typeof(SqlConnection);
// If you switch the Connection String outside too, it should work:
string connectionString = _hostingEnvironment.EnvironmentName == "Test" ? $"Data Source={_logAppSettings.ConnectionStrings.Database};Mode=Memory;Cache=Shared;" : _logAppSettings.ConnectionStrings.Database;

        using (DbConnection sqlConnection = Activator.CreateInstance(connectionType, new object[] {connectionString}) as DbConnection)
        {
            using (DbCommand sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText = query;
                foreach (var parameter in parameters)
                {
                   DbParameter param = sqlCommand.CreateParameter();
                   param.ParameterName= parameter.Key;
                   param.Value=parameter.Value;
                   sqlCommand.Parameters.Add(param );
                }
                sqlConnection.Open();
                await sqlCommand.ExecuteNonQueryAsync();

        }
    }
}

我希望这对您有帮助！