SQL to Linq query [Inner Join]

Question

How to write SQL to Linq Query [i.e.Inner Join]

SELECT * FROM Products pp WITH (NOLOCK)

INNER JOIN (SELECT TOP 1 Shiftdate,ShiftID, Period FROM Products WITH (NOLOCK)

WHERE ProuductId =100 AND OrderId =5 ORDER BY Shiftdate DESC, ShiftID DESC,Period DESC) ss

ON pp.Shiftdate = ss.Shiftdate AND pp.ShiftID = ss.ShiftID AND pp.Period = ss.Period

WHERE ProuductId = 100 AND OrderId = 5 ORDER BY DelyId

Vijay · Answer

Hi Friends.......... I got solution for this question.... Inner Join Query var comprod = from prod in context.Products join ord in (from tp in context.Products where tp. ProuductId == 100 && tp. OrderId == 5 orderby tp.ShiftDate descending tp.ShiftId descending tp.Period descending select tp).Take(1) on new { prod.ShiftDate prod.ShiftId prod.Period } equals new { ord.ShiftDate ord .ShiftId ord.Period } orderby prod. DelyId where prod. ProuductId == 100 && prod. OrderId == 5 select prod ;

Joginder Banger · Answer

you have create lot of confusion you have two question how to used the Inner Join sql to linq and Why are used inner join. your first question : here is main category and sub cateogory join with the help of inner join Category.DataSource = from aa in db.tbl_videos join bb in db.tbl_sub_categories on aa.sub_category equals bb.main_id.ToString() select new { category=bb.sub_category_name }; Category.DataBind(); your second question : a complete solution mention here check it and a best example of sql join more information check this link download pic for better information.

Jignesh Trivedi · Answer

Hi Probable query is var k = from pp in context.Products join ss in (from d in context.Products where d.ProductId ==100 and d.orderId = 5 orderby d.Shiftdate descending select new { d.Shiftdated.ShiftID d.Period}) on new {Shiftdate=pp.Shiftdate ShiftID=pp.ShiftIDPeriod=pp.Period} equals {Shiftdate=ss.Shiftdate ShiftID=ss.ShiftIDPeriod=ss.Period} where pp.ProuductId == 100 && pp.OrderId == 5 select e; hope this will help you.

Mukesh Kumar Tiwari · Answer

good... good luck....

Vijay · Answer

I understood inner join. But i want logic for which i was post sql query....

Mukesh Kumar Tiwari · Answer

var grid_data = from state in Linq.Chk_State_Masters join cnt in Linq.Chk_Country_Masters on state.Country_Id equals cnt.Country_Id select new { State_Id = state.State_Id Country_Name = cnt.Country_Name State_Name = state.State_Name Entry_By = state.Created_by Entry_Date = state.Created_Date Updated_Name = state.Updated_by Updated_Date = state.Updated_Date };