LINQ to SQL nullable boolean without .Value caused TimeoutException

Viewed 52

I have below Customer Entity Class with (other properties went off for clarity):

    public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }
}

I make an HTTP GET request with query param to give me all not Deleted Customers (query params are wrapped in CustomerFilter object). IsDeleted Prop of that filter object is nullable.

public class CustomerFilter
{
    public string Name{ get; set; }
    public bool? IsDeleted { get; set; }
}

GET api/Customers?Name=TestCustomer&isDeleted=false

var response = await _customerService.Get(new CustomerFilter{Name=name, IsDeleted=isDeleted})

in my repository i have following code:

public async Task<List<Customers>> Get(CustomerFilter customerFilter)
    {
        var customersQuerable= _context.Customers.AsQueryable();

        if (customersFilter.Name!= null)
        {
            customersQuerable= customersQuerable.Where(_ => _.Name.Contains(customerFilter.Name.RemoveEmpty()));
        }

  
        if (customersFilter.IsDeleted!= null)
        {
            customersQuerable= customersQuerable.Where(_ => _.IsDeleted== customerFilter.IsDeleted);
        }

        return await customersQuerable.Skip(customersFilter.Offset).Take(customersFilter.Limit).ToListAsync();

   }

When IsDeleted Property is true it works fine but when it is false there is SQL Timeout Exception.

After several attempts I found out that problem is in IsDeleted nullable property. When i put .Value:

if (customersFilter.IsDeleted!= null)
    {
        customersQuerable= customersQuerable.Where(_ => _.IsDeleted== customerFilter.IsDeleted.Value);
    }

I was successfull in recieving result.

Question: Why false value caused the issue even though I never passed null as filter property for IsDeleted. The value was always true or false. If it was some LINQ to SQL translation with nullable values i guess it should also not work with true value.

true worked, false caused SQL TimeoutException and when I changed the code with .Value on nullable bool while constructing queryable in where expression it is working fine.

EDIT

I have extracted queries generated by both cases with and without .Value and both queries when executed directly in database returned result instantly. Execution plans are the same.

1 Answers

Probably SQL Server cached bad execution plan for parameter @__customerFilter_IsDeleted_1. It is called Parameter Sniffing. Removing parameter usage should help in speedup query:

if (customersFilter.IsDeleted != null)
{
    if (customerFilter.IsDeleted.Value)
        customersQuerable = customersQuerable.Where(x => x.IsDeleted);
    else
        customersQuerable = customersQuerable.Where(x => !x.IsDeleted);
}

It is easy if you have just Boolean parameters, but if there is Integer parameter, we cannot easily create workaround. For such purpose I have created extension WithRecompile, which add OPTION(RECOMPILE) to the end of the SQL query. For such queries SQL Server do not cache execution plan.

Related