anyone convert this sql query to linq

ejaz mirza

8y2.3kLINQ

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

16 Replies

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

Gaurav Raj8y ago
Ok....
ejaz mirza....
you can use this toolll.. linqpad
https://www.linqpad.net/
ejaz mirza8y ago
this is also quit good guarav if requirement in linq
Gaurav Raj8y ago
u can also convert this query into store procedure then you can call with linq...........
ejaz mirza8y ago
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 Jindal8y ago
Please provide database or class design so that i can replicate the environment at my end and help you out
ejaz mirza8y ago
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 Jindal8y ago
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 mirza8y ago
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 mirza8y ago
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 Jindal8y ago
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 mirza8y ago
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 mirza8y ago
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 Maurya8y ago
Somehow I put the wrong tool name. Please see the correct tool name. I have updated the answer.
ejaz mirza8y ago
Akash Morya sir
there is no option in converting sql to linq in linqpad
Hemant Jindal8y ago
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 Maurya8y ago
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.

anyone convert this sql query to linq

ejaz mirza....