Using Dapper QueryAsync to return a single object
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();
}
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);
}