如何在.NET Core Web API中用ADO.NET异步方法替代Entity Framework Core？

Hey there, I’ve been in your shoes before—moving from EF Core to ADO.NET for performance gains while keeping async patterns intact is totally doable, and I’ll walk you through the key steps and examples to get started.

Core Migration Directions & Getting Started Tips #

First, let’s outline actionable steps to kick off your migration without biting off more than you can chew:

• Reverse-engineer EF Core queries to SQL: Use EF Core’s logging feature to capture the SQL generated by your existing Linq queries. This gives you a solid starting point instead of writing SQL from scratch—just tweak the generated SQL for readability or performance if needed.
• Build a reusable ADO.NET helper class: Avoid repeating connection/command boilerplate code by creating a DbHelper (or similar) that wraps async ADO.NET operations. This acts like a lightweight replacement for your EF Core DbContext, keeping your code DRY.
• Incremental replacement: Don’t rewrite your entire API at once. Start with the slowest, highest-traffic endpoints (like large dataset queries or reports) first. Test performance after each swap, then move on to other endpoints once you’re confident.
• Stick to async/await consistently: ADO.NET has full async support, so make sure you use all the *Async methods (like OpenAsync, ExecuteReaderAsync) and avoid mixing sync methods (which can cause thread pool starvation).

Step-by-Step ADO.NET Async Operations in .NET Core #

Let’s dive into concrete examples using SQL Server (the pattern is nearly identical for other databases like MySQL or PostgreSQL—just swap out the connection/command classes).

Key Best Practices to Remember #

• Always use using statements for SqlConnection, SqlCommand, and SqlDataReader to ensure proper resource cleanup.
• Never concatenate SQL strings—use parameterized queries to prevent SQL injection (just like EF Core does under the hood).
• Pull your connection string from configuration (via IConfiguration) instead of hardcoding it.

Example 1: Async Get Single Entity #

public async Task<Product> GetProductByIdAsync(int productId, IConfiguration config)
{
    const string sql = "SELECT Id, Name, Price FROM Products WHERE Id = @ProductId";
    var connectionString = config.GetConnectionString("DefaultConnection");

    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand(sql, connection);
    command.Parameters.AddWithValue("@ProductId", productId);

    using var reader = await command.ExecuteReaderAsync();
    if (await reader.ReadAsync())
    {
        return new Product
        {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            Price = reader.GetDecimal(2)
        };
    }

    return null;
}

Example 2: Async Get List of Entities #

public async Task<List<Product>> GetAllProductsAsync(IConfiguration config)
{
    const string sql = "SELECT Id, Name, Price FROM Products";
    var connectionString = config.GetConnectionString("DefaultConnection");
    var products = new List<Product>();

    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand(sql, connection);
    using var reader = await command.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        products.Add(new Product
        {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            Price = reader.GetDecimal(2)
        });
    }

    return products;
}

Example 3: Async Insert Entity (Return New ID) #

public async Task<int> AddProductAsync(Product product, IConfiguration config)
{
    const string sql = "INSERT INTO Products (Name, Price) OUTPUT INSERTED.Id VALUES (@Name, @Price)";
    var connectionString = config.GetConnectionString("DefaultConnection");

    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand(sql, connection);
    command.Parameters.AddWithValue("@Name", product.Name);
    command.Parameters.AddWithValue("@Price", product.Price);

    var newProductId = (int)await command.ExecuteScalarAsync();
    return newProductId;
}

Example 4: Async Update Entity #

public async Task<int> UpdateProductAsync(Product product, IConfiguration config)
{
    const string sql = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id";
    var connectionString = config.GetConnectionString("DefaultConnection");

    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand(sql, connection);
    command.Parameters.AddWithValue("@Id", product.Id);
    command.Parameters.AddWithValue("@Name", product.Name);
    command.Parameters.AddWithValue("@Price", product.Price);

    // Returns number of rows affected
    return await command.ExecuteNonQueryAsync();
}

Example 5: Reusable DbHelper Class #

To avoid repeating boilerplate, wrap these operations into a helper class you can inject into your services:

public class SqlDbHelper
{
    private readonly string _connectionString;

    public SqlDbHelper(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public async Task<T> QuerySingleAsync<T>(string sql, Func<SqlDataReader, T> mapper, params SqlParameter[] parameters)
    {
        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        using var command = new SqlCommand(sql, connection);
        command.Parameters.AddRange(parameters);

        using var reader = await command.ExecuteReaderAsync();
        return await reader.ReadAsync() ? mapper(reader) : default;
    }

    public async Task<List<T>> QueryAsync<T>(string sql, Func<SqlDataReader, T> mapper, params SqlParameter[] parameters)
    {
        var results = new List<T>();

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        using var command = new SqlCommand(sql, connection);
        command.Parameters.AddRange(parameters);

        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            results.Add(mapper(reader));
        }

        return results;
    }

    // Add ExecuteNonQueryAsync, ExecuteScalarAsync, and transaction methods here
}

Using the DbHelper #

// Inject SqlDbHelper into your controller/service
private readonly SqlDbHelper _dbHelper;

public ProductController(SqlDbHelper dbHelper)
{
    _dbHelper = dbHelper;
}

public async Task<IActionResult> GetProduct(int id)
{
    var product = await _dbHelper.QuerySingleAsync(
        "SELECT Id, Name, Price FROM Products WHERE Id = @Id",
        reader => new Product
        {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            Price = reader.GetDecimal(2)
        },
        new SqlParameter("@Id", id)
    );

    return product != null ? Ok(product) : NotFound();
}

Bonus: Async Transaction Handling #

If you need to run multiple operations in a transaction (like EF Core’s SaveChangesAsync), use ADO.NET’s async transaction methods:

public async Task<bool> TransferStockAsync(int fromProductId, int toProductId, int quantity, IConfiguration config)
{
    var connectionString = config.GetConnectionString("DefaultConnection");
    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var transaction = await connection.BeginTransactionAsync();
    try
    {
        // Decrement from product
        using var decrementCommand = new SqlCommand(
            "UPDATE Products SET Stock = Stock - @Quantity WHERE Id = @ProductId",
            connection, transaction);
        decrementCommand.Parameters.AddWithValue("@Quantity", quantity);
        decrementCommand.Parameters.AddWithValue("@ProductId", fromProductId);
        await decrementCommand.ExecuteNonQueryAsync();

        // Increment to product
        using var incrementCommand = new SqlCommand(
            "UPDATE Products SET Stock = Stock + @Quantity WHERE Id = @ProductId",
            connection, transaction);
        incrementCommand.Parameters.AddWithValue("@Quantity", quantity);
        incrementCommand.Parameters.AddWithValue("@ProductId", toProductId);
        await incrementCommand.ExecuteNonQueryAsync();

        await transaction.CommitAsync();
        return true;
    }
    catch (Exception)
    {
        await transaction.RollbackAsync();
        return false;
    }
}

Final Tips #

• Test performance: Use tools like BenchmarkDotNet to compare EF Core vs ADO.NET performance for your specific queries.
• Handle exceptions: Wrap async operations in try-catch blocks to handle database-specific exceptions (like SqlException) gracefully, just as you would with EF Core.
• Keep it simple: You don’t need to over-engineer the helper class—start with the operations you need, then expand as you go.

内容的提问来源于stack exchange，提问作者Volleyball Player