如何将联接sql查询表的结果传递给其他方法
本文关键字:结果 方法 其他 查询表 sql | 更新日期: 2023-09-27 18:01:04
我使用实体框架在ASP.NET MVC中工作。我的数据库中有两个表,AppointmentDb
和StudentDb
。我希望将它们加入控制器中,并将结果传递给控制器中的另一个操作方法。我在Details
操作中传递的这个新表的返回类型是什么?
public ActionResult Index(AdminViewModel model)
{
if (ModelState.IsValid)
{
var innerJoinQuery = from appointment in AppointmentDb
join student in StudentDb on appointment.StudentFirstName equals student.FirstName
select appointment;
return View("Details",innerJoinQuery);
}
}
public ActionResult Details(?????? innerJoinQuery)
{
return View();
}
编辑1:
根据以下答案,我编辑为:
public ActionResult Details(IQueryable<Appointment> appointment)
{
return View(appointment);
}
我仍然无法根据需要调用下面的列。其中一些像AppointmentID
来自AppointmentDb
,其余来自StudentDb
以下是视图Details.cshtml
:
@model IQueryable<OdeToFood.Entities.Appointment>
@{
ViewBag.Title = "Details";
}
<h2>Index</h2>
@Html.ActionLink("Back to Search", "Index")
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.AppointmentID)
</th>
<th>
@Html.DisplayNameFor(model => model.StudentFirstName)
</th>
<th>
@Html.DisplayNameFor(model => model.)
</th>
<th>
@Html.DisplayNameFor(model => model.StudentID)
</th>
<th>
@Html.DisplayNameFor(model => model.AppointmentDate)
</th>
<th>
@Html.DisplayNameFor(model => model.AppointmentTime)
</th>
<th>
@Html.DisplayNameFor(model => model.Status)
</th>
</tr>
</table>
我应该如何使View
工作?
编辑2:
我已经按照编辑1中的建议进行了编辑。在我完整的View.cshtml
中还有另一个小错误。这是我收到foreach cannot operate on Variables of type OdeToFood.ViewModels.AdminDetailsViewModel because it does not contain definition for GetEnumerator
的错误
@model AdminDetailsViewModel
@{
ViewBag.Title = "Details";
}
<h2>Index</h2>
@Html.ActionLink("Back to Search", "Index")
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.AppointmentID)
</th>
<th>
@Html.DisplayNameFor(model => model.StudentFirstName)
</th>
<th>
@Html.DisplayNameFor(model => model.StudentLastName)
</th>
<th>
@Html.DisplayNameFor(model => model.StudentID)
</th>
<th>
@Html.DisplayNameFor(model => model.AppointmentDate)
</th>
<th>
@Html.DisplayNameFor(model => model.AppointmentTime)
</th>
<th>
@Html.DisplayNameFor(model => model.Status)
</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.AppointmentID)
</td>
<td>
@Html.DisplayFor(modelItem => item.StudentFirstName)
</td>
<td>
@Html.DisplayFor(modelItem => item.StudentLastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.StudentID)
</td>
<td>
@Html.DisplayFor(modelItem => item.AppointmentDate)
</td>
<td>
@Html.DisplayFor(modelItem => item.AppointmentTime)
</td>
<td>
@Html.DisplayFor(modelItem => item.Status)
</td>
</tr>
}
</table>
您的查询代码返回IQueryable<AppointmentDb>
:
public ActionResult Details(IQueryable<AppointmentDb> innerJoinQuery)
{
return View();
}
方法2:
最好先执行查询:
var innerJoinQuery = (from appointment in AppointmentDb
join student in StudentDb
on appointment.StudentFirstName equals student.FirstName
select appointment).ToList();
然后传递结果列表:
public ActionResult Details(List<AppointmentDb> innerJoinQuery)
{
return View();
}
编辑:
您需要创建一个类来保存来自多个表的数据:
class MyViewModel
{
public int AppointmentID { set; get; }
public string StudentFirstName { set; get; }
// continue the rest
}
然后将您的查询更改为:
from appointment in AppointmentDb
join student in StudentDb
on appointment.StudentFirstName equals student.FirstName
select new MyViewModel()
{
AppointmentID = AppointmentDb.AppointmentID,
StudentFirstName = StudentDb.StudentFirstName
// continue the rest
};
您可以使用对象类型
public ActionResult Details(object innerJoinQuery)
{
return View();
}