Display data from two tables and show it in page(.CSHTML) using EF Designer from DatabaseRSS

• • Reply
  rjpithwa

  Member

  108 Points

  112 Posts

  Display data from two tables and show it in page(.CSHTML) using EF Designer from Database

  Oct 31, 2014 04:33 AM|rjpithwa|LINK

  Hi,

  I am using EF Designer from Database in mu web application.

  I have two tables like,

  CREATE TABLE [dbo].[Product](
  	[ProductId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
  	[ProductName] [varchar](255) NULL,
  	[CreatedDate] [datetime] NULL
  )
  
  
  CREATE TABLE [dbo].[Orders]
  (
  	[OrderId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
  	[ProductId] [int] NULL  FOREIGN KEY([ProductId]) REFERENCES [dbo].[Product] ([ProductId])
  )

  So in diagram of edmx file two entities will display. and Model.Context.cs files look like ,

  //------------------------------------------------------------------------------
  // <auto-generated>
  //     This code was generated from a template.
  //
  //     Manual changes to this file may cause unexpected behavior in your application.
  //     Manual changes to this file will be overwritten if the code is regenerated.
  // </auto-generated>
  //------------------------------------------------------------------------------
  
  namespace EFDatabaseFirstApproch.Models
  {
      using System;
      using System.Data.Entity;
      using System.Data.Entity.Infrastructure;
      
      public partial class ProductEntities : DbContext
      {
          public ProductEntities()
              : base("name=ProductEntities")
          {
          }
      
          protected override void OnModelCreating(DbModelBuilder modelBuilder)
          {
              throw new UnintentionalCodeFirstException();
          }
      
          public virtual DbSet<Orders> Orders { get; set; }
          public virtual DbSet<Product> Product { get; set; }
      }
  }
  

  Connection String is like,

  <connectionStrings>
      <add name="ProductEntities" connectionString="metadata=res://*/Models.DataModel.csdl|res://*/Models.DataModel.ssdl|res://*/Models.DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost;initial catalog=dbProduct;user id=test;password=test;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    </connectionStrings>

  Controller :

          public ActionResult Index()
          {
              ProductEntities db = new ProductEntities();
              var prdData = from prd in db.Product
                            join ord in db.Orders on prd.ProductId equals ord.ProductId
                            select new 
                            { 
                              ProductId = prd.ProductId, 
                              ProductName = prd.ProductName, 
                              OrderId = ord.OrderId 
                            };
              return View(prdData.ToList());
          }
         

  I want view like this,

  @{
      ViewBag.Title = "Index";
  }
  
  <h2>Index</h2>
  
  <table>
      <tr>
          <th>
              Product Id
          </th>
          <th>
              Product Name
          </th>
          <th>
              Order Id
          </th>
          <th></th>
      </tr>
  
      @foreach (var item in ViewData.Model)
      {
          <tr>
              <td>
                  @Html.DisplayFor(modelItem => item.ProductId)
              </td>
              <td>
                  @Html.DisplayFor(modelItem => item.ProductName)
              </td>
              <td>
                  @Html.DisplayFor(modelItem => item.OrderId)
              </td>
          </tr>
      }
  </table>

  How can I get both tables data in view ?

• • Reply
  AidyF

  All-Star

  37441 Points

  9077 Posts

  Re: Display data from two tables and show it in page(.CSHTML) using EF Designer from Database

  Oct 31, 2014 05:15 AM|AidyF|LINK

  https://www.google.com/search?q=site%3aforums.asp.net+two+models

  I'm afraid I no longer use this forum due to the new point allocation system.
• • Reply
  avishek.kuma...

  Member

  110 Points

  28 Posts

  Re: Display data from two tables and show it in page(.CSHTML) using EF Designer from Database

  Oct 31, 2014 06:06 AM|avishek.kumar66|LINK

  Update Controller:

          public ActionResult Index()
          {
              ProductEntities db = new ProductEntities();
              var prdData = db.Orders.Include(o=>o.Product)
              return View(prdData.ToList());
          }

  Update View(Strongly Typed)

  @model IEnumerable<EFDatabaseFirstApproch.Models.Order>
  
  @{
      ViewBag.Title = "Index";
  }
  
  <h2>Index</h2>
  
  <table class="table">
      <tr>
          <th>
              Product Id
          </th>
          <th>
              Product Name
          </th>
          <th>
              Order Id
          </th>
      </tr>
  
  @foreach (var item in Model) {
      <tr>
          <td>
              @Html.DisplayFor(modelItem => item.ProductId)
          </td>
          <td>
              @Html.DisplayFor(modelItem => item.Product.ProductName)
          </td>
          <td>
              @Html.DisplayFor(modelItem => item.OrderId)
          </td>       
      </tr>
  }
  
  </table>
  

  pls mark as "Answered Question" if it is helpful.

  Thanks,

  Avishek

  mvc

• • Reply
  ehsansajjad4...

  Member

  420 Points

  116 Posts

  Re: Display data from two tables and show it in page(.CSHTML) using EF Designer from Database

  Oct 31, 2014 02:37 PM|ehsansajjad465|LINK

  Create a ViewModels folder in your solution and  create a ViewModel class and add properties of both class that you need in View :

  public class OrderVM
  {
  
   public int ProductId {get;set;}
   public string ProductName {get;set;}
   public int OrderId {get;set; }
  
  }
  
  
  
  

  then in your action create it this way:
  
  

  public ActionResult Index()
          {
              ProductEntities db = new ProductEntities();
              var prdData = from prd in db.Product
                            join ord in db.Orders on prd.ProductId equals ord.ProductId
                            select new OrderVM 
                            { 
                              ProductId = prd.ProductId, 
                              ProductName = prd.ProductName, 
                              OrderId = ord.OrderId 
                            };
              return View(prdData.ToList());
          }

  View:

  @{
      ViewBag.Title = "Index";
  }
  
  @model NameSpace.ViewModels.OrderVM
  
  <h2>Index</h2>
  
  <table>
      <tr>
          <th>
              Product Id
          </th>
          <th>
              Product Name
          </th>
          <th>
              Order Id
          </th>
          <th></th>
      </tr>
  
      @foreach (var item in Model)
      {
          <tr>
              <td>
                  @Html.DisplayFor(modelItem => item.ProductId)
              </td>
              <td>
                  @Html.DisplayFor(modelItem => item.ProductName)
              </td>
              <td>
                  @Html.DisplayFor(modelItem => item.OrderId)
              </td>
          </tr>
      }
  </table>

  model mvc

  Blog | LinkedIn | Stack Overflow | Facebook