How to write a complex query to interrogate multiple lists using client object model

Question

I currently have a SQL query that needs to be rewritten in C# code that interrogates 2 different sharepoint lists.

Given that this query filters using the SQL year() function, has multiple unions and subqueries, how should I be writing this in code? CAML queries or LINQ seem excessive and slow when the query will be converted back into SQL to be run anyway (the lists in question are actually tables surfaced as lists through Access Services, so it seems stupid to convert a database query into code, in order to run a database query!)

Answer 1

I ended up doing this by importing all the data from the relevant table's lists into an in-memory SQLite database, using the client object model, and then running a modified SQL query on the SQLite tables. As there wasn't that much data this was an acceptable method.