I am trying to build a LINQ query that will accommodate a dynamic list of WHERE conditions, but also provide multiple non-equity join conditions between two tables. I am dealing with existing ORACLE tables which have no foreign keys defined so I am unable to define navigation properties between the tables. Here is the PL-SQL Query I am trying to build in Linq.
The WHERE clause will be added dynamically from individual predicates provided in a list. My first pass looked something like this ...
The problem with the above code is that it raises the exception "ORA-01013: user requested cancel of current operation". I believe that this is due to the number of records being returned. I have learned from other tests that when the WHERE clause is added via both the expression and method syntax to the same query it generates multiple SQL Select statements and executes each separately against the database performing the actual join in memory instead. With millions of records this is latent and impractical, I believe giving rise to the exception.
Of course when I removed the WHERE clause from the Expression syntax only one SQL statement was created, and executes as expected.
But, of course I lose the JOIN filters on the dates. The other alternative I had considered was to add multiple conditions to the JOIN. This, however, only allows me to compare for EQUALITY between two object definitions. I need to compare each property with a different comparison operator ( ==, >=, <= ) though ... so again this does not work
The final thought I had was to simply add the conditions as a couple of additional WHERE filters. The problem here is that the WHERE clause is between two table values and not a static value provided by the consumer. The query does not return a Queryable typed for both tables involved in the comparison so this will not work either.
Anybody have any ideas on how to do this? I am fresh out of direction ....