how to write query in MVC controller joining 3 tables?

Jaima Joseph

Hi,Can anybody tell me how to write query to fetch one column value from a table comparing other two column values in that table with other two tables in an MVC Controller?

In my MVC view I need to show 5 column values from one table say POCList and the sixth value(localstock) to be displayed should be fetched from second table say StockMaster which is having matching values in 3rd table Inventorymaster.

My query should be like "select Qty from StockMaster where StockMaster.SKU==POCList.SKU && (StockMaster.SKU==InventoryMaster.SKU &&StockMaster.LocationId==InventoryMaster.LocationID"

I don't know how to write it as a MVC query....Also please tell me how to show this value in view along with other 5 values from POCList table...

Righ now my view is showing all the 5 column values fro POCList properly in my view using the below code

public ActionResult GeneratePOC()

{

var generatepoc = db.POCLists.Include(p => p.EmployeeMaster).Include(p => p.POCMaster).Include(p => p.QuoteMaster).Include(p=>p.SKUMaster);

return View(generatepoc.ToList());

}

Any help will be appreciated..Its urget...Thanks in advance.

3 Replies

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

Jaganathan Bantheswaran12y ago
Accepted answer
Hi,

It is not MVC query or MVC part. Its Entity Framework part.

It is better to use Linq to Entity.

var query = (from pl in db.POCLists
join sm in db.StockMaster on sm.SKU equals pl.SKU
join im in dm.InventoryMaster on im.SKU equals sm.SKU
where sm.LocationId == im.LocationId select new {
SomeProp1 = pl.SomeProp,
SomeProp2 = sm.SomeValue, // six value
}).ToList();
0
chandu komati10y ago
and this the code of controller model
var details = (from c in DB.cmps
join e in DB.emp on c.Emp_Id equals e.Emp_Id
where e.Emp_Id == c.Emp_Id
join d in DB.dept on c.Dept_Id equals d.Dept_Id
where d.Dept_Id == c.Dept_Id
select new {
Com_Id = c.Com_Id,
Com_Name = c.Com_Name,
Emp_Name = e.Emp_Name,
Dept_Name = d.Dept_Name,
});
return View(details.ToList());
0

chandu komati10y ago

then how to create View model

i ,mean just look like this code or not

plz help me

@model IEnumerable

ViewBag.Title = "companydetail";

}

companydetail

@Html.ActionLink("Create New", "Create")

@foreach (var item in Model) {

}

Company_Id:	Company Name:	Employee Name:	Department Name:
@item.Com_Id;	@item.Com_Name;	@item.Emp_Name;	@item.Dept_Name;