If you find yourself in a situation where you have multiple parameters where you can query:
- by one param
- combination of params
- all params
And you want to execute just one DB call and avoid querying with params that are NULL, do the following:
string ID;
string firstName;
string lastName;
var query = dbContext.Table.AsQueryable();
query =
query.Where(u =>
(string.IsNullOrEmpty(userID) || u.ID == ID)
&& (string.IsNullOrEmpty(firstName) || u.FirstName == firstName)
&& (string.IsNullOrEmpty(lastName) || u.LastName == lastName))
.List();
- If String is null or empty, than the first part of the criteria will be false, thus EF will ignore the line.
- If string is neither null nor empty, then the criteria on the right will be questioned
To improve the conditions with case-insensitive search criteria you can make use of string.Equals()
:
string.Equals(a, b, StringComparison.CurrentCultureIgnoreCase);
And apply:
var ignoreCasing = StringComparison.CurrentCultureIgnoreCase;
query =
query.Where(u =>
(string.IsNullOrEmpty(userID) || string.Equals(u.ID, ID, ignoreCasing))
&& (string.IsNullOrEmpty(firstName) || string.Equals(u.FirstName, firstName, ignoreCasing))
&& (string.IsNullOrEmpty(lastName) || string.Equals(u.LastName, lastName, ignoreCasing)))
.List();