Unit of Work Repository Base

unit-of-work-repository-base.cs

public abstract class RepositoryBase<TContext> where TContext : DbContext
{
    public TContext Context { get; set; }
    public RepositoryBase(TContext context)
    {
        Context = context;
    }
    public IQueryable<T2> Procedure<T2>(string name, params DbParameter[] parameters) where T2 : class
    {
        var sqlParameters = new List<SqlParameter>();
        foreach (var parameter in parameters)
        {
            sqlParameters.Add(new SqlParameter(parameter.ParameterName, parameter.Value));
        }
        StringBuilder sqlQuery = new StringBuilder($"EXEC [dbo].[{name}] ");
        sqlParameters.ForEach(p => sqlQuery.Append($"@{p.ParameterName},"));
        if (sqlQuery.ToString().EndsWith(","))
        {
            sqlQuery.Remove(sqlQuery.Length - 1, 1);
        }
        var result = Context.Set<T2>().FromSqlRaw(sqlQuery.ToString(), sqlParameters.ToArray());
        return result;
    }
    public IQueryable<T2> TableValuedFunction<T2>(string name, params DbParameter[] parameters)
        where T2 : class
    {
        var sqlParameters = new List<SqlParameter>();
        foreach (var parameter in parameters)
        {
            sqlParameters.Add(new SqlParameter(parameter.ParameterName, parameter.Value));
        }
        StringBuilder sqlQuery = new StringBuilder($"select * from [dbo].[{name}](");
        sqlParameters.ForEach(p => sqlQuery.Append($"@{p.ParameterName},"));
        if (sqlQuery.ToString().EndsWith(","))
        {
            sqlQuery.Remove(sqlQuery.Length - 1, 1);
        }
        sqlQuery.Append(")");
        var result = Context.Set<T2>().FromSqlRaw(sqlQuery.ToString(), sqlParameters.ToArray());
        return result;
    }
    public async Task<T3> QueryScalar<T3>(string query, params DbParameter[] parameters)
        where T3 : class
    {
        var sqlParameters = new List<SqlParameter>();
        foreach (var parameter in parameters)
        {
            sqlParameters.Add(new SqlParameter(parameter.ParameterName, parameter.Value));
        }
        using var command = Context.Database.GetDbConnection().CreateCommand();
        command.Parameters.AddRange(parameters);
        command.CommandText = query;
        return (T3)await command.ExecuteScalarAsync();
    }
    public IEnumerable<T4> Query<T4>(string query, Func<DbDataReader, T4> mapper, params DbParameter[] parameters)
    {
        using (var command = Context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = query;
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.AddRange(parameters);
            Context.Database.OpenConnection();
            using var result = command.ExecuteReader();
            var entities = new List<T4>();
            while (result.Read())
            {
                entities.Add(mapper(result));
            }
            return entities;
        }
    }
    public async Task<int> NonQuery(string query, params DbParameter[] parameters)
    {
        var sqlParameters = new List<SqlParameter>();
        foreach (var parameter in parameters)
        {
            sqlParameters.Add(new SqlParameter(parameter.ParameterName, parameter.Value));
        }
        using var command = Context.Database.GetDbConnection().CreateCommand();
        command.Parameters.AddRange(parameters);
        command.CommandText = query;
        return await command.ExecuteNonQueryAsync();
    }
}