Is it possible to plug into SQL generation pipeline of DataObjects.NET to rewrite (partially or completely) SQL text generated from LINQ query? Consider the following two scenarios.
Scenario 1. I have an application running simple LINQ query:
which transforms into
I need to embed some extra conditions into WHERE clause of the resulting SQL while leaving source LINQ query intact. For example, it could be a predicate restricting access to USERS table:
Embedding expressions in the source LINQ query is not an option. Extra condition is supposed to be written by administrator who manages user's access roles. Admin doesn't have access to appication source code (also, he can write SQL, but not C#). Furthermore, extra condition could be too complex to be expressed with LINQ, so we have to store it as SQL text.
Scenario 2. Let's consider we have large Oracle database. Cost-based optimizer sometimes fails to generate truly optimal execution plan for some SQL queries (due to outdated statistics or some software bug, for example), so we run SQL Optimizer and rewrite those queries (by adding hints, reordering subqueries, etc) to make them execute faster. We would like to have some query cache we could manage at runtime to make DataObjects.NET replace source queries with their optimized counterparts.
Is it possible to do such things with DataObjects.NET? Are there any extension points we could hook up to get access to SQL generation? What approach should we take to investigate such possibilities?
Thanks in advance!
I'd like to add few direct answers on original questions (i.e. w/o any implications):
DO offers two official ways of extending its LINQ translation pipeline:
1) You can add your own LINQ query preprocessor - a routine that will transform the original expression tree into the one passed into LINQ translation pupeline. Such a preprocessor is used in localization sample (see "Practices" projects).
2) You can write your own compilers for LINQ expressions that can't be handled by integrated translator (e.g. calls of your custom methods). Such compilers can be of two kinds based on their result type: LINQ-to-LINQ and LINQ-to-SQL. First case is obvious: we allow you to substitite an untranslatable expression to some translatable one. The second is simple as well: we allow you to provide SQL DOM model describing the equivalent SQL.
Probably, your case can be covered by combining 1 & 2:
Also, there are some unofficial approaches. Technically we can provide you with access point (not sure if it's already available now) allowing you to transform the final SQL DOM expression in any way you like. But I fear this isn't the easiest way: finding and modifying a specific subtree in AST-like tree can be a pretty tricky problem, if the case is complex.
So being on your place, I'd try to narrow down the case to the one that can be implemented using just official APIs - their usage is pretty easy, but dealing with trees is complex. Also, I'd think about transformations with C# expressions first - you're loosing a lot of info @ SQL DOM level (e.g. mapping is already lost @ this stage, since we don't need it here), so generally you should deal with SQL DOM only on final transformation stage.
After some discussions we've agreed that there is a nice way of extending DataObjects.Net architecture to meet both your requirements. Here is the idea, in short:
As this is an enterprise feature with no doubt, it will be included into Professional and Ultimate Editions only. As for implementation time, it might take approximately a week.