Execute a stored procedure that gets data from multiple tables in EF core

The setup

We are going to recreate a scenario where we need to do just the above. We are going to write a small ‘Todo’ API.

Here is the gist link containing all the files shown below

Entities/Models

User.cs

public class User : EntityBase
{
public User()
{
UserTeams = new HashSet();
TaskItems = new HashSet();
}
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Username { get; set; }
    /// 
/// Gets or sets the tasks assigned to this user.
///

public ICollection TaskItems { get; set; }
    public ICollection UserTeams { get; set; }
}

Team.cs

public class Team : EntityBase
{
public Team()
{
UserTeams = new HashSet();
}
    /// 
/// Gets or sets the name of the team.
///

public string Name { get; set; }
    public ICollection 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; }
}

TaskItem.cs

public class TaskItem : EntityBase
{
public TaskItem()
{
Notes = new HashSet();
}
    /// 
/// Gets or sets the name of the task.
///

public string Name { get; set; }
    /// 
/// Gets or sets the description of the task.
///

public string Description { get; set; }
    /// 
/// Gets or sets the status id of the task.
///

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

public Status Status { get; set; }
    public int UserId { get; set; }
    /// 
/// Gets or sets the person this task is assigned to.
///

public User User { get; set; }
}

Status.cs

public class Status : EntityBase
{
public Status()
{
TaskItems = new HashSet();
}
    /// 
/// Gets or sets the status name.
///

public string Name { get; set; }
    /// 
/// Gets or sets the tasks currently in this status.
///

public ICollection TaskItems { get; set; }
}

Entity.cs

/// 
/// Will default the primary key of the entity to be int.
///

public class EntityBase : EntityBase
{
}
/// 
/// The base entity.
///

/// The primary key.
public class EntityBase : IEntityBase
where TKey: IEquatable
{
///
/// Gets or sets the Id of the entity
///

public int Id { get; set; }
}
public interface IEntityBase
{
}

Now lets say we need to see a progress report where we need the information of teams and users along with the count of all tasks and the tasks which are in todo, in progress and done state. A stored procedure that accepts optional teamId and userId to get the progress report of all/one team(s) fits the solution to our requirement.

read original article here