Tuesday, July 9, 2019

Linq SqlQuery fill SelectListItem by sql normal query in linq

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

No comments:

Post a Comment

Linq Expression syntax for where condtion in linq

(Expression<Func<T, bool>> filter)