Display multiple Table records in 1 View in MVC

Gurjeet Singh

12y8k.NET

Hi,

I have 3 Tables

1 Table: Sale Table with Following columns

id, req_no, Bill_amt

2 Table : dtls Table with following columns

id,Req_no,brand,product

3 Table: bill Table with the following columns

id,req_no,Paid_amt

i have query in Sql but now i am using Linq so how can write same query in Linq

select m.req_no,m.Bill_Amt,d.Brand,d.Product,b.Paid_Amt from sale_mstr m, sale_dtls d, bill_dtls b where m.req_no=d.req_no
and m.req_no=b.req_no and d.Brand='Apple'

and display all records in View

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi12y ago
Accepted answer
hi,
I think this is problem of Anonymous type with in view bag
in this you can create poco class pass it in view as model
public class Mydata
{
    public int Id { get; set; }
    public string req_no { get; set; }
    public string product { get; set; }
    public string brand { get; set; }
    public double price { get; set; }
    public double Qty { get; set; }
    public double bill_amt { get; set; }
}
your action method become
public ActionResult Detail()
{
    var query = (from pd in cs.SaleDtls_entries
                    join od in cs.SaleMstr_entries on pd.req_no equals od.req_no
                    orderby od.Id
                    select new Mydata
                    {
                        Id = od.Id,
                        req_no = od.req_no,
                        product = pd.product,
                        brand = pd.brand,
                        price = pd.price,
                        Qty = pd.Qty,
                        bill_amt = od.bill_amt,
                    }).ToList();
    return View(query);
}
view
@model IEnumerable
@foreach(var list in Model)
{
write your code here....
}
hope this will help you.
Gurjeet Singh12y ago
Thanx Jignesh now it is working

Gurjeet Singh12y ago

ConnectionClass
namespace withoutModelEdmx.Models
{
    public class ConnectionClass :DbContext
    {
        public DbSet SaleMstr_entries { get; set; }
        public DbSet SaleDtls_entries { get; set; }
    }
}

namespace withoutModelEdmx.Models
{
     [Table("sale_dtls")]
    public class SaleDtls
    {
        public int Id { get; set; }
        public string req_no { get; set; }
        public string brand { get; set; }
        public string product { get; set; }
        public string model_no { get; set; }
        public int Qty { get; set; }
        public int price { get; set; }
        public List salesmstr { get; set; }
    }
}

namespace withoutModelEdmx.Models
{
    [Table("sale_mstr")]
    public class SaleMstr
    {
        public int Id { get; set; }
        public string req_no { get; set; }
        public string basicName { get; set; }
        public int bill_amt { get; set; }

    }
}

Contoller
ConnectionClass cs = new ConnectionClass();
        public ActionResult Detail()
        {
            var query = (from pd in cs.SaleDtls_entries
                     join od in cs.SaleMstr_entries on pd.req_no equals od.req_no
                     orderby od.Id
                     select new
                     {
                         od.Id,
                         od.req_no,
                         pd.product,
                         pd.brand,
                         pd.price,
                         pd.Qty,
                         od.bill_amt,
                     }).ToList();
            ViewBag.Entries = query.ToList();
            return View();
        }

If i Put Breakpoint on ( ViewBag.Entries = query.ToList()) all records display there but View returns error.

VIEW

@{
ViewBag.Title = "Detail";
Layout = "~/Views/Shared/_Layout.cshtml";
}

Detail

    @foreach (var list in ViewBag.Entries)
    {









    }

@list.Id

@list.bill_amt

@list.Brand

@list.product

@list.Price

@list.Qty

Jignesh Trivedi12y ago
hi,

multiple model in single view is not supported by MVC
You can create single new model that contain all type of class that you want as a property
and this newly created class you can use as model

example

class A {}
class B {}

class newModel
{
public A a {get;set;}
public B b {get;set;}
}

and in view
@model namespace.newModel

hope this will help you.
Gurjeet Singh12y ago
i have to add individual Model for each table or i can add single Model for multiple table
Jignesh Trivedi12y ago
hi,
try...
var p = from m in Contex.sale_mstr
join d in sale_dtls on m.req_no equals d.req_no
join b in bill_dtls on m.req_no equals b.req_no
where d.Brand=='Apple'
select new
{
m.req_no,
  m.Bill_Amt,
  d.Brand,
  d.Product,
  b.Paid_Amt
}

here contex is entity contex
hope this will help.