1. Fill into List<SelectListItem> in Linq by normal sql query
sql = "\n select o.Odr_Code Value, o.Odr_No Text ";
sql += "\n from v_Order o left join v_Sales s on o.Odr_Code=s.Odr_Code and o.ICode=s.ICode ";
sql += "\n where o.Pcode='" + du.CSQ(Pcode) +"' ";
sql += "\n group by o.Odr_Code,o.Odr_No ";
sql += "\n having sum(isnull(o.Qty,0)-isnull(s.Qty,0))>0 ";
sql += "\n order by convert(numeric(18),substring(o.Odr_No,2,15)) ";
List<SelectListItem> list = db.Database.SqlQuery<SelectListItem>(sql).ToList();
2. Fill into List<Class> in Linq by normal sql query
public class Order_Remain
{
public string Odr_Code { get; set; }
public string Odr_No { get; set; }
public decimal Qty_Bal { get; set; }
}
sql = "\n select o.Odr_Code , o.Odr_No ";
sql += "\n ,sum(isnull(o.Qty,0)-isnull(s.Qty,0)) Qty_Bal ";
sql += "\n from v_Order o left join v_Sales s on o.Odr_Code=s.Odr_Code and o.ICode=s.ICode ";
sql += "\n where o.Pcode='" + du.CSQ(Pcode) +"' ";
sql += "\n group by o.Odr_Code,o.Odr_No ";
sql += "\n having sum(isnull(o.Qty,0)-isnull(s.Qty,0))>0 ";
sql += "\n order by convert(numeric(18),substring(o.Odr_No,2,15)) ";
var list_ = db.Database.SqlQuery<Order_Remain>(sql).ToList();
var list = (from x in list_
select (new SelectListItem() { Text = x.Odr_No, Value = x.Odr_Code })).ToList();
sql = "\n select o.Odr_Code Value, o.Odr_No Text ";
sql += "\n from v_Order o left join v_Sales s on o.Odr_Code=s.Odr_Code and o.ICode=s.ICode ";
sql += "\n where o.Pcode='" + du.CSQ(Pcode) +"' ";
sql += "\n group by o.Odr_Code,o.Odr_No ";
sql += "\n having sum(isnull(o.Qty,0)-isnull(s.Qty,0))>0 ";
sql += "\n order by convert(numeric(18),substring(o.Odr_No,2,15)) ";
List<SelectListItem> list = db.Database.SqlQuery<SelectListItem>(sql).ToList();
2. Fill into List<Class> in Linq by normal sql query
public class Order_Remain
{
public string Odr_Code { get; set; }
public string Odr_No { get; set; }
public decimal Qty_Bal { get; set; }
}
sql = "\n select o.Odr_Code , o.Odr_No ";
sql += "\n ,sum(isnull(o.Qty,0)-isnull(s.Qty,0)) Qty_Bal ";
sql += "\n from v_Order o left join v_Sales s on o.Odr_Code=s.Odr_Code and o.ICode=s.ICode ";
sql += "\n where o.Pcode='" + du.CSQ(Pcode) +"' ";
sql += "\n group by o.Odr_Code,o.Odr_No ";
sql += "\n having sum(isnull(o.Qty,0)-isnull(s.Qty,0))>0 ";
sql += "\n order by convert(numeric(18),substring(o.Odr_No,2,15)) ";
var list_ = db.Database.SqlQuery<Order_Remain>(sql).ToList();
var list = (from x in list_
select (new SelectListItem() { Text = x.Odr_No, Value = x.Odr_Code })).ToList();
No comments:
Post a Comment