Querying Database MVC 4

Question

I'm learning MVC from sample application MVC Music Store. It has two database tables, one called 'Order' and the other 'OrderDetails'. If an order is made and a customer selects more than one item, after checkout each item bought creates a new row in the OrderDetails database and the OrderDetailsID will increment.

The OrderDetail database looks like:

http://imagebin.org/221494

I want to write a controller that will just pass all rows which have the same particular OrderId. For example, in that database there are 3 rows each with the same OrderId '3'.

How can I do this and pass it to a razor view? I've tried:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcMusicStore.Models;

namespace Test.Controllers
{
public class OrderDetailsController : Controller
{
    private MusicStoreEntities db = new MusicStoreEntities();

          //
    // GET: /OrderDetails/Details/5

    public ActionResult Details(int id = 0)
    {
        OrderDetail orderdetail = db.OrderDetails.Find(id);
        if (orderdetail == null)
        {
            return HttpNotFound();
        }
        return View(orderdetail);
    }

This will return a single result based on OrderDetailsId and not several results all with the same OrderId. I should be able to visit ....:11091/orderdetails/Details/3 and see not one result but a list of three different albums and their prices.

Is there a simple way to do this? Thanks for advice.

Answer 1

I'm guessing you want the LINQ query to do this.

Original Answer (just the OrderItems)

The following should suffice, but note that you will need to change your razor view Model type from OrderDetail to IList<OrderDetail>.

public ActionResult Details(int id = 0)
{
    var orderdetails = db.OrderDetails.Where(od => od.OrderId == id).ToList();
    if ((orderdetails == null) || (!orderdetails.Any()))
    {
        return HttpNotFound();
    }
    return View(orderdetails);
}

Edit

Because you are now passing a collection to the view, you need to iterate through the items to render it. That said, it might actually make sense not to fetch just the OrderDetails for an Order, but instead, fetch the Order and .Include() all of the associated OrderDetails, and then pass the graph to the view. I've assumed that you've set up the relationship between Order and OrderDetails in Entity Framework.

Controller

public ActionResult Details(int id = 0)
{
    var orderWithDetails = db.Orders
                             .Include(o => o.OrderDetails)
                             .FirstOrDefault(o => o.OrderId == id);
    if (orderWithDetails == null)
    {
        return HttpNotFound();
    }
    return View(orderWithDetails);
}

View (Takes the Order with the OrderDetails already eager-loaded)

@model MvcMusicStore.Models.Order

... Show Order level fields here

<div class="display-label">
   @Html.DisplayNameFor(model => model.Username)
</div>
<div class="display-field">
  @Html.DisplayFor(model => model.Username)
</div>

... Show OrderDetail level fields here, e.g. in a table or grid

@foreach (var orderItem in model.OrderDetails) {

  <div class="display-label">
       @Html.DisplayNameFor(orderItem => orderItem.OrderItemField1)
  </div>
  <div class="display-field">
      @Html.DisplayFor(orderItem => orderItem.OrderItemField1)
  </div>
}

Answer 2

public ActionResult Details(int id = 0)

{
    OrderDetail orderdetail = db.OrderDetails.Where(o => o.Id == id).ToList();
    if (orderdetail == null)
    {
        return HttpNotFound();
    }
    return View(orderdetail);
}

In your view...

Depending on your Entity. Your view will use @model List

Then iterate thorugh the list:

@foreach(var i in Model) { Html.DisplayFor(x => i.Name) }

or just YourProject.Model.xxx

@foreach(var i in Model.YourList) { Html.DisplayFor(x => i.Name) }

Answer 3

In terms of SQL, this would be (I have invented the field names)

select orderdetails.title, orderdetails.quant
from orderdetails inner join orders
on orderdetails.ord = orders.ord
where orders.ordname = :p1

P1 is a parameter which would accept the order's name/number