SqlQuery<T> EFCore

EFCoreDatabaseExtensions.cs

public static class DatabaseExtensions
{
    public static SqlQuery<T> SqlQuery<T>(this DatabaseFacade database, string sqlQuery, object parameters = null)
        where T : class
    {
        return new SqlQuery<T>
        {
            Database = database,
            Query = sqlQuery,
            Parameters = GetParameters(parameters)
        };
    }

    public static SqlQuery SqlQuery(this DatabaseFacade database, string sqlQuery, object parameters = null)
    {
        return new SqlQuery
        {
            Database = database,
            Query = sqlQuery,
            Parameters = GetParameters(parameters)
        };
    }

    private static SqlParameter[] GetParameters(object parameters = null)
    {
        IDictionary<string, object> dictionary;

        if (parameters is IDictionary<string, object> objects)
        {
            dictionary = objects;
        }
        else
        {
            dictionary = new Dictionary<string, object>();

            if (parameters != null)
            {
                foreach (var property in parameters.GetType().GetProperties())
                {
                    dictionary[property.Name] = property.GetValue(parameters);
                }
            }
        }

        return dictionary.Select(x => SqlParametersNullSafe.Create(
                x.Key.StartsWith("@") ? x.Key : $"@{x.Key}",
                x.Value))
            .ToArray();
    }
}

public class SqlQuery<T>
{
    public DatabaseFacade Database { get; set; }
    public string Query { get; set; }
    public SqlParameter[] Parameters { get; set; }

    private readonly IMapper _mapper;

    public SqlQuery() : this(createMapping: true)
    {

    }

    public SqlQuery(bool createMapping)
    {
        if (createMapping)
        {
            _mapper = new MapperConfiguration(m =>
            {
                m.AddDataReaderMapping();
                m.CreateMap<IDataRecord, T>();
            }).CreateMapper();
        }
    }

    public Task<T> FirstOrDefaultAsync()
    {
        return FirstOrDefaultAsync(CancellationToken.None);
    }

    public async Task<T> FirstOrDefaultAsync(CancellationToken cancellationToken)
    {
        return (await ToListAsync(cancellationToken)).FirstOrDefault();
    }

    public Task<IList<T>> ToListAsync()
    {
        return ToListAsync(CancellationToken.None);
    }

    public async Task<IList<T>> ToListAsync(CancellationToken cancellationToken)
    {
        var connection = Database.GetDbConnection();
        var transaction = Database.CurrentTransaction.GetDbTransaction();

        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = Query;
            cmd.Transaction = transaction;

            if (Parameters.Any())
            {
                cmd.Parameters.AddRange(Parameters.ToArray());
            }

            var shouldCloseConnection = false;

            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open();

                shouldCloseConnection = true;
            }

            try
            {
                using (var reader = await cmd.ExecuteReaderAsync(cancellationToken))
                {
                    return Read(reader);
                }
            }
            finally
            {
                if (shouldCloseConnection)
                {
                    cmd.Connection.Close();
                }
            }
        }
    }

    protected virtual IList<T> Read(IDataReader reader)
    {
        return _mapper.Map<IDataReader, IEnumerable<T>>(reader).ToList();
    }
}

public class SqlQuery : SqlQuery<dynamic>
{
    public SqlQuery() : base(createMapping: false)
    {

    }

    protected override IList<dynamic> Read(IDataReader reader)
    {
        var results = new List<dynamic>();

        while (reader.Read())
        {
            results.Add(GetDataRow(reader));
        }

        return results;
    }

    private static dynamic GetDataRow(IDataRecord reader)
    {
        var dataRow = new ExpandoObject() as IDictionary<string, object>;

        for (var fieldCount = 0; fieldCount < reader.FieldCount; fieldCount++)
        {
            dataRow.Add(reader.GetName(fieldCount), reader[fieldCount]);
        }

        return dataRow;
    }
}