时间戳

首页 > 解决方案 > C# - 数据库上下文转换返回

c# - C# - 数据库上下文转换返回

问题描述

我目前正在使用 Asp.net 数据库上下文来创建 API，但遇到了问题。我有两个数据库表，我想输出一些数据，但不是全部

我的桌子：

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  |
+------------+--------------------+--------------------+--------+

在我的表中，Employee Table 和 Shift Table 在 Employee.Id 和 Shift.Id 之间有一对多的关系

使用我创建的控制器，我已经能够返回所有数据，但是我想返回一个特定的数据集（不是所有的），基本上我想要返回的只是：


+----+------+--------------------+--------------------+--------+
| 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   |
+----+------+--------------------+--------------------+--------+

我在控制器中尝试了以下代码，但我似乎无法返回我想要的数据集

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;
}

我检索数据的模型如下所示

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; }
    }

最后我的 DatabaseContext 文件如下所示：

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

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

}

希望我的问题不会太长，并提前感谢您的回复

标签： c#asp.netdatabaseapidbcontext

解决方案

创建另一个仅包含必要属性的类。

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;}
}

然后修改您的控制器方法，以便仅返回必要的内容：

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;
}

推荐阅读