Stored procedure - EF Core

EntityBase.cs

/// <summary>
/// Using this class for base will default the primary key of the entity to be int.
/// </summary>
public class EntityBase : EntityBase<int>
{
}

/// <summary>
/// The base entity.
/// </summary>
/// <typeparam name="TKey">The primary key parameter type for the entity.</typeparam>
public class EntityBase<TKey> : IEntityBase where TKey: IEquatable<TKey>
{
    /// <summary>
    /// Gets or sets the Id of the entity
    /// </summary>
    public int Id { get; set; }
}

public interface IEntityBase
{
}

ISprocRepository.cs

public interface ISprocRepository
{
    DbCommand GetStoredProcedure(string name, params (string, object)[] nameValueParams);

    DbCommand GetStoredProcedure(string name);
}

public interface ISprocRepository<TSprocEntity> : ISprocRepository
{
    IList<TSprocEntity> ExecuteStoredProcedure(DbCommand command);

    Task<IList<TSprocEntity>> ExecuteStoredProcedureAsync(DbCommand command);
}

Note.cs

/// <summary>
/// Note Entity.
/// </summary>
public class Note : EntityBase
{
    /// <summary>
    /// Gets or sets the title of the note.
    /// </summary>
    public string Title { get; set; }

    /// <summary>
    /// Gets or sets the detailed description of the note.
    /// </summary>
    public string Description { get; set; }

    public int TaskItemId { get; set; }

    /// <summary>
    /// Gets or sets the task this note is part of.
    /// </summary>
    public TaskItem TaskItem { get; set; }
}

ProgressReportEntity.cs

public class ProgressReportEntity
{
    public int TeamId { get; set; }

    public int UserId { get; set; }

    public int TotalTasks { get; set; }

    public int Todo { get; set; }
    
    public int InProgress { get; set; }

    public int Done { get; set; }
} 

ReportsController.cs

[Route("api/[controller]")]
[ApiController]
public class ReportsController : ControllerBase
{
    private readonly ISprocRepository _repository;

    public ReportsController(ISprocRepository repository)
    {
        _repository = repository;
    }

    [HttpGet("Progress")]
    public Task<IList<ProgressReportEntity>> GetProgressReport([FromQuery] int? teamId, [FromQuery] int? userId) 
    {
        (teamId, userId) = (teamId != 0 ? teamId : null, userId != 0 ? userId : null);
        return _repository
        .GetStoredProcedure("[dbo].[Sp_ProgressReport]")
        .WithSqlParams(
            (nameof(teamId), teamId), 
            (nameof(userId), userId))
        .ExecuteStoredProcedureAsync<ProgressReportEntity>();
    }
}

Sp_ProgressReport.sql

CREATE PROCEDURE [dbo].[Sp_ProgressReport]
@teamId INT NULL,
@userId INT NULL

AS
	SELECT ut.TeamId, ut.UserId, COUNT(t.Id) TotalTasks,
	SUM(CASE WHEN t.StatusId = 1 THEN 1 ELSE 0 END) Todo,
	SUM(CASE WHEN t.StatusId = 2 THEN 1 ELSE 0 END) InProgress,
	SUM(CASE WHEN t.StatusId = 3 THEN 1 ELSE 0 END) Done
	FROM 

	[UserTeam] ut
	JOIN [TaskItem] t
	ON ut.UserId = t.UserId

	JOIN [Status] s
	ON t.StatusId = s.Id

	WHERE ut.TeamId = @teamId OR @teamId IS NULL
	AND 
	ut.UserId = @userId OR @userId IS NULL
	GROUP BY
	ut.TeamId, ut.UserId, s.Id
GO

SprocRepository.cs

public class SprocRepository : ISprocRepository
{
    private readonly TaskPlannerDbContext _dbContext;

    public SprocRepository(TaskPlannerDbContext dbContext)
    {
        _dbContext = dbContext;    
    }

    public DbCommand GetStoredProcedure(string name, params (string, object)[] nameValueParams)
    {
        return _dbContext
            .LoadStoredProcedure(name)
            .WithSqlParams(nameValueParams);
    }

    public DbCommand GetStoredProcedure(string name)
    {
        return _dbContext.LoadStoredProcedure(name);
    }
}

public class SprocRepository<TEntity> : SprocRepository, ISprocRepository<TEntity> where TEntity : class
{
    public SprocRepository(TaskPlannerDbContext dbContext) : base(dbContext)
    {
    }

    public IList<TEntity> ExecuteStoredProcedure(DbCommand command)
    {
        return command.ExecuteStoredProcedure<TEntity>();
    }

