C# Linq Using Null or Empty Strings in a Where Statment

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

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());

handling empty strings using linq

You could use this:

 public IEnumerable<Job> GetJobs(string jobNumber, string jobName, string projectDirectorName, string projectManagerName, string groupName) {
    IQueryable<Job> query = this._context.Jobs;

    if (!String.IsNullOrEmpty(jobNumber))
       query = query.Where(j => j.JobNumber.Contains(jobNumber));

    if (!String.IsNullOrEmpty(jobname))
       query = query.Where(j => j.JobName.Contains(jobName));

    // etc.

    return query;
}

If this will query the database, then that database will only get queried when you iterate over the results of this method, not for each ".Where".

LINQ syntax where string value is not null or empty

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=367077

Problem Statement

It's possible to write LINQ to SQL that gets all rows that have either null or an empty string in a given field, but it's not possible to use string.IsNullOrEmpty to do it, even though many other string methods map to LINQ to SQL.
Proposed Solution
Allow string.IsNullOrEmpty in a LINQ to SQL where clause so that these two queries have the same result:

var fieldNullOrEmpty =
from item in db.SomeTable
where item.SomeField == null || item.SomeField.Equals(string.Empty)
select item;

var fieldNullOrEmpty2 =
from item in db.SomeTable
where string.IsNullOrEmpty(item.SomeField)
select item;

Other Reading:

1. DevArt

2. Dervalp.com

3. StackOverflow Post

Object format and return empty string in LINQ query

My first question: Does LINQ allow me to return an empty string form the Email1 field if it is null, similar to SQL coalesce? (I would remove the null test from the where clause).

Yes, there is the ?? operator that works similar to the coalesce.:

new { Email1 = c.Email1 ?? "", c.ID } //String.Empty would be nicer, but i think it depends on EF version if you are allowed to use it.

For your second question, if this is the only place you are going to use them, then anonymous is pretty fine.
If you want to use this on other places, yes create an object just with two properties... That's the object's purpose after all. (or maybe a struct?)

Null value in linq where clause

Checking the property is null or empty before comparing it it's the only way I know

if (!string.IsNullOrEmpty(searchString))
      {
          Infos = Infos.Where(
              x =>
              (!String.IsNullOrEmpty(x.FirstName) && x.FirstName.ToLowerInvariant().Contains(searchString)) ||
              (!String.IsNullOrEmpty(x.LastName) && x.LastName.ToLowerInvariant().Contains(searchString)) ||
              (!String.IsNullOrEmpty(x.ContractNum) && x.ContractNum.ToLowerInvariant().Contains(searchString)) ||
              (!String.IsNullOrEmpty(x.VIN) && x.VIN.ToLowerInvariant().Contains(searchString)) ||
              (x.Claim != null && !String.IsNullOrEmpty(x.Claim.InitiatedBy) && x.Claim.InitiatedBy.ToLowerInvariant().Contains(searchString))
              ).ToList();
      }

EXTRA: I added a check on the Claim property to make sure it's not null when looking at InitiatedBy

EXTRA 2: Using the build in function IsNullOrEmpty to compare string to "" and nullso the code is clearer.

Extra 3: Used of ToLowerInvariant (https://msdn.microsoft.com/en-us/library/system.string.tolowerinvariant(v=vs.110).aspx) so the lowering action will act the same no matter of the culture.

Skip an empty sequence inside a LINQ .Where statement

This is a perfect opportunity to use the Enumerable.FirstOrDefault() method:

Returns the first element of a sequence, or a default value if no element is found.

In this case, the "default value" would be (null, null). So, use FirstOrDefault() instead of First(), and then ignore items appropriately. Something like this might work:

var results = Formats.Where(f => f.Where(o => o.Item1.IsAssignableFrom(typ)).FirstOrDefault().Item2?.Invoke(i) ?? false);

LINQ: Returning an empty string/null instead of an System.InvalidOperationException error

You could use the method SingleOrDefault. Then you could check if that you get is not or not.

var appUser = (from c in db.AppUsers
                 where c.AppUserID == AppUserId
                 select c).SingleOrDefault();

if(appUser!=null)
    var appUserId = appUser.AppUserID.ToString();

Or more compact:

var appUser = db.AppUsers.SingleOrDefault(x=>x.AppUserID==AppUserId);

if(appUser!=null)
    var appUserId = appUser.AppUserID.ToString();

The method SingleOrDefault returns the single item from a sequence for which the predicate (the expression in the where clause) is true. If there are more than one items, for which the predicate is true, an exception will be thrown. Furthermore, if there isn't any item in the sequence for which the predicate is true, you will get the default value for the projected item.

---

Related Topics