Custom Type Handler for List<int> To catch empty lists to avoid Full Table Scan

[InvincibleDRT]

Any tips on adding Custom Type Handler for List to catch null or empty lists and throw exception. Getting lot of timeouts because of these empty lists. I tried doing it like this

public class EmptyListTypeHandler<T> : SqlMapper.TypeHandler<List<T>> { public override void SetValue(IDbDataParameter parameter, List<T> value) { if (value?.Any() == false) { throw new ArgumentException("List cannot be empty"); } parameter.Value = value; } public override List<T> Parse(object value) => (List<T>)value; }

But keeps on getting errors for "IN" clause.

Any tips? TIA

[HugoRoss]

Assuming you have an Id column and a list of Id values you like to use as filter, you could do something like this:

list with multiple entries => WHERE Id IN (1,2,3,5,8,13) (with OPTION RECOMPILE in SQLServer)
list with 1 entry => WHERE (Id = @Id) (and setting the value of @id)
empty list => WHERE (1 = 2)