how can get data from another Table

I am designing a project in asp.net mvc3, i have designed my database in sql server, add i am using ado.net.

This is my controller action

    public ViewResult ProductF开发者_开发知识库ormulationIndex()
    {

        return View(db.ProductFormulation.ToList());
    }

means i want to display all fields of ProductFormulation table. this is my table:-

and this is my productCategory Table

in my ProductFormulationIndex.cshtml i want to display Code of ProductCategory Table, not only id. So what should i do in controller or in Model for it ? you may suggest tutorial related to it. Thanks in advance.

---

You need a view model which is specifically designed for the view.

When defining your view models you shouldn't be thinking in terms of tables. SQL tables have absolutely no meaning in a view. Think in terms of what information you need to show and define your view models accordingly.
Therefore, You can define a view model like:

public class ProductInformation
{
...
public string CategoryCode {get; set;}
...
}  

Or public Category ProductCategory.
You can use AutoMapper to convert between your real models and the view model you have defined.

You can find a good tutorial at http://weblogs.asp.net/shijuvarghese/archive/2010/02/01/view-model-pattern-and-automapper-in-asp-net-mvc-applications.aspx

---

Although I may not answer your question you are touching on some interesting points.

I subscribe to the school of thought that says one should not query an object model but rather make use of a denormalized lightweght query layer.

You will pobably quickly run into the lazy-loading and projection issue: i.e. you do not always require the related data and you do not always require all the fields. A query layer takes care of this quite nicely since using a denormalized model means that when you do your query you do not need to do N-number of joins to get the related data. You will still at some point need to gather up the data but you actual view queries will be order fo magnitude faster.

Also, getting all the data (by joining) for your denormalized model is a once-off affair as opposed to doing it each and every time you display the relevant data on the front-end.

Hope that makes sense :)