>>I think it's time to convert the above logic into SP. Part of the reason I didn't want to do so is that I construct query dynamically depending on the type. I would need to reproduce the same logic in SP then. But the above code looks too monstrous to me.
>
>Maybe just retrieve the SQL data using the simple startdate-endate query then use linq to objects to filter the results on the remaining criteria.
>If the number of records returned from the simple query is excessive then perhaps a CLR proc in SQLServer ?

Well, the number of rows should not be too big, in fact, it should be 0 as that code is used for checking for conflicts with the existing data. If SQL Server is smart enough to start with the date/time predicates, then the extra criteria even though they are looking very complex may not be really a problem. I need to play with it a little bit more today to see the final SQL and how long does it take.

My second challenge in this problem is to invoke stored procedure with TVP parameters. I have the idea of this SP in my head (haven't yet wrote it), but the problem is of passing the parameters to the SP. In documentation I saw that we should pass that TVP as DataTable.

We're using classes representing our tables (EF Code First) and AutoMapper. I suppose AutoMapper can map class back to DataTable? Need to find some samples of that.

Actually, may be you're right with your idea of first getting data back and then filtering the objects. Let me try that instead - I like it.