asp.net-mvc - 如何使用 LINQ 显示来自多个表的行的详细信息？

Question

我被困在这里，我应该显示一个人的详细信息以及他/她的分配列表。我已经创建了一个视图模型以将数据传递给视图，但结果是：

传入字典的模型项的类型为“System.Data.Entity.Infrastructure.DbQuery`1[EKCMIDTA.ViewModels.EmployeeDetailsVM]”，但此字典需要“EKCMIDTA.ViewModels.EmployeeDetailsVM”类型的模型项。

对于TicketScannings表，我只想知道这个人是否使用了一些分配，并计算使用了多少，不管它是否为空。

我希望有人可以帮助我。

谢谢！

控制器：

public ActionResult GetDetails(int empId)
{
    var employeeInformation = identityContext.AspNetUsers.Find(empId);

    var employeeDetails = dbContext.TicketAllocations.Include(a => a.AllocationCategory).Where(t => t.CMId == empId).ToList();

    var query = (from alloc in dbContext.TicketAllocations
                 join scan in dbContext.TicketScannings
                 on alloc.Id equals scan.TicketAllocationId
                 join card in dbContext.CardNumberAssignments
                 on alloc.CMId equals card.CMId into a
                 from card in a.DefaultIfEmpty()
                 join reserve in dbContext.ReservedCardNumbers
                 on card.CardNumberId equals reserve.Id into b
                 from reserve in b.DefaultIfEmpty()
                 where (alloc.CMId == empId)
                 select new EmployeeDetailsVM()
                 {
                     Employee = new Employee()
                     {
                         FirstName = employeeInformation.FirstName,
                         LastName = employeeInformation.LastName,
                         CMId = employeeInformation.Id,
                         CardNumber = reserve == null ? "No Card Number yet" : reserve.CardNumber,
                         QRCode = card == null ? "No QR Code yet" : card.QRCode
                     },

                     GetTicketAllocations = employeeDetails
                 });

      return View(query);

查看型号：

public class EmployeeDetailsVM
{
    public Employee Employee { get; set; }
    public IEnumerable<Allocation> GetTicketAllocations { get; set; }
}

public class Employee
{
    public string CMId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string CardNumber { get; set; }
    public string QRCode { get; set; }
}

public class Allocation
{
    public int AllocationId { get; set; }
    public string AllocationName { get; set; }
    public int Quantity { get; set; }
    public bool IsActive { get; set; }
    public string CreatedBy { get; set; }
    public DateTime CreatedDate { get; set; }
    public string ModifiedBy { get; set; }
    public DateTime ModifiedDate { get; set; }
}

看法：

@model EKCMIDTA.ViewModels.EmployeeDetailsVM

标签： asp.net-mvc

Answer 1

看起来您的视图只接受 single 的模型EmployeeDetailsVM，但您传入的查询可能会返回多个。

所以你可以@model EKCMIDTA.ViewModels.EmployeeDetailsVM改为@model IEnumerable<EKCMIDTA.ViewModels.EmployeeDetailsVM>

或将您的GetDetails操作更改为return View(query.FirstOrDefault());

根据评论编辑

public ActionResult GetDetails(int empId)
{
    var employeeInformation = identityContext.AspNetUsers.Find(empId);

    var employeeTickets = dbContext.TicketAllocations.Include(a => a.AllocationCategory).Where(t => t.CMId == empId).ToList();

    var employeeDetails = (from alloc in dbContext.TicketAllocations
                 join scan in dbContext.TicketScannings
                 on alloc.Id equals scan.TicketAllocationId
                 join card in dbContext.CardNumberAssignments
                 on alloc.CMId equals card.CMId into a
                 from card in a.DefaultIfEmpty()
                 join reserve in dbContext.ReservedCardNumbers
                 on card.CardNumberId equals reserve.Id into b
                 from reserve in b.DefaultIfEmpty()
                 where (alloc.CMId == empId)
                 select new EmployeeDetailsVM()
                 {
                     Employee = new Employee()
                     {
                         FirstName = employeeInformation.FirstName,
                         LastName = employeeInformation.LastName,
                         CMId = employeeInformation.Id,
                         CardNumber = reserve == null ? "No Card Number yet" : reserve.CardNumber,
                         QRCode = card == null ? "No QR Code yet" : card.QRCode
                     }
                 }).FirstOrDefault();

    if (employeeDetails != null)
        employeeDetails.GetTicketAllocations = employeeTickets;

    return View(employeeDetails);
}