Retrieve data from multiple tables in JSON format in ASP.Net MVC

MrPattis...

SOLVED CLOSED ACTIVE ANSWERED
User: MrPattison
Posted: on Aug 15, 2021 10:52 PM
Forum: ASP.Net MVC
Answer: 1
Views: 8229
Sample Code: Download

How to retrieve data from multiple tables in JSON.

I have a system which records the cars a customer has bought

Tables are:

Customer - Id, Name

CarPurchases - Id, CustomerID, ManufacturerID, ColorChosen

Manufacturer - Id, ManName

The Manufacturer table is a look up table with car manufacturers (Audi, BMW, Mercedes, Ferrari etc)

One customer can purchase multiple cars (maybe one for themselves, wife etc) but we note down the color.

So far this is ok. I use EF 6 to retrieve the data:

myContext.CarPurchases.GetAll(); On a webpage all looks ok and as required however now i need to have this data available in Json so i created a webAPI project. When i return the data with the above code the JSON is heavily nested and doesnt look very friendly to use.

How can i rerieve the data so it return the

CustomerName, ManufacturerName, ColorChosen

Example of some data

Customer

Id - Name

1 Tony

2 Bob

CarPurchases

Id - CustomerID - ManufacturerID - ColorChosen

1 1 2 Blue

2 1 2 Red

3 1 4 Black

4 2 2 Custom Yellow

Manufacturer

Id - ManName

1 Audi

2 BMW

3 Mercedes

4 Ferrari

So again how do i retrieve this data in a nice format without having heavy nesting when i get CarPurchases? Do i create a new Model and reference this in or craete a separate method with Joins etc? If you can provide any examples that would be helpful.

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Aug 16, 2021 02:30 AM
Report

Hi MrPattison,

Create a new Model and reference this with Joins.

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

public class CustomerModel
{
    public string ContactName { get; set; }
    public string City { get; set; }
    public string EmployeeName { get; set; }
    public int OrderID { get; set; }
    public string ShipName { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        using (NorthwindEntities entites = new NorthwindEntities())
        {
            List<CustomerModel> model = (from ord in entites.Orders
                                            join cus in entites.Customers on ord.CustomerID equals cus.CustomerID
                                            join emp in entites.Employees on ord.EmployeeID equals emp.EmployeeID
                                            where cus.CustomerID.Equals("TOMSP")
                                            select new CustomerModel
                                            {
                                                ContactName = cus.ContactName,
                                                City = cus.City,
                                                EmployeeName = emp.FirstName + " " + emp.LastName,
                                                OrderID = ord.OrderID,
                                                ShipName = ord.ShipName
                                            }).ToList();
            return View(model);
        }
    }
}

View

@using Table_MVC.Models
@model IEnumerable<CustomerModel>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <table>
        <tr>
            <th>Contact Name</th>
            <th>City</th>
            <th>Employee Name</th>
            <th>OrderID</th>
            <th>Ship Name</th>
        </tr>
        @foreach (CustomerModel customer in Model)
        {
            <tr>
                <td>@customer.ContactName</td>
                <td>@customer.City</td>
                <td>@customer.EmployeeName</td>
                <td>@customer.OrderID</td>
                <td>@customer.ShipName</td>
            </tr>
        }
    </table>
</body>
</html>

Screenshot

Retrieve data from multiple tables in JSON format in ASP.Net MVC

Company

Explore

Follow us

Contact Us