Entity Framework LINQ statement always translated into WHERE 0 = 1

Question

So I have a method with two parameters that I use as filter to get a list of results from a SQL Server database, using EF Core. Before querying the database I verify that the parameters are not null. If one is null, I query using only the other and vice versa. If both are non nulls I use both of them for my query.

Here is the code :

        // At least one parameter must be non null
        if (string.IsNullOrEmpty(param1) && string.IsNullOrEmpty(param2))
            return;

        // 1) If param1 is null, use the param2 as filter
        if (string.IsNullOrEmpty(param1))
        {
            list = _context.Foos.AsNoTracking().Where(x => x.Param2 == param2).ToList();
        }
        // 2) If param2 is null, use param1 as filter
        else if (string.IsNullOrEmpty(param2))
        {
            list = _context.Foos.AsNoTracking().Where(x => x.Param1 == param1).ToList();
        }
        // 3) Use both parameters
        else
        {
            list = _context.Foos.AsNoTracking().Where(x => x.Param2== param2 && x.Param1 == param1).ToList();
        }

For the first if statement (1) where I query with param2 as filter, the query is always translated into WHERE 0 = 1. The else if statement (2) works correctly.

Note that is I swap the order (querying with param1 as filter in the first if statement, and param2 in else if), now this is the query with param1 which gets translated into WHERE 0 = 1. So it seems to have nothing to do with the parameter in itself, but with the order in which I proceed.

Any clue on what is happening and how I can resolve this issue?

Thanks in advance

Answer 1

Where 0 = 1 indicates there is a optimization taking place in which the code has decided it can never be true hence 1=0. Without seeing the rest of the code it is not possible to say what is going one. However you can simplify this code which might shed some light.

If we setup an IQueryable we can add the filters independently. If there is a value for param1 then add param1 etc. If both values are present you get both filters. This also makes adding filters other parameters easier

Note the sql is only generated and executed when you call the .ToList() method.

// At least one parameter must be non null
if (string.IsNullOrEmpty(param1) && string.IsNullOrEmpty(param2))
    return;
    
// Setup IQueryable interface .
IQueryable<Foos> query = _context.Foos();
// param1 has value
if (!string.IsNullOrEmpty(param1))
{
    query = query.Where(x => x.param1 == param1)
}
// param2 has value
if (!string.IsNullOrEmpty(param2))
{
    query = query.Where(x => x.param2 == param2)
}
// Run the query.
list = query.AsNoTracking().ToList();