Entity Framework Core - Append conditional where clause

c# entity-framework-core sql

Question

I am trying to add an additional where clause if a specific queryParameter is passed in through the url, but it doesn't seem to be working. Before I result to doing raw sql, I want to first figure out if I'm doing it correct (documentation seems to be minimal for this as I can't find anything)

Code minimized for brevity

public IActionResult RetrieveAll([FromQuery] string orderByDate, [FromQuery] string taskStatus)
{
    try
    {
        var taskQuery = (from t in _context.Tasks select t);
        switch(taskStatus)
        {
            case "completed":
                taskQuery.Where(t => t.IsCompleted == true);
                break;
            case "notcompleted":
                taskQuery.Where(t => t.IsCompleted == false);
                break;
        }

        var tasks = taskQuery.ToList();
        return Ok(tasks);
    }
    catch (Exception ex)
    {
        return BadRequest();
    }

}

I was thinking that simply appending the Where clause would do it. The code executes the proper code path, but It still comes back with all results.

1
1
2/20/2019 2:54:46 AM

Accepted Answer

You define the base query here:

var taskQuery = (from t in _context.Tasks select t);

Later, you call the .Where(...) extension method on the query in order to further filter the query:

 case "completed":
        taskQuery.Where(t => t.IsCompleted == true);
        break;

However, .Where(...) doesn't replace the IQueryable, it returns a new IQueryable. As you noted in the comments, you need to replace the query with the new query so that your call to .ToList() returns the expected results later.

Like this:

taskQuery = taskQuery.Where(t => t.IsCompleted == true);

That's a pretty common pattern for "building up" queries in Entity Framework, so you're definitely on the right track!

3
2/20/2019 3:07:04 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow