anyone convert this sql query to linq

Question

select * from OrderBookingDetails where OrderType='" + ordertypedd.SelectedItem + "' and PartyName='" + partynameddmain.SelectedItem + "'and LEN(RTRIM(ISNULL(Status,'')))=0 and (LEN(RTRIM(ISNULL(OrderConfirmedBy, ''))) > 0) and ((LEN(RTRIM(ISNULL(OrderIssuedBy, ''))) > 0)) and StockLocationName in (" + stloc + ") and OrderNo in (Select OrderNo from OrderBookingDetails where QtyBags>Isnull(DOExecutedQty,0))

Gaurav Raj · Answer

Ok.... ejaz mirza.... you can use this toolll.. linqpad https://www.linqpad.net/

ejaz mirza · Answer

this is also quit good guarav if requirement in linq

Gaurav Raj · Answer

u can also convert this query into store procedure then you can call with linq...........

ejaz mirza · Answer

this is class of model order public partial class order { public long ID { get ; set ; } public long OrderNo { get ; set ; } public System.DateTime DocDate { get ; set ; } [Required(ErrorMessage = "Customer is required." )] public string PartyName { get ; set ; } public string CustomerCode { get ; set ; } public string TruckNo { get ; set ; } [Required(ErrorMessage = "Grade is required." )] public string Grade { get ; set ; } public decimal QtyBags { get ; set ; } public Nullable decimal > QtyMts { get ; set ; } [Required(ErrorMessage = "Stocklocation is required." )] public string StockLocationName { get ; set ; } public string Status { get ; set ; } public string Billing { get ; set ; } public string OrderIssuedBy { get ; set ; } public string OrderConfirmedBy { get ; set ; } public Nullable OrderTime { get ; set ; } public string Remarks { get ; set ; } public string DepoName { get ; set ; } public Nullable OrderIssuedTime { get ; set ; } public Nullable OrderConfirmedTime { get ; set ; } [Required(ErrorMessage = "OrderType is required." )] public string OrderType { get ; set ; } public string DealerName { get ; set ; } public Nullable long > DispatchID { get ; set ; } public Nullable int > DOExecutedQty { get ; set ; } public Nullable int > DAExecutedQty { get ; set ; } public Nullable long > PreOrderDOID { get ; set ; } public Nullable int > BalQty { get ; set ; } } public ActionResult dispatchordergrid(String tradetype, String partynames) { using (var aa = new orderbookingEntities()) { var data = from s in order where (s.OrderType == tradetype) && (s.PartyName== partynames) && (s.Status.Length == 0) && (s.OrderConfirmedBy.Length > 0) && (s.OrderIssuedBy.Length > 0) && (s.OrderNo = from z in order where ((z.QtyBags > 0) )select z) select s.ToArray(); if (data == null || data.Count() == 0) { } return PartialView( "dispatchgrid" , data); } } now i just want the linq query all is working good

Hemant Jindal · Answer

Please provide database or class design so that i can replicate the environment at my end and help you out

ejaz mirza · Answer

no use sir ) or end of expression expected (change the Query Language to 'C# Statements' for multi-statement queries) Invalid expression term 'in' A query body must end with a select clause or a group clause ; expected select * from orders where PartyName='" + partynameddmain.SelectedItem + "' and OrderNo in (Select OrderNo from OrderBookingDetails where QtyBags>Isnull(DOExecutedQty,0)) just evaluate the above query to linq only i will get it remaining

Hemant Jindal · Answer

Please try out this one Orders.Where (x => x.OrderType == "Trade" && x.PartyName == "Mirza Steel" && x.Status==null && x.OrderConfirmedBy == null && x.OrderIssuedBy != null && x.QtyBags > ( (decimal?)x.DOExecutedQty??0) && x.OrderNo in ( from z in OrderBookingDetails where z.QtyBags == null || z.QtyBags > ((decimal?)x.DOExecutedQty??0)) select z.OrderNo))

ejaz mirza · Answer

Hemanth sir select * from orders where PartyName='" + partynameddmain.SelectedItem + "' and OrderNo in (Select OrderNo from OrderBookingDetails where QtyBags>Isnull(DOExecutedQty,0)) i just want this query in linq that it

ejaz mirza · Answer

Hemanth sir i have placed u can see the screenhot it showing error "could not find the query pattern of source type order not found where clause i just want query only Orders.Where (x => x.OrderType == "Trade" && x.PartyName == "Mirza Steel" && x.Status==null && x.OrderConfirmedBy == null && x.OrderIssuedBy != null && x.QtyBags > ( (decimal?)x.DOExecutedQty??0) && [below sql query i want here] ) the above query running successfull but i want add another condition that is and OrderNo in (Select OrderNo from OrderBookingDetails where QtyBags>Isnull(DOExecutedQty,0) in my above linq query

Hemant Jindal · Answer

Hi Ejaz Please replace order with OrderBookingDetails or with the order collection object you have implemented in the codebasse. Please share your code structure class diagram if possible thanks

ejaz mirza · Answer

Orders.Where (x => x.OrderType == "Trade" && x.PartyName == "Mirza Steel" && x.Status==null && x.OrderConfirmedBy == null && x.OrderIssuedBy != null && x.QtyBags > ( (decimal?)x.DOExecutedQty??0)) it is working fine but i want to extend it to and OrderNo in (Select OrderNo from OrderBookingDetails where QtyBags>Isnull(DOExecutedQty,0) instead of the last x.QtyBags > ( (decimal?)x.DOExecutedQty??0)) please solve it for me

ejaz mirza · Answer

Hemanth jindal sir from s in order where (s.OrderType == tradetype && s.PartyName== partynames && s.Status.Length == 0 && s.OrderConfirmedBy.Length > 0 && s.OrderIssuedBy.Length > 0 && s.OrderNo = (from z in order where (z.QtyBags > 0 )select z)) select s.ToUpper(); could not find the implementation for query pattern for source type order 'where' not found I have added using system.linq namespace

Aakash Maurya · Answer

Somehow I put the wrong tool name. Please see the correct tool name. I have updated the answer.

ejaz mirza · Answer

Akash Morya sir there is no option in converting sql to linq in linqpad

Hemant Jindal · Answer

Let's try this one from s in OrderBookingDetails where s. OrderType == ordertype && s.PartyName && s.Status.Length == 0 && s. OrderConfirmedBy.Length > 0 && s. OrderIssuedBy.Length > 0 && s.StockLocationName in (stloc) && s. OrderNo =( from z in OrderBookingDetails where z. QtyBags > 0 ) select z) select s.ToUpper(); you can try LinqPad as well

Aakash Maurya · Answer

Would you like to do this job by own? If yes, here is a bonus guideline for you. SQLtoLINQ is the great tool which converts your SQL queries to LINQ expression. What you have to do is? Download the tool from here http://www.sqltolinq.com/, connect your SQL database with it. Write your SQL queries And convert it to the LINQ. Using above steps you can convert your any of the SQL queries to the LINQ. You can make use of trial version.