MVC with Entity Framework … a twist on Brad Abram’s example

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)

Then I can click on a customer and see a list of their orders

 

Then drill in to see the order details. (The Edit button is not implemented yet, in case you were wondering!)

 

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 categoriesproducts.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.MvcImports System.LinqImports MvcApplication.awModelNamespace MvcApplication.Controllers Public Class SalesOrdersControllerInherits System.Web.Mvc.Controller
<ControllerAction()> _Sub Index()REM Add Action Logic hereEnd Sub
‘example URL:http://localhost:xxxx/SalesOrders/Customers<ControllerAction()> _Public Sub Customers()Using aw = New awEntitiesDim _customers = aw.Customers. _
Where(Function(c) c.SalesOrderHeaders.Any). _
OrderBy(Function(c) c.CompanyName).ToListRenderView(“Customers”, _customers)End UsingEnd Sub
‘example URL:http://localhost:xxxx/SalesOrders/List/[CustomerID]<ControllerAction()> _Public Sub List(ByVal id As String)Using aw = New awEntitiesDim _salesorders = (From cust In aw.Customers _Where cust.CustomerID = id _Select cust.SalesOrderHeaders).FirstorDefault.ToListRenderView(“SalesOrdersbyCustomer”, _salesorders)End UsingEnd Sub
‘example URL:http://localhost:xxxx/SalesOrders/List/#### (order number)<ControllerAction()> _Public Sub Detail(ByVal id As String)Using aw = New awEntitiesDim _order = (From ord In aw.SalesOrderHeaders.Include(“SalesOrderDetails.Product”) _ Where ord.SalesOrderID = id _ Select ord).FirstDim _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.FirstRenderView(“SalesOrder”, _order2)End UsingEnd Sub End ClassEnd 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!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

5 thoughts on “MVC with Entity Framework … a twist on Brad Abram’s example

  1. Can you remove the ControllerAction attribute as this is confusing since I’ve found out that it’s no longer required?Thanks for the tutorial !

  2. I am quite new to entity framework, after watched some videos it seems that 1 set of data will require 1 EDMX. What happen if I got 100 types of dataset and then my database schema changed? Is there a utility to update them both? and how to make sure it won’t break the application ?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.