I followed the great walkthrough on Brad's blog showing how to use MVC together with Entity Framework. But I did it my own way - in VB, using a different database and trying to write more effective EF queries. I have a simple solution that renders these views from the AdventureWorksLT database:
Start with a list of customers who have orders in the system (that's less than 10% of the full customer list)
.png)
Then I can click on a customer and see a list of their orders
.png)
Then drill in to see the order details. (The Edit button is not implemented yet, in case you were wondering!)
.png)
So here is what's significantly different from Brad's walkthrough.
- My EDM is created from AdventureWorksLT.
- The relationship from AW's SalesOrderHeaders to Customer is the same as the relationship from Northwind's Products to Category. Therefore, where he use Products, I use SalesOrderHeaders and where he uses Categories, I use Customers.
- One of the keys for getting data easily to a view is that we need to send ONE object (and not an anoymous type) to the view. Yet what we really desire in the case of the List of Order (which also has the Customer Name) and the list of details (which also has data from the order and the customer) is an object graph.
So on the 2nd page, I need to pass in an set of orders with their related Customer entity so that I can have access to the customer name.
On the 3rd page, I need to pass in a set of order details with their related Order entiteis AND the order's related customer.
Brad achieves this by starting with the desired entity and then using entity references and some scary looking LINQ to Entities queries.
List<Product> products =TheProducts.Where(c => c.Category.CategoryName == category).ToList();
//prepare the view by explicitly loading the categories
products.FindAll(p => p.Category == null).ForEach(p => p.CategoryReference.Load());
Only because I've spent a lot of time with LINQ to Entities, do I happen to know a little trick.
If I start with the "parent", i.e. category and query it's property collection (i.e. products), when I return the property collection, the "parent" entity is still attached.
So taking Brad's query, I can get the same effect with this query (still in C#):
var _prod = Northwind.Categories.
Where(c => c.CategoryName == id).
OrderBy(c => c.CategoryName).
Select(c => c.Products).
First().ToList();
(Update: turns out this only works because of a bug which will get fixed - see THIS POST for an even better way!)
This returns a list of products that belong to the category. However I do not have to do any extra loading to get to Product.Category.CategoryName. Beasue my query began with the Category, it's already there. (I learned this by trial and error by the way.)
Therefore, in my SalesOrderController (my versoin of his productController), the List ControllerAction code is a little different.
I use the same type of querying to get the order details.
Another thing that I spent some time thinking about and asking about was about the ObjectContext. In a web app you want an ojbectcontext to be as short-lived as possible. I notice that Brad was instantiating in the class level declarations. This is okay because in the background, MVC instantiates the class for each ControllerAction and then disposes it when it's finished. It doesn't hang around waiting for another method call. (This is one of the key premises of MVC. As Kevin Hoffman explained to me, it works in "short bursts" long enough to get something out to the browser. I have much to learn!)
Brad uses the CategoryName as the basis for the view creation so that he gets a pretty URL http://host/products/list/Beverages.
I'm still seeing if there is a way around this, but I don't like querying on a string like this. I like my keys! So I'm passing in CustomerID and SalesOrderID and my urls aren't as pretty.
Here is what my controller looks like and you can see my queries that populate the Customer List, the customer's order list and the order's detail list.
Imports System.Web.Mvc
Imports System.Linq
Imports MvcApplication.awModel
Namespace MvcApplication.Controllers
Public Class SalesOrdersController
Inherits System.Web.Mvc.Controller <ControllerAction()> _
Sub Index()
REM Add Action Logic here
End Sub 'example URL:http://localhost:xxxx/SalesOrders/Customers
<ControllerAction()> _
Public Sub Customers()
Using aw = New awEntities
Dim _customers = aw.Customers. _
Where(Function(c) c.SalesOrderHeaders.Any). _
OrderBy(Function(c) c.CompanyName).ToList
RenderView("Customers", _customers)
End Using
End Sub 'example URL:http://localhost:xxxx/SalesOrders/List/[CustomerID]
<ControllerAction()> _
Public Sub List(ByVal id As String)
Using aw = New awEntities
Dim _salesorders = (From cust In aw.Customers _
Where cust.CustomerID = id _
Select cust.SalesOrderHeaders).FirstorDefault.ToList
RenderView("SalesOrdersbyCustomer", _salesorders)
End Using
End Sub 'example URL:http://localhost:xxxx/SalesOrders/List/#### (order number)
<ControllerAction()> _
Public Sub Detail(ByVal id As String)
Using aw = New awEntities
Dim _order = (From ord In aw.SalesOrderHeaders.Include("SalesOrderDetails.Product") _
Where ord.SalesOrderID = id _
Select ord).First
Dim _order2 = (From cust In aw.Customers.Include("SalesOrderHeaders.SalesOrderDetails.Product") _
Where cust.SalesOrderHeaders.Any(Function(so As SalesOrderHeader) so.SalesOrderID = id) _
Select cust.SalesOrderHeaders).First.ToList.First
RenderView("SalesOrder", _order2)
End Using
End Sub
End Class
End Namespace
The markup is not really different from Brad's since I can drill from the ViewData into my references (Customer, Product, SalesOrder) thanks to my queries (which make me feel so clever!)
The last page just uses tables to do the trick.
<asp:Content ID="Content1" ContentPlaceHolderID="MainContentPlaceHolder" runat="server">
<h2><%=ViewData.Customer.CompanyName%></h2>
<h3>
<%=String.Format("Sales Order #: {0}", ViewData.SalesOrderNumber)%>
<%=String.Format("Order Date: {0:d}", ViewData.OrderDate)%>
<%=String.Format("Order Total: {0:C2}", ViewData.TotalDue)%>
</h3>
<table>
<tr><td style="width: 225px">Product</td><td style="width: 154px">Quantity</td>
<td style="width: 256px">Line Item Total</td></tr>
<% For Each detail As awModel.SalesOrderDetail In ViewData.SalesOrderDetails%>
<tr>
<td style="width: 225px"><%=detail.Product.Name%></td>
<td align="center" style="width: 154px"><%=detail.OrderQty%></td>
<td style="width: 256px"> <%=String.Format("{0:C2}", detail.LineTotal)%></td>
</tr>
<% Next%>
</table>
</asp:Content>
As an aside, this was my first time really playing with client side code in VS2008 and I am enamored of all the intellisense in there!