Linq where is the offer with several conditions

Question

this method returns a general list, but it has several conditions for selection. I just write this using if-else if -else if ... so much, if I also mean is there a shorter way to do this? Thanks.

public List<ProductReqNoDate> GetRequestsQuery(string departmant, int reqStateID, string firstDate, string lastDate) { var db = new requestsDBEntities(); var listPrn = new List<ProductReqNoDate>(); if (!string.IsNullOrEmpty(departmant)) { return (from r in db.requests where r.departmant== departmant select new ProductReqNoDate { departmant= r.departmant, reqNo = r.reqNo , reqDate = r.reqDate , prdctName= stringCutter((from p in db.products where p.reqNo == r.reqNo select p.prdctName).FirstOrDefault()) }).ToList(); } if (!string.IsNullOrEmpty(firstDate) && !string.IsNullOrEmpty(lastDate)) { DateTime dtfirstDate = Convert.ToDateTime(firstDate); DateTime dtlastDate = Convert.ToDateTime(lastDate); return (from r in db.requests where r.reqDate <= dtlastDate && r.reqDate >= dtfirstDate select new ProductReqNoDate { departmant= r.departmant, reqNo = r.reqNo , reqDate = r.reqDate, prdctName= stringCutter((from p in db.products where p.reqNo == r.reqNo select p.prdctName).FirstOrDefault()) }).ToList(); } } 

Answer 1

You may have the core of your request as follows:

 var query = from r in db.requests select new ProductReqNoDate { departmant= r.departmant, reqNo = r.reqNo , reqDate = r.reqDate , prdctName= stringCutter((from p in db.products where p.reqNo == r.reqNo select p.prdctName).FirstOrDefault()) } 

Then apply if then else :

 if (!string.IsNullOrEmpty(departmant)) return query.Where(r=>r.departmant== departmant).ToList(); if (!string.IsNullOrEmpty(firstDate) && !string.IsNullOrEmpty(lastDate)) { DateTime dtfirstDate = Convert.ToDateTime(firstDate); DateTime dtlastDate = Convert.ToDateTime(lastDate); return query.Where(r=> r.reqDate <= dtlastDate && r.reqDate >= dtfirstDate) .ToList(); } 

It does not reduce if then else , but makes it more reasonable what is happening.

Answer 2

1 *

you can find a better solution, but I want this help (I do), but I use it: you can check this function

  List<ProductReqNoDate> yourList = GetRequestsQuery(string departmant, int reqStateID) if (!string.IsNullOrEmpty(firstDate) && !string.IsNullOrEmpty(lastDate)) { yourdatagrid.Itemsource = yourList.where(a=> a.reqDate <= Datetime.parse(firstDate) & a.reqDate >= Datetime.parse(lastDate)) } public List<ProductReqNoDate> GetRequestsQuery(string departmant, int reqStateID) { var db = new requestsDBEntities(); var listPrn = new List<ProductReqNoDate>(); if (!string.IsNullOrEmpty(departmant)) { return (from r in db.requests where r.departmant== departmant select new ProductReqNoDate { departmant= r.departmant, reqNo = r.reqNo , reqDate = r.reqDate , prdctName= stringCutter((from p in db.products where p.reqNo == r.reqNo select p.prdctName).FirstOrDefault()) }).ToList(); } } 

and you can apply any condition in your first list, but it is not recommended in a heavy application or a lot of information in the database.

2 *

Or you can simply make the first date and last date; for example, if the date is not set, enter the first date = 01/01/1900 and the last date Datetime.Today and always pass your date in the linq request

Answer 3

something like this, I did not compile or test it, but it should give you the key.

 public List<ProductReqNoDate> GetRequestsQuery(string departmant, int reqStateID, string firstDate, string lastDate) { using(var db = new requestsDBEntities()) { DateTime dtfirstDate =null; DateTime.TryParse(firstDate,out dtfirstDate); DateTime dtlastDate = null; DateTime.TryParse(lastDate,out dtlastDate); var result = (from r in db.requests where (r.departmant == departmant) || (r.reqDate <= dtlastDate.Value && r.reqDate >= dtfirstDate.Value) select new ProductReqNoDate { departmant = r.departmant, reqNo = r.reqNo , reqDate = r.reqDate , prdctName= stringCutter((from p in db.products where p.reqNo == r.reqNo select p.prdctName).FirstOrDefault()) }).ToList(); } } 

EDIT:

if you want to use non-sql functions in your filter then call ToList () in your table

  var result = db.requests.ToList().Where(r => { // do test for what you want // so write a function to work out when you want to filter by // name or date return true; }).Select( r => new ProductReqNoDate { departmant = r.departmant, reqNo = r.reqNo , reqDate = r.reqDate , prdctName= stringCutter(db.products.Where(p=> p.reqNo == r.reqNo).Select(p=> p.prdctName).FirstOrDefault()) }) 

the problem is that you load the entire table into memory, which in case of a penalty, if you do not change the values ​​in it often and if it is not too large.

Another approach would be to write it as a stored procedure, which is likely to make your DBA happy.