cast system.object to List<model> asp.net mvc

Question

I have a common dapper function to get list<model> using QueryAsync of dapper

the function looks like below

public async Task<object> QueryAsync(string spName, DynamicParameters p)
{
    return await Task.Run(async () =>
    {
        object obj = new object();
        IList objectList = obj as IList;
        using (SqlConnection conn = new SqlConnection(_connStr))
        {
            try
            {
                conn.Open();
                obj = (List<object>)await conn.QueryAsync<object>(sql: spName, param: p, commandType: CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                Utils.Logger.Instance.LogException(ex);
            }
            conn.Close();
        }
        return obj;
    });
}

now I am calling this method from my businessLogic Layer like below

public async Task<List<GetTemplates>> GetDocTemplates(string TemplateName, int AccountId)
{
    _Parameters.Add("@SP_TemplateName", TemplateName, dbType: DbType.String, direction: ParameterDirection.Input);
    _Parameters.Add("@SP_AccountId", AccountId, dbType: DbType.Int32, direction: ParameterDirection.Input);
    return (List<GetTemplates>)await _Dapper.QueryAsync("[dbo].[GetDocTemplates]", _Parameters);
}

but I am getting the following error.

Unable to cast object of type 'System.Collections.Generic.List1[System.Object]' to type 'System.Collections.Generic.List1[DocPro.DMS.BusinessEntities.Admin.GetTemplates]'.

I don't know what is wrong with the above code.

Answer 1

Dapper creates the list here. If you want it to be a list of GetTemplates, then you're going to have to tell dapper about that, presumably by making the method generic and calling _Dapper.QueryAsync<GetTemplates>(...). That said... honestly, this method isn't really adding anything except connection setup and logging - the Task.Run is unnecessary, the blind catch that swallows failure is actively dangerous, and DynamicParameters is the least preferred way of passing parameters to dapper. Suggestion:

public async Task<List<T>> QueryListAsync<T>(string spName, object parameters)
{
    using var conn = new SqlConnection(_connStr);
    try
    {
        return (await conn.QueryAsync<T>(sql: spName, param: parameters, commandType: CommandType.StoredProcedure)).AsList();
    }
    catch (Exception ex)
    {
        Utils.Logger.Instance.LogException(ex);
        throw;
    }
}
...
public Task<List<GetTemplates>> GetDocTemplates(string TemplateName, int AccountId)
{
    return _Dapper.QueryListAsync<GetTemplates>("[dbo].[GetDocTemplates]", new {
        SP_TemplateName = TemplateName,
        SP_AccountId = AccountId
    });
}