How to handle null values with where clause in LINQ?

Question

I have two entities where I Groupjoin them into a list and I am trying to search for a match for a certain value in different properties. The problem is that if one property has null value in the list, the search completly ignores the other properties and returns empty list.

I am posting the relevant code,

populating entities,

List<PatientsRegistrySearchViewModel> SearchList = new List<PatientsRegistrySearchViewModel>(); 
List<PatientsRegistrySearchViewModel> DataResponse = new List<PatientsRegistrySearchViewModel>();

Groupjoin,

SearchList = (
    from p in registryList
    join c in registryAccountsList on p.PatientFileId equals c.PatientFileId into g
    from c in g.DefaultIfEmpty()
    select new PatientsRegistrySearchViewModel {
        PatientFileId = p.PatientFileId,
        Name = p.FirstName,
        AliasName = p.AliasName,
        PatientDob = p.PatientDob.ToString(),
        PatientAge = 0,
        PatientEmail = c?.Email,
        SocialSecurityNo = p.SocialSecurityNo,
        PatientMobileNo = c?.MobileNo
    }).ToList();

and my search logic,

searchResults = (from i in SearchList where (
    i.Name.ToLower().Contains (value.ToLower()) ||
    i.PatientEmail.ToLower ().Contains (value.ToLower())
) select i).ToList();

this search logic will compare the value to Name or PatientEmail, if either prop is null in the list, the returned result is empty! Why (||) "or" operator is not skipping null?

list example,

{
patientFileId: 1111,
Name: "John",
aliasName: null,
patientDob: "1/25/85 12:00:00 AM",
patientAge: 0,
patientEmail: "[email protected]",
socialSecurityNo: "1212121SSN",
patientMobileNo: "3244990"
},
{
patientFileId: 2222,
Name: "Nicole",
aliasName: null,
patientDob: "1/1/01 12:00:00 AM",
patientAge: 0,
patientEmail: null,
socialSecurityNo: null,
patientMobileNo: null
},
{
patientFileId: 3333,
Name: "Nancy",
aliasName: null,
patientDob: "3/25/85 12:00:00 AM",
patientAge: 0,
patientEmail: "[email protected]",
socialSecurityNo: null,
patientMobileNo: "3244990"
}

There does not seem to be EF involved. Did you miss parts, or can the tag be removed? — nvoigt
– nvoigt, Commented Apr 18, 2018 at 8:10
If you are really using lists, there is nothing in common with EF Core. And if some property is null, prop.ToLower ().Contains (value.ToLower () will simply generate NRE. So why don't you present your real case? — Ivan Stoev
– Ivan Stoev, Commented Apr 18, 2018 at 8:11
@IvanStoev, I am removing the ef-core2.0 tag, you are right, its LINQ question, my apologies. — JSON
– JSON, Commented Apr 18, 2018 at 8:14
If you want good answers, you should try better formatting your LINQ queries. I did it for you this time. — Sefe
– Sefe, Commented Apr 18, 2018 at 8:16

Chrᴉz remembers Monica · Accepted Answer · 2018-04-18 08:43:30Z

2

Ok, I made a small test which made my guess sure. Code snippet:

class Program
{
    static void Main(string[] args)
    {
        var searchList = new List<TwoProps>()
        {
            new TwoProps() {Name = "sdfs1", PatientEmail="[email protected]" },
            new TwoProps() {Name = "sdfs2", PatientEmail=null },
            new TwoProps() {Name = "sdfs3", PatientEmail="[email protected]" }
        };

        var stringToSearch = "myMail".ToLower();
        var query = (from listItem in searchList
                    where listItem.Name.ToLower().Contains(stringToSearch)
                        || listItem.PatientEmail.ToLower().Contains(stringToSearch)
                    select listItem).ToList(); //NullReferenceException is thrown here @ second element
        //listItem.PatientEmail.ToLower() => null.ToLower() => NRE

        Console.WriteLine(query.Count());
    }

    private class TwoProps
    {
        public string Name { get; set; }
        public string PatientEmail { get; set; }
    }
}

You can fix this with ...|| (listItem.PatientEmail?.ToLower().Contains(stringToSearch) ?? false).

As expected, there is a NRE thrown. You are handling the exception somewhere and return a empty list or simply dont populate your list with teh result items(results are the same).

answered Apr 18, 2018 at 8:43

Chrᴉz remembers Monica

1,9201 gold badge12 silver badges25 bronze badges

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

4 Comments

JSON Over a year ago

Sorry I missed the part of your comment with regards to NRE, its not shown but yes in case of NRE i am returning empty list and my controller throws 404.

JSON Over a year ago

This is amazing, Thanks so much

Chrᴉz remembers Monica Over a year ago

I'm happy I made your day :)

JSON Over a year ago

You did indeed :)

Sefe · Accepted Answer · 2018-04-18 08:18:12Z

1

The || operator plus the String.IsNullOrWhiteSpace will INCLUDE values that are null. And then you are trying to do a ToLower() on a null value.

Try this. It will first make sure the valie of email is not null then do the contains.

searchResults = (from i in SearchList where (
    i.Name.ToLower ().Contains (value.ToLower ()) ||
    (!string.IsNullOrWhiteSpace (i.PatientEmail) &&
    i.PatientEmail.ToLower ().Contains (value.ToLower ()))
) select i).ToList ();

edited Apr 18, 2018 at 8:18

Sefe

14.1k6 gold badges45 silver badges56 bronze badges

answered Apr 18, 2018 at 8:10

Shaun

311 silver badge3 bronze badges

Comments

Jamez Fatout · Accepted Answer · 2018-04-18 08:18:27Z

1

For example:

AliasName = p.AliasName,

Replace With

AliasName = p.AliasName ?? ""

When p.AliasName is null, then AliasName is an empty string.

answered Apr 18, 2018 at 8:18

Jamez Fatout

3924 silver badges15 bronze badges

4 Comments

JSON Over a year ago

the query does not look in AliasName prop

Jamez Fatout Over a year ago

What do you mean

JSON Over a year ago

please look at searchResults query, AliasName prop is not included in the query context

Jamez Fatout Over a year ago

I dont talk about the variable searchResults. The select new is your problem. You map the p.AliasName to the variable AliasName(PatientsRegistrySearchViewModel). In the Map you dont make a null check. Make this ?? to get by null, default a empty string.

Collectives™ on Stack Overflow

How to handle null values with where clause in LINQ?

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related