Hi enceladus,
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Model
CategoryModel
public class CategoryModel
{
public int CategoryID { get; set; }
public string CategoryName { get; set; }
public string Description { get; set; }
}
ProductModel
public class ProductModel
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public decimal UnitPrice { get; set; }
public int CategoryID { get; set; }
public CategoryModel Category { get; set; }
}
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
List<ProductModel> products = GetProducts();
foreach (ProductModel product in products)
{
CategoryModel category = GetCategory(product.CategoryID);
product.Category = category;
}
return View(products);
}
private List<ProductModel> GetProducts()
{
List<ProductModel> products = new List<ProductModel>();
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT TOP 10 ProductID,ProductName,UnitPrice,CategoryID FROM Products ORDER BY NEWID()";
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
products.Add(new ProductModel
{
ProductID = Convert.ToInt32(sdr["ProductID"]),
ProductName = sdr["ProductName"].ToString(),
UnitPrice = Convert.ToDecimal(sdr["UnitPrice"]),
CategoryID = Convert.ToInt32(sdr["CategoryID"])
});
}
con.Close();
}
}
return products;
}
private CategoryModel GetCategory(int categoryID)
{
CategoryModel category = new CategoryModel();
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT CategoryID,CategoryName,Description FROM Categories WHERE CategoryID = @CategoryID";
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@CategoryID", categoryID);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
category.CategoryID = Convert.ToInt32(sdr["CategoryID"]);
category.CategoryName = sdr["CategoryName"].ToString();
category.Description = sdr["Description"].ToString();
}
con.Close();
}
}
return category;
}
}
View
<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<IEnumerable<_185123_Multiple_Model_View.Models.ProductModel>>" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Index</title>
</head>
<body>
<table>
<tr>
<th>Product ID</th>
<th>Product Name</th>
<th>Price</th>
<th>Category Name</th>
<th>Description</th>
</tr>
<% foreach (var item in Model)
{ %>
<tr>
<td><%: item.ProductID %></td>
<td><%: item.ProductName %></td>
<td><%: String.Format("{0:F}", item.UnitPrice) %></td>
<td><%: item.Category.CategoryName %></td>
<td><%: item.Category.Description %></td>
</tr>
<% } %>
</table>
</body>
</html>
Screenshot
