C# linq using null or empty strings in a where statment

Question

I have a stored procedure in my DB that allows me to send nulls as parameters. If the value is null then it's used in the where statement. However, I don't need to query the database a second time because I have the data in a binding list.

I have been trying to figure out a way to accomplish the same task in a Linq statement. Below is the Linq code I have now. Sometimes the jobname or jobnumber could be empty. Is there any way to include an operator to say if not empty, use it in the where clause? So far the only way I got it to work is to have a deep if/else statement. I was looking around and it looks like the only way to do this is with a lambda expression and not a Linq? But I have no idea on how to accomplish that with a binding list?

  t = new BindingList<Tracking>(
                                 OmanWorkflow.TrackingData.Where(o => o.Created >= dateFrom.Value
                                 && o.Created <= dateTo.Value
                                 && o.JobNumber.Contains(txtFilterJobNumber.Text)
                                 && o.JobName.Contains(txtFilterJobName.Text)

                                 ).ToList());

Rather than a single Where clause with && in it, use multiple Where clauses. var query = OmanWorkflow.TrackingData.Where(o => o.Created >= dateFrom.Value && o.Created <= dateTo.Value); if (!string.IsNullOrEmpty(txtFilterJobNumber.Text)) { query = query.Where(z => o.JobNumber.Contains(txtFilterJobNumber.Text)) } etc. — mjwills
– mjwills, Commented Aug 13, 2018 at 11:39

Cetin Basoz · Accepted Answer · 2018-08-13 11:51:04Z

4

It depends which flavor of Linq you are using. If it is Linq To SQL then simply:

var t = new BindingList<Tracking>(
         OmanWorkflow.TrackingData.Where(o => o.Created >= dateFrom.Value
         && o.Created <= dateTo.Value
         && (string.IsNullOrEmpty(txtFilterJobNumber.Text) ||
             o.JobNumber.Contains(txtFilterJobNumber.Text))
         && (string.IsNullOrEmpty(txtFilterJobName.Text) ||
             o.JobName.Contains(txtFilterJobName.Text))).ToList());

Linq To SQL is clever enough to check if txtFilterJobNumber\Name is empty\null and if it is then doesn't even include it in the where clause generated.

With EF however, the story is a little different and using if ... construct is perfectly fine and easy:

var td = OmanWorkflow.TrackingData.Where(o => 
            o.Created >= dateFrom.Value && o.Created <= dateTo.Value);

// this likely should be o.Created < dateTo.Value
//

if (!string.IsNullOrEmpty(txtFilterJobNumber.Text))
{
   td = td.Where(o.JobNumber.Contains(txtFilterJobNumber.Text));
}
if (!string.IsNullOrEmpty(txtFilterJobName.Text))
{
   td = td.Where(o.JobName.Contains(txtFilterJobName.Text));
}
var t = new BindingList<Tracking>(td).ToList());

answered Aug 13, 2018 at 11:51

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ivan Stoev Over a year ago

Good answer. Just to add that EF Core is also clever enough to eliminate the constant true expression. The second approach though works well for any LINQ implementation, being it IEnumerable<T> or IQueryable<T> and clever or not clever enough.

Mel Gerats · Accepted Answer · 2018-08-13 11:41:14Z

1

By combining a IsNullOrEmpty chack with the actual clause:

 t = new BindingList<Tracking>(
                             OmanWorkflow.TrackingData.Where(o => o.Created >= dateFrom.Value
                             && o.Created <= dateTo.Value
                             && (string.IsNullOrEmpty(txtFilterJobNumber.Text) || o.JobNumber.Contains(txtFilterJobNumber.Text))
                             && (string.IsNullOrEmpty(txtFilterJobName.Text) || o.JobName.Contains(txtFilterJobName.Text))

                             ).ToList());

answered Aug 13, 2018 at 11:41

Mel Gerats

2,2721 gold badge16 silver badges33 bronze badges

Comments

steliosbl · Accepted Answer · 2018-08-13 11:43:46Z

1

You can avoid having to write out the conditions by using the null propagation and null coalescing operators:

OmanWorkflow.TrackingData.Where(o => o.Created >= dateFrom.Value
                             && o.Created <= dateTo.Value
                             && (o.JobNumber?.Contains(txtFilterJobNumber.Text) ?? true)
                             && (o.JobName?.Contains(txtFilterJobName.Text) ?? true)
).ToList());

If o.JobNumber is null, then that part of the clause is effectively skipped (made true). Likewise for o.JobName.

answered Aug 13, 2018 at 11:43

steliosbl

8,9275 gold badges33 silver badges57 bronze badges

1 Comment

Ivan Stoev Over a year ago

Looks concise, but won't work if TrackingData is IQueryable<T> (?. operator is not supported in expression trees).

Collectives™ on Stack Overflow

C# linq using null or empty strings in a where statment

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related