how to write query in MVC controller joining 3 tables?

Question

HiCan 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.

Jaganathan Bantheswaran · 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();

chandu komati · Answer

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());

chandu komati · Answer

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) Company_Id: Company Name: Employee Name: Department Name: @foreach (var item in Model) { @item.Com_Id; @item.Com_Name; @item.Emp_Name; @item.Dept_Name; }