Using Dapper QueryAsync to return a single object

Question

Unfortunately, our DB is dated back to the 90s. Its legacy is so strong that we are still using SP in order to do most of the CRUD operations. However, it seems that Dapper suits pretty well and we have just started to play with it.

However, I'm a bit concerned about how to handle a single data row. In this case, I'm using QueryAsync to call the SP passing an ID. As you can see, the object is returning outside of the async call(*).

Am I going to be in trouble? If so, does anyone know how to handle it? Do I need to use a QuerySync instead?

public class SchemePolicyRepository : ISchemePolicyRepository
{
    private readonly SqlConnection sql;

    protected SchemePolicyRepository(SqlConnection connection)
    {
        sql = connection;
    }
    ... 
    public async Task<SchemePolicy> GetById(string id)
    {
        var schemePolicy = await sql.QueryAsync<SchemePolicy>("risk.iE_GetSchemePolicyById",
            new { Id = id },
            commandType: CommandType.StoredProcedure);
        return schemePolicy != null ? schemePolicy.FirstOrDefault() : null;
    }
    ...
}

(*)The SchemePolicy object returned by FirstOfDefault() is not an async method.

*As you can see, the object is not returning out of the async call.* What does that mean? You mean your query isn't working? — Yuval Itzchakov, Jul 06 '15 at 09:41
There's nothing wrong with stored procedures, they are SQL just like the heavy, slower ORMs generate. — Crowcoder, Jul 06 '15 at 10:36
@Crowcoder you are right, this is why we preferred to use Dapper instead of EF. The old fashion ADO.NET is still something that we love to use here :) — Stefano Magistri, Jul 06 '15 at 11:31
So basically, you're worried because `FirstOrDefault` isn't async? — Yuval Itzchakov, Jul 06 '15 at 11:37

score 24 · Answer 1 · answered May 06 '19 at 12:32

Dapper supports QueryFirstOrDefaultAsync() nowadays, so you could write the code like this,

public async Task<SchemePolicy> GetById(string id)
{
    return await sql.QueryFirstOrDefaultAsync<SchemePolicy>("risk.iE_GetSchemePolicyById",
        new { Id = id },
        commandType: CommandType.StoredProcedure);
}

score 17 · Accepted Answer · answered Jul 07 '15 at 04:11

First of all, I don't think you need the null check, Dapper will return zero row for a query. TAKE NOTE that this is TRUE for SQL Server but should be the same for any other RDBMS. So this

return schemePolicy != null ? schemePolicy.FirstOrDefault() : null;

can be simply written as

return schemePolicy.FirstOrDefault();

Now to tackle the real concern, and you mentioned:

the object is returning outside of the async call(*)

That is not true. If you write it either way you will ONLY get your object after the query has run. So the following will two set of codes yield the same behavior:

var schemePolicy = await sql.QueryAsync<SchemePolicy>("sp", {rest of code});
return schemePolicy.FirstOrDefault();

and

var schemePolicy = sql.QueryAsync<SchemePolicy>("sp", {rest of code});
return schemePolicy.Result.FirstOrDefault();

The concern really is now with the way you call GetById to make sure that (1) the method will not block any other thread and (2) that you will get your target object ONLY when the query has finished running. Here's a snippet for a Console App that you can test it with:

static async void GetValue()
{
    var repo = new SchemePolicyRepository(new DbManager()); // creates an open connection 
    var result = await repo.GetById();
    Console.WriteLine(result);
}

static void Main(string[] args)
{
    GetValue();   
    Console.WriteLine("Query is running...");
    Console.ReadKey();
}

That test will show you that GetValue that consequently calls the GetById method does not block the rest of the code. Also, that nothing is returned from FirstOrDefault until the query has been processed.

Here's the supporting code for the query in case someone wants to try and verify that the concept is valid (code works with SQL Server 2008 and later):

public async Task<int> GetById()
{
    var sql = @"
WAITFOR DELAY '00:00:05';
select 1 where 1=1";

    var result = await {the_open_connection}.QueryAsync<int>(sql);    
    return result.FirstOrDefault();
}

Using Dapper QueryAsync to return a single object

2 Answers2