In to Linq query, we can use dynamic query:
public List<Insplan> GetInsplanLookupList(string planName = null, string binNo = null, string procCtrl = null)
{
var query = (from i in _db.insplan
orderby i.cinsplanname ascending
select i).AsQueryable();
if (!string.IsNullOrWhiteSpace(planName))
query = query.Where(x => x.cinsplanname.StartsWith(planName.Trim()));
if (!string.IsNullOrWhiteSpace(binNo))
query = query.Where(x => x.cbinno.StartsWith(binNo.Trim()));
if (!string.IsNullOrWhiteSpace(procCtrl))
query = query.Where(x => x.cprocctrlno.StartsWith(procCtrl.Trim()));
return (from q in query
join ic in _db.inscomp on q.inscompid_FK equals ic.inscompid_PK into ic_joined
from ic in ic_joined.DefaultIfEmpty()
join cl in _db.clinic on q.clinicid_FK equals cl.clinicid_PK into cl_joined
from cl in cl_joined.DefaultIfEmpty()
join b in _db.insbiller on q.insbillerid_FK equals b.insbillerid_PK into gj
from sub in gj.DefaultIfEmpty()
select new Insplan
{
insplanid_PK = q.insplanid_PK,
cinsplanname = q.cinsplanname,
cbinno = q.cbinno,
cpharmacyno = q.cpharmacyno,
cprocctrlno = q.cprocctrlno,
ncpdpversion_FK = q.ncpdpversion_FK,
cinscompname = ic.cinscompname,
cinsbillername = sub.cinsbillername,
Inscomp = (ic != null ? new Inscomp { cinscompname = ic.cinscompname, inscompid_PK = ic.inscompid_PK } : null),
Clinic = (cl != null ? new Clinic { clinicid_PK = cl.clinicid_PK, cclinicname = cl.cclinicname } : null)
}).Take(20).ToList();
}
Comments
Post a Comment