    public Task<IList<TEntity>> ExecuteStoredProcedureAsync(DbCommand command)
    {
        return command.ExecuteStoredProcedureAsync<TEntity>();
    }
}

public static class SprocRepositoryExtensions 
{
    public static DbCommand LoadStoredProcedure(this DbContext context, string storedProcName) 
    {
        var cmd = context.Database.GetDbConnection().CreateCommand();
        cmd.CommandText = storedProcName;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        return cmd;
    }

    public static DbCommand WithSqlParams(this DbCommand cmd, params (string, object)[] nameValueParamPairs)
    {
        foreach (var pair in nameValueParamPairs)
        {
            var param = cmd.CreateParameter();
            param.ParameterName = pair.Item1;
            param.Value = pair.Item2 ?? DBNull.Value;
            cmd.Parameters.Add(param);
        }

        return cmd;
    }

    public static IList<T> ExecuteStoredProcedure<T>(this DbCommand command) where T : class
    {
        using (command)
        {
            if (command.Connection.State == System.Data.ConnectionState.Closed)
                command.Connection.Open();
            try
            {
                using (var reader = command.ExecuteReader())
                {
                    return reader.MapToList<T>();
                }
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }

    public static async Task<IList<T>> ExecuteStoredProcedureAsync<T>(this DbCommand command) where T : class
    {
        using (command)
        {
            if (command.Connection.State == System.Data.ConnectionState.Closed)
                await command.Connection.OpenAsync();
            try
            {
                using (var reader = command.ExecuteReader())
                {
                    return reader.MapToList<T>();
                }
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }

    private static IList<T> MapToList<T>(this DbDataReader dr)
    {
        var objList = new List<T>();
        var props = typeof(T).GetRuntimeProperties();

        var colMapping = dr.GetColumnSchema()
            .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
            .ToDictionary(key => key.ColumnName.ToLower());

        if (dr.HasRows)
        {
            while (dr.Read())
            {
                T obj = Activator.CreateInstance<T>();
                foreach (var prop in props)
                {
                    var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
                    prop.SetValue(obj, val == DBNull.Value ? null : val);
                }
                objList.Add(obj);
            }
        }
        return objList;
    }
}

Status.cs

/// <summary>
/// The status entity --> Master table.
/// </summary>
public class Status : EntityBase
{
    public Status()
    {
        TaskItems = new HashSet<TaskItem>();
    }

    /// <summary>
    /// Gets or sets the status name.
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the tasks currently in this status.
    /// </summary>
    public ICollection<TaskItem> TaskItems { get; set; }
}

TaskItem.cs

/// <summary>
/// Task entity.
/// </summary>
public class TaskItem : EntityBase
{
    public TaskItem()
    {
        Notes = new HashSet<Note>();
    }

    /// <summary>
    /// Gets or sets the name of the task.
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the description of the task.
    /// </summary>
    public string Description { get; set; }

    /// <summary>
    /// Gets or sets the status id of the task.
    /// </summary>
    public int StatusId { get; set; }

    /// <summary>
    /// Gets or sets the status of the task.
    /// </summary>
    public Status Status { get; set; }

    public int UserId { get; set; }

    /// <summary>
    /// Gets or sets the person this task is assigned to.
    /// </summary>
    public User User { get; set; }

    /// <summary>
    /// Gets or sets the Notes made for the task.
    /// </summary>
    public ICollection<Note> Notes { get; set; }
}

Team.cs

/// <summary>
/// The team entity --> master entity.
/// </summary>
public class Team : EntityBase
{
    public Team()
    {
        UserTeams = new HashSet<UserTeam>();
    }


    /// <summary>
    /// Gets or sets the name of the team.
    /// </summary>
    public string Name { get; set; }

    public ICollection<UserTeam> UserTeams { get; set; }
}

User.cs

/// <summary>
/// This entity is not for authentication, this is an entity. --> Master table.
/// </summary>
public class User : EntityBase
{
    public User()
    {
        UserTeams = new HashSet<UserTeam>();
        TaskItems = new HashSet<TaskItem>();
    }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string Email { get; set; }

    public string Username { get; set; }

    /// <summary>
    /// Gets or sets the tasks assigned to this user.
    /// </summary>
    public ICollection<TaskItem> TaskItems { get; set; }

    public ICollection<UserTeam> UserTeams { get; set; }
}

UserTeam.cs

public class UserTeam : IEntityBase
{
    public int UserId { get; set; }

    public User User { get; set; }

    public int TeamId { get; set; }

    public Team Team { get; set; }
}