Optional filters on LINQ Query returning unexpected result

Question

I have function that searches for a list of users with their username, user address, previous address, and phone numbers. Once I have those user information, I can filter against their username, address, previous address or phone number. When I get the user result and filter for a specific phone number, the filter returns the correct phone number and other relevant information.

The problem arises when I search for a username that does not have a phone number. So when I search for a username and then filter for a specific username, the result is empty.

For example, If I search for username "John", I get 100 results matching that username along with their useraddress, previousaddress and phonenumber. Filtering for John123 works without any issues.

However, If I search for "Mary", and try to filter against a specific username, I don't get any result back. This is because all usernames with "Mary" do not have a phonenumber.

The code below is what I've tried and it isn't working as expected. Could someone please take a look and tell me where I'm going wrong?

  var finalResult = context.User
              .Select(x => new UserModel()
              {
                  UserName = x.Name,
                  UserAddress = x.Address,
                  PreviousAddress = x.PAddress,
                  PhoneNumber = x.PhoneNumbers.Select(y => y.PersonalNumber),
              }).AsQueryable();

            if (!string.IsNullOrWhiteSpace(search.UserName))

            {
                finalResult = finalResult.Where(x => EF.Functions.Like(x.UserName, $"%{search.UserName}%"));

                if (search.UserNameFilter != "" || search.PhoneNumberFilter != "")
                {
                    finalResult = finalResult.Where(x =>
                          ((EF.Functions.Like(x.PhoneNumber.FirstOrDefault(), $"%{search.PhoneNumberFilter}%")) &&
                           EF.Functions.Like(x.UserName, $"%{search.UserName}%") &&
                           EF.Functions.Like(x.UserName, $"%{search.UserNameFilter}%") &&
                           EF.Functions.Like(x.PreviousAddress, $"%{search.PreviousAddressFilter}%") &&
                           EF.Functions.Like(x.UserAddress, $"%{search.UserAddressFilter}%")));

                    return finalResult;
                }
                return finalResult;
            }

Answer 1

Split out the tests and guard each filter with an if separately:

var finalResult = context.User
              .Select(x => new UserModel() {
                  UserName = x.Name,
                  UserAddress = x.Address,
                  PreviousAddress = x.PAddress,
                  PhoneNumber = x.PhoneNumbers.Select(y => y.PersonalNumber),
              });

if (!String.IsNullOrWhiteSpace(search.UserName))
    finalResult = finalResult.Where(x => EF.Functions.Like(x.UserName, $"%{search.UserName}%"));

if (search.UserNameFilter != "")
    finalResult = finalResult.Where(x => EF.Functions.Like(x.UserName, $"%{search.UserNameFilter}%"));

if (search.PhoneNumberFilter != "")
    finalResult = finalResult.Where(x => EF.Functions.Like(x.PhoneNumber.FirstOrDefault(), $"%{search.PhoneNumberFilter}%"));

if (search.PreviousAddressFilter != "")
    finalResult = finalResult.Where(x => EF.Functions.Like(x.PreviousAddress, $"%{search.PreviousAddressFilter}%"));

if (search.UserAddressFilter != "")
    finalResult = finalResult.Where(x => EF.Functions.Like(x.UserAddress, $"%{search.UserAddressFilter}%"));

return finalResult;

PS Don't call AsQueryable on a IQueryable - always know your types.