MVC3 cannot display other table value using foreign key

Question

I am trying to display two values in a entity by using two foreign keys.

I have three tables; one of table is Product table.

Two tables are Category and Model for displaying these value 'name', and 'modelName'.

When I use LINQ, I was using this coding before adding the Model entity.

var product = from a in db.Product.Include(a => a.Category)
                      select a;

How can I add Model entity in here?

such as

var product = from a in db.Product.Include(a => a.Category, a => a.Model)
                      select a;

Is it possible to write?

Here is my models.

--Prodruct.cs--

public class Product
{
    [Key] public int productId { get; set; }

    [Required(ErrorMessage = "Please select category")]
    public int categoryId { get; set; }

    [Required(ErrorMessage = "Please select model")]
    public int modelId { get; set; }

    [DisplayName("Model name")]
    public String model { get; set; }

    public virtual Category Category { get; set; }
    public virtual Model Model { get; set; }
}

--Category.cs--
public class Category
{
    [Key] public int categoryId { get; set; }
    public String name { get; set; }
}

--Model.cs--
public class Model
{
    [Key] public int modelId { get; set; }
    public String name { get; set; }
}

--RentalDB.cs--
public class rentalDB : DbContext
{
    public DbSet<Product> Product { get; set; }
    public DbSet<Model> Model { get; set; }
    public DbSet<Customer> Customer { get; set; }
    public DbSet<Order> Order { get; set; }
    public DbSet<Cart> Cart { get; set; }
    public DbSet<Category> Category { get; set; }
    public DbSet<OrderDetails> OrderDetails { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

Please let me know how to put inner join(?) in LINQ.

Thank you.

Answer 1

Is this what you need in your ProductController.cs?...

    public ViewResult index(int param_categoryId, int param_modelId)
    {
        List<Product> locvar_CollectionOfProduct
            = getCollectionOfProduct(param_categoryId, param_modelId);

        return View("index", locvar_CollectionOfProduct);
    }

    private List<Product> getCollectionOfProduct(int param_categoryId, int param_modelId)
    { 
        return db.Product.Where(a => a.categoryId == param_categoryId && a.modelId == param_modelId).ToList();
    }

    public void Product_Save(List<Product> param_CollectionOfProduct)
    {
        if (Model.IsValid)
        {
            foreach (Product i_Product in param_CollectionOfProduct)
            {
                Product locvar_Product = null;

                if (i_Product.productId == null || i_Product.productId == 0)
                {
                    locvar_Product = new Product();
                }
                else
                {
                    locvar_Product = new Product{productId = i_Product.productId};
                    db.Product.Attach(locvar_Product)
                }

                locvar_Product.categoryId = i_Product.categoryId;
                locvar_Product.modelId = i_Product.modelId;

                if (i_Product.productId == null || i_Product.productId == 0)
                {
                    db.Product.Add(locvar_Product);
                }
            }

            db.SaveChanges();
        }
    }

and then in your "Views\Product\index.cshtml" view you can iterate through these. I'll put them in a table for you:

    @using insert_entity_reference_here.Models;
    @model List<Product>

    @{
        List<Product> param_CollectionOfProduct = Model;
    }

    @using (Ajax.BeginForm("Product_Save", "Product", null, new AjaxOptions { HttpMethod = "POST" }))
    {

    <table style="width:100%">

        <tr>
            <th>
                Category Name
            </th>
            <th>
                Model Name
            </th>
        </tr>

        @if(Model.Count() > 0)
        {

            for( i_Product = 0 ; i_Product < Model.Count() ; i_Product++ )
            {

                @Html.HiddenFor(modelItem => param_CollectionOfProduct[i_Product].productId)

                <tr>
                    <td>
                        @Html.HiddenFor(modelItem => param_CollectionOfProduct[i_Product].Category.categoryId)
                        @Html.EditorFor(modelItem => param_CollectionOfProduct[i_Product].Category.Name, new { style="width:100%" })
                        @Html.ValidationMessageFor(modelItem => param_CollectionOfProduct[i_Product].Category.Name)
                    </td>
                    <td>
                        @Html.HiddenFor(modelItem => param_CollectionOfProduct[i_Product].Model.modelId)
                        @Html.EditorFor(modelItem => param_CollectionOfProduct[i_Product].Model.Name, new { style="width:100%" })
                        @Html.ValidationMessageFor(modelItem => param_CollectionOfProduct[i_Product].Model.Name)
                    </td>
                </tr>
            }
        }

    </table>

    <input type="submit">Save</input>

    }

Let me know if I'm on the right track. If so, I should be able to help you some more.

Best Regards, Nick

Answer 2

I think you might want the following since Include returns IQueryable:

var product = from a in db.Product.Include(a => a.Category).Include(a => a.Model)
                  select a;