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?

user_14653849 0
Viewed 1372

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.

dapper blazor-server-side c#
2 Answers

The most straight forward answer I think should be:

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

I can't say I've got much Dapper experience, but with your initial implementation you were using:

  1. .Result -> .Result means that you're going to block and wait for the task to complete to get the result of the query so that it can be used immediately. Using the await keyword instead (which means one has to also add the async keyword to the method definition) means that while whatever async method you're awaiting is executing, you won't block the thread and the currently executing thread can be used for something else in the meantime until the result is ready. I'd recommend Steven Cleary's blog posts about async await as a good reference.

  2. You don't need the foreach loop I think. You're getting all the models with the first call, so you shouldn't need to requery them on a per Id basis?

You'll then call this method like

var allModels = await GetAllModelsAsync();
user_37630210

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.

user_34331780
Related