dynamically search db using linq

We can create dynamic expression using Expression class as below.

var fromDate = request.fromDate == default ? DateTime.Today.AddDays(-30) : request.fromDate;
var toDate = request.toDate == default ? DateTime.Today.AddDays(30) : request.toDate;

Expression> searchExpr = item => item.dateofinterview >= fromDate && item.dateofinterview <= toDate;

if (request.location > 0)
{
    Expression> locationExpr = item => item.location == request.location;
    var tempExpr = Expression.AndAlso(searchExpr.Body, Expression.Invoke(locationExpr, searchExpr.Parameters));
    searchExpr = Expression.Lambda>(tempExpr, searchExpr.Parameters);
}

if (!string.IsNullOrEmpty(request.textboxSearchValue))
{
    Expression> textBoxExpr = item => item.firstname.Contains(request.textboxSearchValue) || item.lastname.Contains(request.textboxSearchValue);
    var tempExpr = Expression.AndAlso(searchExpr.Body, Expression.Invoke(textBoxExpr, searchExpr.Parameters));
    searchExpr = Expression.Lambda>(tempExpr, searchExpr.Parameters);
}

var result = table.Where(searchExpr).ToList();

@Tuhin

Yes, we can remove the ternary operator but, there is a requirement before and after 30 days. 

// Get the search criteria from the user
string location = ddlLocation.SelectedValue;
string searchText = txtSearch.Text;
DateTime fromDate = string.IsNullOrEmpty(txtFromDate.Text) ? DateTime.Now.AddDays(-30) : DateTime.Parse(txtFromDate.Text);
DateTime toDate = string.IsNullOrEmpty(txtToDate.Text) ? DateTime.Now.AddDays(30) : DateTime.Parse(txtToDate.Text);

// Create the LINQ query
var query = from row in myTable
            where ((string.IsNullOrEmpty(location) || row.location == location)
                   && (string.IsNullOrEmpty(searchText) || row.firstname.Contains(searchText) || row.lastname.Contains(searchText))
                   && (row.dateofinterview >= fromDate && row.dateofinterview <= toDate))
            select row;

// Execute the query and return the results
List results = query.ToList();

We can remove the ternary operator for the location condition by simply checking if it's not equal to zero, which will be true if location has a valid value. We can use the DateTime.Parse method directly on fromDate and toDate without checking if they're null or empty, as the method will throw an exception if the input is invalid. We can still keep the string.IsNullOrEmpty check to avoid unnecessary exceptions in case the input is null or empty.

The code you provided appears to be a LINQ query that retrieves data from a database table based on certain conditions.

var result = table.Where(a =>
    (searchBox == null || (a.firstname.Contains(searchBox) || a.lastname.Contains(searchBox))) &&
    (location == 0 || a.location == location) &&
    (string.IsNullOrEmpty(fromDate) || a.dateofinterview >= DateTime.Parse(fromDate)) &&
    (string.IsNullOrEmpty(toDate) || a.dateofinterview <= DateTime.Parse(toDate))
);

We can use the Contains method to check if a string contains a specified substring, which is more flexible than exact matching using the == operator. We can simplify the first condition by checking if searchBox is null, which covers both cases where it's empty or has a value.

Hello Kaushik

Please try the below query with your parameters to get the expected result.

var result = table.Where(a => (a.firstname == searchBox || a.lastname == searchBox) &&
            ((location == 0) ? true : a.location == location) &&
            (string.IsNullOrEmpty(fromDate) ? (a.dateofinterview >= DateTime.Now.AddDays(-30)) : (a.dateofinterview >= DateTime.Parse(fromDate))) &&
            (string.IsNullOrEmpty(toDate) ? a.dateofinterview <= DateTime.Now.AddDays(30) : a.dateofinterview <= DateTime.Parse(toDate)));

Thanks

Vishal

Define a variable to hold the result of the query.

var result = Enumerable.Empty();

Define the search criteria and check if the Location dropdown has a value greater than 0.

int location = //get the value from the dropdown
if(location > 0)
{
   result = db.TableName.Where(x => x.Location == location);
}

Check if the textbox has any value to search for in the Firstname or Lastname column.

string searchText = //get the search text from the textbox
if(!string.IsNullOrEmpty(searchText))
{
   result = result.Where(x => x.Firstname.Contains(searchText) || x.Lastname.Contains(searchText));
}

Get the FromDate and ToDate values and check if they are empty or not. If they are empty, set the default values to search between today's date and +/- 30 days from today.

DateTime fromDate = //get the FromDate value
DateTime toDate = //get the ToDate value
if(fromDate == DateTime.MinValue && toDate == DateTime.MinValue)
{
   DateTime today = DateTime.Today;
   fromDate = today.AddDays(-30);
   toDate = today.AddDays(30);
}
else if(fromDate == DateTime.MinValue)
{
   fromDate = toDate.AddDays(-30);
}
else if(toDate == DateTime.MinValue)
{
   toDate = fromDate.AddDays(30);
}

Filter the results based on the FromDate and ToDate values. Return the final result.

result = result.Where(x => x.Dateofinterview >= fromDate && x.Dateofinterview <= toDate);
return result.ToList();