linq function to handle : both fields equal or both null or empty

Question

Hi I have a linq query where I compare an object with all other entity stored in my database. Firstname and lastname are mandatory fields so I don't have to check for null. But in the case of street I have to do these checks.

I want to match if both fields are null or empty strings or that they are the same. The below linq query is working fine.

But I was wandering isn't there any way to make it more readable. For example with a custom function like Bool FieldsAreEqualOrBothNullOrEmpty(r.street, request.street)

Can't figure how to do that and if it's possible.

var same = 
   from r in db.Requests
   where r.firstName.ToLower() == request.firstName.ToLower()
      && r.lastName.ToLower() == request.lastName.ToLower()

      //Seems long to just compare two fields
      && ( string.IsNullOrEmpty(r.street) && string.IsNullOrEmpty(request.street) ) 
         || r.street.ToLower() == request.street.ToLower()

      select r;
return same;

Marc Gravell · Accepted Answer · 2012-10-24T08:34:20.740

I would simplify:

var same = 
   from r in db.Requests
   where r.firstName.ToLower() == request.firstName.ToLower()
      && r.lastName.ToLower() == request.lastName.ToLower()
   select r;

if(string.IsNullOrEmpty(request.street)) {
    same = same.Where(r => string.IsNullOrEmpty(r.street));
} else {
    string street = request.street.ToLower();
    same = same.Where(r => r.street.ToLower() == street);
}

The nice thing about the this is that it keeps the query simple in each case. You could use similar logic for the first two if you choose, and it could also be moved to an expression-based utility method. Untested:

public static IQueryable<T> FieldsAreEqualOrBothNullOrEmpty<T>(
    this IQueryable<T> source,
    Expression<Func<T, string>> member, string value)
{
    Expression body;
    if (string.IsNullOrEmpty(value))
    {
        body = Expression.Call(
            typeof(string), "IsNullOrEmpty", null, member.Body);
    }
    else
    {
        body = Expression.Equal(
            Expression.Call(member.Body, "ToLower", null),
            Expression.Constant(value.ToLower(), typeof(string)));
    }

    return source.Where(Expression.Lambda<Func<T,bool>>(
        body, member.Parameters));
}

then:

var same = db.Requests
           .FieldsAreEqualOrBothNullOrEmpty(x => x.firstName, request.firstName)
           .FieldsAreEqualOrBothNullOrEmpty(x => x.lastName, request.lastName)
           .FieldsAreEqualOrBothNullOrEmpty(x => x.street, request.street);

Thanks a lot ! The expression based method was a first shot and it works perfectly ! — Arno 2501, Oct 24 '12 at 09:13
I asked a question about this at http://stackoverflow.com/questions/33315584/linq-express-to-handle-null because I can't get it to work when there is a null in the database. — Kirsten, Oct 24 '15 at 07:18

score 1 · Answer 2 · edited May 23 '17 at 12:03

1

You can just call String.Equals but ignore case, using an appropriate StringComparison.

string.equals(r.street, request.street, StringComparison.OrdinalIgnoreCase);

It's bad practice to convert strings to lower-case for comparison purposes, as it won't reliably work in all languages, and is slower than using a comparison which ignores case. See, e.g., this answer

edited May 23 '17 at 12:03

Community

1
1

answered Oct 24 '12 at 08:24

RB.

36,301
12
91
131

1

depends a bit on whether the handler (EF in this case) recognises that and can process it correctly; that varies per handler and per scenario. – Marc Gravell Oct 24 '12 at 08:25
1

This will not match the case where `r.street = null` && `request.street = ""`. For that you need something like: `(r.street ?? "").Equals(request.street ?? "", StringComparison.OrdinalIgnoreCase);` – Paolo Moretti Oct 24 '12 at 08:30
I tried this which seems the simplest way but my sql server database doesn't seem to support that because of the collation and it yields an exception. – Arno 2501 Oct 24 '12 at 08:59
@PaoloMoretti You're right, sorry. I'd misread the question, and hadn't realised he wanted to treat null and empty as the same. – RB. Oct 24 '12 at 09:23

score 1 · Answer 3 · answered Oct 24 '12 at 08:29

How about creating extension method on string IsEqualOrBothNullOrEmpty -

public static class Extensions
{
   public static bool IsEqualOrBothNullOrEmpty(this string firstValue,
                           string secondValue)
   {
      return string.IsNullOrEmpty(firstValue) &&
               string.IsNullOrEmpty(secondValue)
                  || firstValue.ToLower() == secondValue.ToLower();
    }
}

And use in your query like this -

var same = 
   from r in db.Requests
   where r.firstName.ToLower() == request.firstName.ToLower()
      && r.lastName.ToLower() == request.lastName.ToLower()
      && r.street.IsEqualOrBothNullOrEmpty(request.Street)

      select r;
return same;

That will only work for LINQ-to-Objects. The question states (tags) Entity Framework. For that, you need to do expression re-writing. — Marc Gravell, Oct 24 '12 at 08:37

linq function to handle : both fields equal or both null or empty

3 Answers3

Linked