How can I return a Task<List<Model>> from dapper for "Select * from table" without having to query the resulting ids from the first query again?

Question

I am working on a blazor server side project. I wrote a repository pattern class for my queries and had some troubles with the function Task<List<Model>> GetAllModelsAsync(). I want it to return a task so I can await its result within my partial components class to allow more responsive rendering.

I got it working using the following code:

public Task<List<Model>> GetAllModelsAsync()
        {
            List<Model> modelList = new();
            var sql = "SELECT * FROM model";
            var query = DbConnection.QueryAsync<Model>(sql).Result;
            
            var result = query.ToList();
            foreach (var model in result) modelList.Add(GetModelAsync(int.Parse($"{model.Id}")).Result);

            return Task.FromResult(modelList);
        }

Even though it is working it doesn't feel like being the best version since I have read that the following syntax should be working:

using (var connection = new SqlConnection(...)) 
{ 
  var authors = connection.Query<Author>(
    "Select * From Author").ToList(); 
}

I noticed that the following syntax is working as well:

var query = (await DbConnection.QueryAsync<Model>(sql)).ToList();

Though I have to change my method to async now.

I tried to define a task for it but was not able to get it working.

var sql = "SELECT * FROM model";
var task = new Task<List<Model>>(async () =>
{
  (await DbConnection.QueryAsync<Model>(sql)).ToList();
});

return task

Now I get return type of an 'async' anonymous function must be a 'void', 'Task', 'Task<T>', a task-like type, 'IAsyncEnumerable<T>', or 'lAsyncEnumerator<T>'

Most of the answers to the error state I have to return some type of Task<..> but I actually do return it.

I am not even sure if this wouldn't destroy my intended logic. thanks in advance! Regarding perfomance I am not even sure if a have any benefit from having to run GetSkill so often for my first solution.

Answer 1

This is how I've been using Dapper. Works great:

1. Make your Dapper method async.
2. Make the method or event that CALLS your method an async Task as well, so you can await the Task.
3. To return a list, do return (await blah blah blah).ToList();
4. Use the Async versions of all SQL calls.

Something seems a little off with your foreach loop. Would you mind explaining what you're trying to achieve with it? It seems like your first query should return all the models you need.

Example:

public async Task<List<ImageChoiceQuestion>> GetImageChoiceQuestionsByCreator(string AddedByUserId)
{
    string connectionString = _config.GetConnectionString("DefaultConnection");
    using (IDbConnection connection = new SqlConnection(connectionString));
    return (await connection.QueryAsync<ImageChoiceQuestion>(QImage.GetImageChoiceQuestionsByCreator, new { AddedByUserId })).ToList();
}

QImage.GetImageChoiceQuestionsByCreator is just a query string.