How do I select rows from database elegantly with multiple conditions (which could be null)?

Question

I would like to select data from database according to what the user has entered. However, if that condition is not provided (null), it will ignore that condition and only take the provided conditions to filter out the relevant rows.

I find my solution (pseudocode) to be very inefficient and ugly and I hope someone can share his/her knowledge with me.

This is what I have tried:

'''
//selectConditions - input by user

var dataList = from data in entities.StudentsData
                    orderby data.Id
                    select data;

if (selectCondition.Age != null)
{
    dataList = filter(dataList, selectCondition.Age);
}

if (selectCondition.Gender != null)
{
   dataList = filter(dataList, selectCondition.Gender);
}

//may contain more conditions

//dataList now contains all rows with conditions specified by user
'''

So If I have a table

Name Age Gender

Tom 12 Male

Mary 13 Female

May 15 Female

Jack 14 Male

---

Case 1: Conditions are

Gender: Male
Age: null

I should get

Tom 12 Male

Jack 14 Male

---

Case 2: Conditions are

Gender: Female

Age: 15

I should get

May 15 Female

Answer 1

You can use the power of ||

entitieslist.Where(stud => 
    (!selectCondition.Age.HasValue || selectCondition.Age == stud.Age)
    && (!selectCondition.Gender.HasValue || selectCondition.Gender== stud.Gender)
    .ToList()

Answer 2

I don't think you can apply all filters in a single query. You can check for values that are not null and add appropriate where clauses.

A sample given below:

var datalist = entities.StudentsData.OrderBy(stud => stud.id);

//filterCondition contains the filter values

if(filterCondition.Age.HasValue) 
{
  datalist = datalist.Where(stud => stud.Age == filterCondition.Age);
}
if(filterCondition.Gender.HasValue)
{
  datalist = datalist.Where(stud => 
  stud.Gender.Equals(filterCondition.Gender))
}

//More filters can be added as per your requirement. 
datalist.ToList();

I hope this will help you.

Answer 3

You can return true if the condition was null

var dataList = from data in entities.StudentsData
               where 
               (selectCondition.Age == null ? true :data.Age ==selectCondition.Age )&&
               (selectCondition.Gender == null ? true :data.Gender ==selectCondition.Gender )
               //...(check if is null ? return true: your conidtion)
                orderby data.Id
                select data;

Answer 4

I have done this several ways One similar, with an IQueryable List, like this

 public List<Product1> GetProducts(long? cId, string productCode)
        {

            var age = false; //Added in a hurry
            var gender = "M"; //Added in a hurry
            var products =
                _unitOfWork.StagingProductRepository.GetMany(
                    x => x.CID == cId && x.ProductCode == productCode );
            if(age)
            {
                products = products.Where(x => x.ProductAge == age);
            }
            if (gender != string.Empty)
            {
                products = products.Where(x => x.Gender.Trim() == gender.Trim());
            }
            return products.ToList();



        }

Also done the same idea but with Raw sql, appending the And clauses

Lastly, Q for you. Is your filter method enumerating the data?