re3el re3el - 4 months ago 14
ASP.NET (C#) Question

How to pass join sql query table result to another method?

I am working in ASP.NET MVC using Entity Framework. I have two tables,

AppointmentDb
and
StudentDb
in my db. I wish to join them in the controller and pass the result to another action method within the controller. What is the return type of this new table that I pass in
Details
action?

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


Edit 1:

From the answers below, I edited to:

public ActionResult Details(IQueryable<Appointment> appointment)
{

return View(appointment);
}


I am still unable to call the columns as desired below. Some of them like
AppointmentID
are from
AppointmentDb
and the rest are from
StudentDb

Below is the View
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>


How should I make this
View
work?

Edit 2:

I have made the edits as suggested in Edit 1. There is yet another small error in my complete
View.cshtml
. This is the error I receive
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>

Answer

Your query code returns IQueryable<AppointmentDb>:

 public ActionResult Details(IQueryable<AppointmentDb> innerJoinQuery)
 {           
        return View();
 }

Approach 2:

Better execute your query first:

var innerJoinQuery = (from appointment in AppointmentDb
                     join student in StudentDb 
                     on appointment.StudentFirstName equals student.FirstName
                     select appointment).ToList();

Then pass the resulting list:

public ActionResult Details(List<AppointmentDb> innerJoinQuery)
{           
    return View();
}

Edit:

You need to create a class to hold data from multiple tables:

class MyViewModel
{
    public int AppointmentID { set; get; }
    public string StudentFirstName { set; get; }
    // continue the rest
}

Then change your query to:

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