Query multiple results in Dapper asychronously

May 29, 2022

I am trying to fix an issue with an existing code and not sure if this approach is correct or not as I have not worked with dapper before. I am trying to return a list of objects using a stored procedure.

The code below compiles but I am not sure if this is the correct way to return multiple rows or not. Is there anything that just returns to list rather than me looping through and constructing list and then returning?

public async Task<List<Event>> GetEventsAsync()
{
   await using SqlConnection db = new(this.settings.ConnectionString);
       
   List<Event> events = new List<Event>();

   var eventsMarkedPublished =  await db.QueryAsync<Event>("[sp_get_published_events]", null, commandTimeout: 60, commandType: CommandType.StoredProcedure);

     foreach (var pubEvents in eventsMarkedPublished)
     {
        events.Add(pubEvents);
     }

     return events;
 }

>Solution :

We can try to return List by ToList method directly.

return (await db.QueryAsync<Event>("[sp_get_published_events]", null, commandTimeout: 60, commandType: CommandType.StoredProcedure)).ToList();

var result = await db.QueryAsync<Event>("[sp_get_published_events]", null, commandTimeout: 60, commandType: CommandType.StoredProcedure);
return result.ToList();