C# Query when any of the condition is not null
C# Query when any of the condition is not null
I want to find the data in database when any one of the conditions meet.
Pasted my code so that it will be more clear
[HttpGet]
[Route("")]
public IEnumerable<User> GetUsers(string FirstName = null, string LastName = null, int Year = 0, int Month = 0)
This code is doing a simple search in database
where year == createdAt.year &&
month == createdAt.month &&
LastName == abc &&
FirstName == abc
However, if one of the condition is 0/null, then the database will return nothing since there is no month/year == 0 or firstname/lastname == null; What I want is, if year/month/lastname/firstname is 0/null, then just ignore it and check other condition.
Any idea?
3 Answers
3
// first style
users = _context.Users.Where(u =>
(Year != 0 ? u.CreatedAt.Year == Year : true) &&
(Month != 0 ? u.CreatedAt.Month == Month : true) &&
(FirstName != null ? u.FirstName == FirstName : true) &&
(LastName != null ? u.LastName == LastName : true));
// second style
users = _context.Users.Where(u =>
(Year == 0 || u.CreatedAt.Year == Year) &&
(Month == 0 || u.CreatedAt.Month == Month) &&
(FirstName == null || u.FirstName == FirstName) &&
(LastName == null || u.LastName == LastName));
I think you should check each condition separately like this.
For example when Year != 0 and every other para is not set, your original code will return nothing.
You can add your logic to the LINQ query to check conditions.
users = _context.Users.Where(x => x.Id !=0
&& x.FirstName != null
&& x.FirstName != null
&& x.Year != 0
&& x.Month != 0)
.ToList();
This will search all the data that is not null right? I want to make it search the database with the parameters I given. I have added the whole method above in order to better understanding.
– William Shu
Sep 14 '18 at 1:43
So this code is doing a simple search in database where year == createdAt.year && month == createdAt.month. However, if one of the condition is 0, then the database will return nothing since there is no month/year == 0; What I want is, if year/month is 0, then just ignore it and check other condition.
– William Shu
Sep 14 '18 at 1:47
Try this users = _context.Users.Where(x =>
&& (x.FirstName != null || x.FirstName == FirstName)
&& (x.Year == 0 || x.Year == Year)
&& (x.Month == 0 || x.Month == Month)
.ToList();
users = _context.Users.Where(x =>
&& (x.FirstName != null || x.FirstName == FirstName)
&& (x.Year == 0 || x.Year == Year)
&& (x.Month == 0 || x.Month == Month)
.ToList();
This is a really good hint, thanks. However, it need some modification in order to make it works. In stead of x.Year!=0, we should check Month == 0 || x.Month == Month, so that whenever the month is 0, it will be true and not check the second one, right?
– William Shu
Sep 14 '18 at 2:02
Yep, I had the same situation 2 weeks ago doing a filter. Tell us if it works :D
– Victor Emidio
Sep 14 '18 at 2:05
I tried, in your solution, you checked (x.year/month/firstname != null/0), but you didnt check the parameter year/month/firstname == 0/null, then year/month/firstname can still be 0 and be passed in. Then it still doesnt work. ivooQ's answer works. See his second style.
– William Shu
Sep 14 '18 at 2:10
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
The second style is the correct answer. The first one will give some warning about simplifying the ternary expression. Thanks!
– William Shu
Sep 14 '18 at 2:03