Trying to render my LINQ query to a razor view

Question

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.

Answer 1

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.

Answer 2

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.

• Create a Model that maps to each table in your database.
• Create a Repository to get the data (with linq)
• Create a ViewModel for all possible views.
• Populate the ViewModel in the Controller
• Return the ViewModel to the View.