CODEWITHSUNDEEP
c#entity-frameworkasp.net-mvc-4
Chris
Chris

Reputation: 47

ASP.NET MVC 4 Joining two tables

please may you take a look at my code and point me in the right direction. I have 2 tables : Product and vProductAndDescription. I need the Name and LisPrice from Product and the Description from vProductAndDescription joined by their ProductID respectively. Am I on the right track at achieving this in my view? Because so far it is just crashing.

Controller:

public ActionResult Index()
        {
            string query = "SELECT v.Name, p.ListPrice, LEFT(v.Description, 20) from SalesLT.vProductAndDescription v, SalesLT.Product p WHERE v.ProductID = p.ProductID";
            var list = db.Database.SqlQuery<Product>(query);              
            var result = from a in list.ToList()
                    join b in db.vProductAndDescriptions on a.ProductID equals b.ProductID
                    select new
                    {
                        c = a.Name,
                        d = a.ListPrice,
                        e = b.Description
                    };
            return View(result.ToList());
}

View:

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<table>
    <tr>
        <th>Name
        </th>
        <th>Price (R)
        </th>
        <th>Description
        </th>
        <th></th>
    </tr>

@foreach (var item in ViewData.Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.ListPrice)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Description)
        </td>
    </tr>
}
</table>

Upvotes: 0

Views: 23180

Answers (1)

Claies
Claies

Reputation: 22323

Your call var list = db.Database.SqlQuery<Product>(query); trying to create a List of type Product but your actual query doesn't return a ProductID parameter necessary to create the Product type. Also, what type are you expecting from the line 

var result = from a in list.ToList()
                join b in db.vProductAndDescriptions on a.ProductID equals b.ProductID
                select new
                {
                    c = a.Name,
                    d = a.ListPrice,
                    e = b.Description
                };

To start with, you need a ViewModel to hold your values in, because you aren't passing a whole existing object to your view.

public class ProductDescriptionsViewModel {
    public string Name {get;set;}
    public string ListPrice {get;set;}
    public string Description {get;set;}
}

in your View code at the top:

@model IEnumerable<YourFullNamespace.ProductDescriptionsViewModel>

in your query you are actually making 2 calls on the database, let's see if we can re-arrange things to get one call:

string query = "SELECT v.Name as Name, p.ListPrice as ListPrice, LEFT(v.Description, 20) as Description from SalesLT.vProductAndDescription v, SalesLT.Product p WHERE v.ProductID = p.ProductID";
var viewModel = db.Database.SqlQuery<ProductDescriptionsViewModel>(query);

Upvotes: 2

Related Questions