How to Implement search method for db records with multiple filters using lambda expressions

Question

I'm trying to implement a Search method with multiple filters on db records.

The below code works fine but when ever one of the filters is null a:

NullReferenceException

...occurs

var user = model.Username != null ? await UserManager.FindByNameAsync(model.Username) : null;
res = DB.UserWatchActivityLogs.Where(x => x.User_Id == (user != null ? user.Id : x.User_Id) &&
x.Tutorial.Title.Contains(model.TutorialName ?? "") &&
x.DateTime >= (model.DateTimeFrom != null ? DateTime.Parse(model.DateTimeFrom, new CultureInfo("fa-IR")) : x.DateTime) &&
x.DateTime <= (model.DateTimeTo != null ? DateTime.Parse(model.DateTimeTo, new CultureInfo("fa-IR")) : x.DateTime))
        .Include(x => x.User).Include(x => x.Tutorial).ToList();

Is user.Id or x.User_Id equal to null? – jdweng Sep 05 '19 at 09:26 — jdweng, Sep 05 '19 at 09:26

haldo · Answer 1 · 2019-09-05T18:15:39.243

Linq deferrs the execution of the query until the result is materialised. This happens when the collection is iterated over or ToList() is called on the result.

You can chain together linq queries until you want to materialise the result set. Check whether the filter is null before applying it to your query.

See below for an idea, hopefully you'll be able to adapt it to your needs:

var user = model.Username != null ? await UserManager.FindByNameAsync(model.Username) : null;

var res = DB.UserWatchActivityLogs.Include(x => x.User).Include(x => x.Tutorial);

// filter by Id if user is not null
if (user != null)
{
    res = res.Where(x => x.User_Id == user.Id);
}

// filter by tutorial name if it is not null
if (!string.IsNullOrEmpty(model.TutorialName))
{
    res = res.Where(x => x.Tutorial.Title.Contains(model.TutorialName);
}

// filter by date from (assuming model.DateTimeFrom is a string)
if (DateTime.TryParse(model.DateTimeFrom, new CultureInfo("fa-IR"), DateTimeStyles.None, out DateTime dateFrom))
{
    res = res.Where(x.DateTime >= dateFrom);
}

if (DateTime.TryParse(model.DateTimeTo, new CultureInfo("fa-IR"), DateTimeStyles.None, out DateTime dateTo))
{
    res = res.Where(x.DateTime <= dateTo);
}

// materialise the results in a list, this is when the query is executed
var results = res.ToList();

Here I've assumed model.DateTimeTo and model.DateTimeFrom are string so you could try to parse it to DateTime before filtering.

How to Implement search method for db records with multiple filters using lambda expressions

1 Answers1