C# - Database context cast return

Question

I am currently using Asp.net database context to create a API, and I came into a problem. I have two Database Tables and I want to output some of the data but not all of it

My tables:

Employee Table:
+----+------+---------------+------------+---------------+
| Id | Name |     Mail      |   Phone    |   Birthday    |
+----+------+---------------+------------+---------------+
|  1 | John | John@Mail.com | 987 72 123 | 25-July  2000 |
|  2 | Stan | Stan@Mail.com | 978 21 342 | 10-April 1998 |
|  3 | Kim  | Kim@Mail.com  | 973 28 199 | 26-March 2001 |
+----+------+---------------+------------+---------------+

Shift Table:
+------------+--------------------+--------------------+--------+
| EmployeeId |       Start        |        End         | Active |
+------------+--------------------+--------------------+--------+
|     1      | 10-June 2019 08:00 | 10-June 2019 16:00 |  true  |
|     2      | 10-June 2019 12:00 | 10-June 2019 18:00 |  true  |
|     3      | 10-June 2019 16:00 | 11-June 2019 00:00 |  true  |
|     2      | 11-June 2019 08:00 | 11-June 2019 16:00 |  true  |
+------------+--------------------+--------------------+--------+

In my tables the Employee Table and Shift Table have a 1-Many relationship between Employee.Id and Shift.Id

Using the controller I have created i have been able to return all of the data, however I want to return a specific data-set (not everything) basically what i want to return is only:


+----+------+--------------------+--------------------+--------+
| Id | Name |       Start        |        End         | Active |
+----+------+--------------------+--------------------+--------+
|  1 | John | 10-June 2019 08:00 | 10-June 2019 16:00 | true   |
|  2 | Stan | 10-June 2019 12:00 | 10-June 2019 18:00 | true   |
|  3 | Kim  | 10-June 2019 16:00 | 11-June 2019 00:00 | true   |
|  2 | Stan | 11-June 2019 08:00 | 11-June 2019 16:00 | true   |
+----+------+--------------------+--------------------+--------+

I have tried the following code in the Controller, but I cant seem to be able to return the Data-set i want

private readonly DatabaseContext context;

public ManageUsersController(DatabaseContext Dbcontext)
{
    context = Dbcontext;
}

[HttpGet("Users")]
public List<Employee> GetUsers()
{
    var data = context.Employee.Include(c=> c.Schedule).toList();
    return data;
}

The models i retrieve the data looks like this

public partial class Employee
    {
        public int Id { get; set; }
        public int PhoneNr { get; set; }
        public string Name { get; set; }
        public string Mail { get; set; }
        public DateTime Birthday { get; set; }
        public bool Active { get; set; }

        //Relationship
        public ICollection<Schedule> Schedule { get; set; }
    }

    public partial class Schedule
    {
        public int Id { get; set; } //EmployeeId
        public DateTime Start { get; set; }
        public DateTime End{ get; set; }
        public bool? Active { get; set; }

        //Relationship
        public Employee Employee { get; set; }
    }

lastly my DatabaseContext file looks like this:

public class DatabaseContext: DbContext
{
    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
    {
    }

    public DbSet<Schedule> Schedule { get; set; }
    public DbSet<Employee> Employee { get; set; }

}

Hope my question isn't to long, and thanks in advance for the response

So you are saying that you want to filter data of the "Shift" table? If yes what kind of filter you want to add... date,name or ? — dim mik, Jun 13 '19 at 07:49
What data you require to filter is it based on any where condition. you can user where after include function. — Usama Kiyani, Jun 13 '19 at 07:53
I want to combine the tables into a single table, much like you would do in SQL with the following code: "Select e.ID, e.Name, s.Start, s.End, s.Active inner join Schedule s on s.Id = e.Id From Employee e " — Steinar, Jun 13 '19 at 07:55
@Steinar have a look here https://stackoverflow.com/questions/24485360/linq-query-to-select-data-from-table-and-join-2-tables — dim mik, Jun 13 '19 at 07:55
@dimmik Thanks ! Have been looking for something like this for at least 2 days now !! — Steinar, Jun 13 '19 at 08:14

score 2 · Answer 1 · answered Jun 13 '19 at 07:58

Create another class which will contain only the necessary properties.

public class EmployeeWithScheduleModel
{
    public int Id {get;set;}
    public string Name {get;set;}
    public DateTime Start {get;set;}
    public DateTime End {get;set;}
    public bool? Active {get;set;}
}

Then modify your controller method in order to return only what's necessary:

public List<EmployeeWithScheduleModel> GetUsers()
{
    var data = context.Employee.Include(c=>c.Schedule).ToList();

    var response = new List<EmployeeWithScheduleModel>();

    foreach (var item in data)
    {
        if(!item.Schedule.Any() || item.Schedule.Count > 1) continue; //your custom logic here

       var schedule = item.Schedule.FirstOrDefault();


       var employeeWithSchedule = new EmployeeWithScheduleModel
       {
          Id = item.Id,
          Name = item.Name,
          Start = schedule.Start,
          End = schedule.End,
          Active = schedule.Active
       };

       response.Add(employeeWithSchedule);
    }     

    return response;
}

Steinar · Accepted Answer · 2019-06-13T08:35:52.837

as @dimmik answered in the Comments on the question, it is possible to return the data by using Linq In my case this would be:

[HttpGet("")]
public List<EmployeeTable> GetUsers()
{
    var d = from e in context.Employee
        join s in context.Schedule on e.Id equals s.EmployeeId
        select new
        {
            Id = e.Id,
            Name = e.Name,
            Phone = e.PhoneNr,
            Mail = e.Mail,
            State = e.Active
        };
    return (List<EmployeeTable>)(Object)d.ToList();
}

EmployeeTable, is a custom class i created that has Id, Name, Phone, Mail and State as a property

score 0 · Answer 3 · answered Jun 13 '19 at 10:07

[HttpGet("Users")]
public List<EmployeeTable> GetUsers()
{
var empDetails=context.Employee.join(context.Schedule,e=>e.Id,d=>d.Id,(e,d)=>new {
            Id = e.Id,
            Name = e.Name,
            Start = d.Start,
            End = d.End,
            Active = d.Active
return (List<EmployeeTable>)(Object)empDetails.ToList();
});
}

C# - Database context cast return

3 Answers3