I want to write LINQ where some condition may be null or not

Question

I want to retrieve some data from database based on some condition, for this I'm using LINQ. But problem is that I don't know how to write LINQ when some condition may be null.

from x in _db.AirWorkOrder
join c in _db.Clients on x.ClientId equals c.Id
where x.CreatedOn >= model.StartDate && x.CreatedOn <= model.EndDate && x.ClientId == model.ClientId && x.Type == model.Type && x.WorkOrderStatus == model.Status 
select new DateWisedReportItemModel

I want if the clientID, type, status have null value then it will take all the values saved in the DB. and if there are some values provided in it, then it will work according to the condition.

https://stackoverflow.com/a/57805828/34092 will get you started. In particular switching from a single `Where` with &&` to multiple `Where` clauses. — mjwills, Sep 06 '19 at 05:43

Caius Jard · Accepted Answer · 2019-09-06T11:05:19.183

I presume you're asking how to wildcard things that are null. You need to make the particular clause true if your model value is null. You could do this by saying things like:

(dbcolumn == modelvalue  || modelvalue == null) && ...

Or like

(dbcolumn ==  modelvalue == null ? dbvalue : modelvalue) && ...

Or like

(dbcolumn ==  modelvalue ?? dbvalue)

For example:

from x in _db.AirWorkOrder
join c in _db.Clients on x.ClientId equals c.Id
where
   (x.CreatedOn >= model.StartDate ?? x.CreatedOn) && 
   (x.CreatedOn <= model.EndDate == null ? x.CreatedOn : model.EndDate) && 
  (x.ClientId == model.ClientId || model.ClientId == null) &&     
  ...

I prefer the first as it is the most easy to understand. Also note carefully that it is the only one that will return the row if the row value in the db is null, because to a database null is never equal to null

I want if there is no condition given in ClientId, type and status then only date(this is mandatory) filter will apply, and if there is condition in these only then these(clientId, type and status) conditions will work

It might be most simple, in terms of code readability to do like:

var baseQuery = from x in _db.AirWorkOrder
join c in _db.Clients on x.ClientId equals c.Id

IEnumerable<...> result;

if(model.ClientId == null && model.Type == null && model.Status == null){
  //search on date only
  result = baseQuery.Where(x.CreatedOn >= model.StartDate && x.CreatedOn <= model.EndDate);

} else {
  //search on no null client/type/status only
  result = baseQuery.Where(x => 
    (x.ClientId == model.ClientId || model.ClientId == null) &&
    (x.Type == model.Type || model.Type == null) &&
    (x.WorkOrderStatus == model.Status || model.Status == null);
}

Thanks for the reply.. But this is not i want, I want if there is no condition given in ClientId, type and status then only date(this is mandatory) filter will apply, and if there is condition in these only then these(clientId, type and status) conditions will work... — Naruto, Sep 06 '19 at 08:14

I want to write LINQ where some condition may be null or not

1 Answers1