Execute Dynamic Entity in Database using Dapper

Question

My user send dynamic entity from client-project so, I have to write methods like this

public Task<TUser> FindByNameAsync(string normalizedUserName, CancellationToken cancellationToken)
    {
        cancellationToken.ThrowIfCancellationRequested();
        throw new NotImplementedException();
        //string sql = "SELECT * FROM \"IdentityUsers\" WHERE \"NormalizedUserName\" = @NormalizedUserName;";
        //using (var connection =  _databaseConnectionFactory.CreateConnectionAsync())
        //{
        //   connection.QueryFirstOrDefaultAsync<TUser>(sql,
        //        new { NormalizedUserName = normalizedUserName });
        //}
    }

My IDatabaseConnectionFactory class bind ConnectionString like below:

public interface IDatabaseConnectionFactory
{       
    Task<IDbConnection> CreateConnectionAsync();
}

public class ConnectionFactory : IDatabaseConnectionFactory
{
    private readonly string _connectionString;

    public ConnectionFactory(string connectionString) => _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));

    public async Task<IDbConnection> CreateConnectionAsync()
    {
        try
        {
            var connString =  new NpgsqlConnection(_connectionString);
            await connString.OpenAsync();
            return connString;
        }
        catch
        {
            throw;
        }
    }
}

Now, how can I execute following query using generic-type entity TUser

  string sql = "SELECT * FROM \"IdentityUsers\" WHERE \"NormalizedUserName\" = @NormalizedUserName;";
    using (var connection =  _databaseConnectionFactory.CreateConnectionAsync())
    {
       connection.QueryFirstOrDefaultAsync<TUser>(sql,
            new { NormalizedUserName = normalizedUserName });
    }

Note: QueryFirstOrDefaultAsync not found under connection here

Answer 1

You aren't awaiting the CreateConnectionAsync. Unfortunately it isn't obvious in this case, because Task<T> is disposable (so the using doesn't complain); try instead:

using (var connection = await _databaseConnectionFactory.CreateConnectionAsync())
{
    var user = await connection.QueryFirstOrDefaultAsync<TUser>(sql,
         new { NormalizedUserName = normalizedUserName });
}

As a tip: the compiler output (against the original code) helps make this clear:

Error CS1929 'Task<IDbConnection>' does not contain a definition for 'QueryFirstOrDefaultAsync' and the best extension method overload 'SqlMapper.QueryFirstOrDefaultAsync<TUser>(IDbConnection, string, object, IDbTransaction, int?, CommandType?)' requires a receiver of type 'IDbConnection'

which tells us that:

• it found some QueryFirstOrDefaultAsync method, but it wasn't usable, because
• the target expression is a Task<IDbConnection>, not an IDbConnection

---

As a side note: it is worth knowing that if you're only doing one operation with the connection, Dapper can deal with opening and closing the connection for you - which can help reduce the number of async/await operations. Consider, for example, if you had a CreateClosedConnection() method that did not open the connection, and thus had no need to be async; the following would still work:

using (var connection = _databaseConnectionFactory.CreateClosedConnection())
{
    var user = await connection.QueryFirstOrDefaultAsync<TUser>(sql,
         new { NormalizedUserName = normalizedUserName });
}

with Dapper dealing with the await OpenAsync() for you as part of the QueryFirstOrDefaultAsync.