Linq to SQL - ignore search parameters that are null or null

Question

I have a search form in which the user can enter one or more parameters (data, status, type, identifier, summary, description) and leave the rest blank.

Here is my Linq to SQL code for my main search. Is there a way to check each parameter in Linq for a null, empty or empty string?

List<RequestStatusModel> objRequestStatus = new List<RequestStatusModel>(); var query = from r in SimCareDB.Requests where r.CustomerID == 31 select (new RequestStatusModel { RequestID = r.RequestID, RequestTitle = r.RequestTitle, DateAdded = r.DateAdded.ToString(), DateChanged = r.DateChanged.ToString(), RequestStatusID = r.StatusID }); 

Thanks!

Answer 1

If this is not necessary in your linq expression, you can simply do this using the classic if statements.

 List<RequestStatusModel> objRequestStatus = new List<RequestStatusModel>(); var query = from r in SimCareDB.Requests where r.CustomerID == 31 select (new RequestStatusModel { //... }); if(data != null) //Replace with additional checks, if neccessary { query = query.where(x=> ...); } if(status != null) { query = query.where(x => ...) } 

Answer 2

If you only want to filter, if certain criteria are passed, you should do something like this

 var objRequestStatus = new List<RequestStatusModel>(); var query = from r in SimCareDB.Requests where r.CustomerID == 31 if (String.IsNullOrEmpty(r.RequestID)) objRequestStatus = objRequestStatus.Where(x => x.RequestID == r.RequestID); if (String.IsNullOrEmpty(r.RequestTitle)) objRequestStatus = objRequestStatus.Where(x => x.RequestTitle == r.RequestTitle); //you other filters here 

This sets up an expression for what you want based on what requests are passed

Answer 3

If you want to avoid all these if s, you can do

 List<RequestStatusModel> objRequestStatus = new List<RequestStatusModel>(); var query = from r in SimCareDB.Requests where (r.CustomerID == 31) && (!String.IsNullOrEmpty(id) ? r.RequestID == id : true) && (!String.IsNullOrEmpty(status) ? r.StatusID == status : true) /* And so on */ select (new RequestStatusModel { RequestID = r.RequestID, RequestTitle = r.RequestTitle, DateAdded = r.DateAdded.ToString(), DateChanged = r.DateChanged.ToString(), RequestStatusID = r.StatusID });