Reputation:
Good evening all.
I created a join using between three tables, one of which is a junction table. I used a combination of LinqPad and Linquer to test my LINQ query. My C# code doesnt generate an error and my two item fields (Product_ID, Title) are rendered just fine. What Im trying to do is render the contents from the other table (tblAuthor.FirstName) with razor.
// GET: TestProducts
public ActionResult Index()
{
var products = (from pd in db.tblProducts
join od in db.tblAuthorProducts on pd.Product_ID equals od.Product_ID
orderby pd.Product_ID
select new
{
pd.Title,
pd.Description,
pd.Start_FY,
pd.ProductType,
od.tblAuthor.FirstName,
od.tblAuthor.LastName
}).ToList();
return View(products.ToList());
// Original effort
//var tblProduct = db.tblProducts.Include(t => t.tblAuthorProducts);
//var products = (from prod in db.tblProducts
// join auProd in db.tblAuthorProducts on prod.Product_ID equals auProd.Product_ID
// join au in db.tblAuthors on auProd.Author_ID equals au.Author_ID
// orderby prod.Product_ID
// select new
// {
// ProductID = prod.Product_ID,
// Title = prod.Title,
// Author = au.LastName,
// }).ToList();
//return View(products.ToList());
//var tblTitles = db.tblTitles.Include(t => t.tblAuthor).Include(t => t.tblRegion);
}
//Desired render
<table class="table">
<tr>
<th>Product ID</th>
<th>Title</th>
<th>Author</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.Product_ID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Title)
</td>
<td>
// LastName
</td>
</tr>
}
</table>
So I tested my LINQ query in a Console app and all the information I was seeking to join worked fine.
// LINQ
var products = (from pd in db.tblProducts
join aupd in db.tblAuthorProducts on pd.Product_ID equals aupd.Product_ID
join au in db.tblAuthors on aupd.Author_ID equals au.Author_ID
orderby pd.Product_ID
select new
{
pd.Title,
pd.Description,
pd.Start_FY,
pd.ProductType,
au.FirstName,
au.LastName
}).ToList();
foreach (var item in products)
{
Console.WriteLine("{0}", item);
Console.ReadKey();
}
So Im still using the same model. Once I did the above and set my return View(products); I was able to access the tblAuthor fields.
Upvotes: 1
Views: 932
Reputation: 5294
You can't pass anonymous
object in view this way. At first if you need create a ViewModel
.
Example: ViewModel:
public class ProductViewModel{
public string Title { get; set; }
public string Description { get; set; }
public string Start_FY { get; set; }
public string ProductType { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
Action:
public ActionResult Index()
{
var products = (from pd in db.tblProducts
join aupd in db.tblAuthorProducts on pd.Product_ID equals aupd.Product_ID
join au in db.tblAuthors on aupd.Author_ID equals au.Author_ID
orderby pd.Product_ID
select new ProductViewModel
{
pd.Title,
pd.Description,
pd.Start_FY,
pd.ProductType,
au.FirstName,
au.LastName
}).ToList();
return View(products);
}
View:
@model IEnumerable<ProductViewModel>
<table class="table">
<tr>
<th>Product ID</th>
<th>Title</th>
<th>Author</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.Product_ID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Title)
</td>
<td>
@Html.DisplayFor(modelItem => item.FirstName)
</td>
</tr>
}
</table>
although if you need to pass anonimus object to view you can follow this link.
Upvotes: 0
Reputation: 4067
If you have a junction table that means that one product can have many authors, but the select you're using implies that there is only one author behind a product. If that is really the case, there's no need for a junction table. You could just set a authorId field in the product table.
A product needs a list of authors, not just one.
On a side note: Ideally you would want to seperate the concerns.
Upvotes: 1