How to join two tables using Entity Framework

Question

Controller:

public ActionResult Index()
{
        var Cs = new List<Customer>();

        using (JoinEntities db = new JoinEntities())
        {
            Cs = (from p in db.Customers
                  join e in db.Orders on p.ID equals e.Customer_id
                  where p.Name == "Rama"
                  select p).ToList();
        }

        return View(Cs);
}

View:

@model IEnumerable<JOIN.Models.Customer>
@{
    ViewBag.Title = "Home Page";
}
<table class="table table-condensed table-hover">
   <thead>
     <tr>
        <td>First Name</td>
        <td>salary</td>
        <td>age</td>
        <td>amount</td>
    </tr>
   </thead>
   <tbody>
   @foreach (var per in Model)
   {
     <tr>
        <td>@per.ID</td>
        <td>@per.Name</td>
        <td>@per.Age</td>
        <td>@per.Amount</td>
     </tr>
    }
</tbody>
</table>

This above code view is it taking only one table columns how can I get the other table columns in customer table id is the primary key and order table customer_id is the foreign key

customer table: id, name, age, salary columns
order table: oid, date, customer_id, amount columns

You can create viewmodel class with property of both customer and order table and can use that view model in select clause — Abhishek
– Abhishek, Commented Apr 23, 2019 at 5:02

Abhishek · Accepted Answer · 2019-04-23 05:09:43Z

1

Create Another View Model as below

public class CustomerOrderViewModel
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public double  Salary { get; set; }

        public int OrderId { get; set; }
        public DateTime Date { get; set; }
        public double  Amount { get; set; }
    }

And Update your Index method as below

public ActionResult Index()
    {
        using (JoinEntities db = new JoinEntities())
        {
            var customerOrderList = (from p in db.Customers
                  join e in db.Orders on p.ID equals e.Customer_id
                  where p.Name == "Rama"
                  select new CustomerOrderViewModel {
                      CustomerId = p.Id,
                      Name = p.Name,
                      Age= p.Age,
                      Salary = p.Salary,
                      OrderId= e.Id,
                      Date= e.Date,
                      Amount = e.Amount
                  }).ToList();
            return View(customerOrderList);
        }        
    }

And Change the view model of you view to

@model IEnumerable<JOIN.Models.CustomerOrderViewModel>

answered Apr 23, 2019 at 5:09

Abhishek

1,0043 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rama Over a year ago

thank you so much bro its working fine now in your code above using statement u,r not adding List

Hien Nguyen · Accepted Answer · 2019-04-23 05:05:18Z

0

You need create another model like

class MyModel{
     public string Name{get;set;}
     public DateTime Date {get;set;}
}

And change in select query:

var Cs = new List<MyModel>();

using (JoinEntities db = new JoinEntities())
        {
            Cs = (from p in db.Customers
                  join e in db.Orders on p.ID equals e.Customer_id
                  where p.Name == "Rama"
                  select new MyModel {
                     Name = p.Name,
                     Date = e.date
                  }).ToList();
        }

answered Apr 23, 2019 at 5:05

Hien Nguyen

19k7 gold badges57 silver badges70 bronze badges

Collectives™ on Stack Overflow

How to join two tables using Entity Framework

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related