Linq to SQL " 2 Query "

本文关键字：quot Query to SQL Linq | 更新日期: 2023-09-27 18:07:57

这是我的数据库:

http://s29.postimg.org/9xgvulvl3/Untitled.jpg

这是我的编码:

public ActionResult Index()
    {
        IList<TicketListModel> TicketList = new List<TicketListModel>();
        var TicketListQuery = from Tickets in db.Tickets
                              select new TicketListModel
                              {
                                  Number = Tickets.Number,
                                  RequestDate = Tickets.RequestDate,
                                  Applicant = (from Applicants in db.Employees where Applicants.ID = Tickets.ID select new { Applicants.Name }).First(),
                                  ComName = ComLibs.Name,
                                  ProbType = Tickets.ProbType,
                                  ProbDetail = Tickets.ProbDetail,
                                  Status = Tickets.Status
                              };
        TicketList = TicketListQuery.ToList();
        return View(TicketList);
    }

我想显示Applicant name，但在表Ticket中，申请人是int。因此，只需在表Employee中选择申请人名称，其中申请人ID等于雇员ID，然后选择雇员名称。

Linq to SQL " 2 Query "

也许你可以使用Linq Join

IList<TicketListModel> TicketList = (from t in db.Tickets
                        join e in db.Employees on t.ID equals e.ID
                        select new TicketListModel
                        {
                            Number = t.Number,
                            RequestDate = t.RequestDate,
                            Applicant = e.Name,
                            ComName = ComLibs.Name,
                            ProbType = t.ProbType,
                            ProbDetail = t.ProbDetail,
                            Status = t.Status
                        }).ToList();

试试这个

public ActionResult Index()
{
    IList<TicketListModel> TicketList = new List<TicketListModel>();
    var TicketListQuery = from t in db.Tickets
                          select new TicketListModel
                          {
                              Number = t.Number,
                              RequestDate = t.RequestDate,
                              Applicant = (from a in db.Employees where a.ID = t.ID select a.Name).FirstOrDefault(),
                              ComName = ComLibs.Name,
                              ProbType = t.ProbType,
                              ProbDetail = t.ProbDetail,
                              Status = t.Status
                          };
    TicketList = TicketListQuery.ToList();
    return View(TicketList);
}

或

public ActionResult Index()
{
    IList<TicketListModel> TicketList = new List<TicketListModel>();
    var TicketListQuery = from t in db.Tickets
                          from a in db.Employees
                          where t.ID == a.ID
                          select new TicketListModel
                          {
                              t.Number,
                              t.RequestDate,
                              Applicant = a.Name,
                              ComName = ComLibs.Name,
                              t.ProbType,
                              t.ProbDetail,
                              t.Status
                          };
    TicketList = TicketListQuery.ToList();
    return View(TicketList);
}

看起来你有正确的外键约束，你正在使用实体框架，所以我认为你应该能够简化和这样做:

public ActionResult Index()
{
    IList<TicketListModel> TicketList = new List<TicketListModel>();
    var TicketListQuery = from t in db.Tickets
                          select new TicketListModel
                          {
                              Number = t.Number,
                              RequestDate = t.RequestDate,
                              Applicant = t.Applicant.Name,
                              ComName = t.ComLib.Name,
                              ProbType = t.ProbType,
                              ProbDetail = t.ProbDetail,
                              Status = t.Status
                          };
    TicketList = TicketListQuery.ToList();
    return View(TicketList);
}