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.