How to create a query to filter a table from database when some passed values are null or empty ?

Question

1.00/5 (1 vote)

See more:

, +

C#

Hi C# Core Teams Developer could you please help me with a query

Table result will be loaded by ajax on Razor page

Table A from data base to filter and create result to display
+------------+-----------+-------+
| StartDate  | EndDate   | Name  |
+------------+-----------+-------+
| 05/11/2019 |05/12/2019 | Smith |
+------------+-----------+-------+
| 01/12/2019 |05/12/2019 |  Tom  |
+------------+-----------+-------+
| 04/08/2019 |05/11/2019 | Dante |
+------------+-----------+-------+

public JsonResult FilterTableA ( string dateStart, string dateEnd, string name)
{
var createFilter = await _context.TableA
.Where(r => (r.CreatedDate >= dateStart && 
             r.UpdatedDate <= dateEnd && 
             r.UserName == name)
.ToLis();

return new JsonResult(createFilter );

}



Thanks Everyone!!!!

What I have tried:

C#

these are input fields in page to create the filter:

but user can just type the name and search in database Table A and create the filter
or user can type fromDate >=
or user can type toDate >=
or user can type toDate and the UserName  and do the filter

I dont know how to include this options in the query???

+------------+-----------+
|From date   |05/11/2019 |  
+------------+-----------+
+------------+-----------+
| To Date    |05/11/2019 | 
+------------+-----------+
+------------+-----------+
| Name       |   Dante   |
+------------+-----------+

Just filter by Name:

+------------+-----------+
|From date   |           |  
+------------+-----------+
+------------+-----------+
| To Date    |           | 
+------------+-----------+
+------------+-----------+
| Name       |   Dante   |
+------------+-----------+

result of  search filter:

+------------+-----------+-------+
| StartDate  | EndDate   | Name  |
+------------+-----------+-------+
| 04/08/2019 |05/11/2019 | Dante |
+------------+-----------+-------+

How could I create the sql query to filrter Table A in database 

var createFilter = await _context.TableA

Where(r=>
 (r.CreatedDate == null 
  r.UpdatedDate == null
  r.UserName == name) 
|| 
(r.UpdatedDate <= dateEnd && r.UserName == name) ).ToLis();

Posted 5-Dec-19 13:57pm

JimB_

Updated 6-Dec-19 3:34am

v2

Add a Solution

2 solutions

Solution 1

What I would do is have a set of default dates (eg Min & Max date values), and use those if the use enters a null or invalid date value

Posted 5-Dec-19 14:46pm

MadMyche

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-12-06T03:34:00

First of all, make sure you're not storing dates as strings. Use DateTime in your .NET code, and one of the date/time types[^] in SQL - I would suggest the date type[^].

You can either pass your date parameters as strings and use the DateTime.TryParse method[^] to try to convert them to dates; or pass them as nullable DateTime values, and rely on model binding to convert them.

Apply only the filters which are defined. Since you want to return records which match all specified filters, that's as simple as using multiple Where filters:

C#

[HttpPost]
public async Task<JsonResult> FilterTableA(DateTime? dateStart = null, DateTime? dateEnd = null, string name = null)
{
    var records = _context.TableA.AsQueryable();
    if (dateStart != null) records = records.Where(r => r.CreatedDate >= dateStart);
    if (dateEnd != null) records = records.Where(r => r.UpdatedDate <= dateEnd);
    if (!string.IsNullOrEmpty(name)) records = records.Where(r => r.UserName == name);
    
    var result = await records.ToListAsync();
    return new JsonResult(result);
}