Question

Hello!

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:

var query =
from u in db.Users
where u.Name.StartsWith("demo")
select u;

which transforms into

SELECT
    u.ID,
    u.NAME,
    u.GROUP_ID as UserGroupID
FROM
    USERS u
WHERE
    u.NAME LIKE 'demo%'

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:

SELECT
    u.ID,
    u.NAME,
    u.GROUP_ID as UserGroupID
FROM
    USERS u
WHERE
    <b>(u.GROUP_ID NOT IN 
      (SELECT ID FROM USER_GROUPS 
       CONNECT BY PARENT_ID = PRIOR ID 
       START WITH ID = 2)) AND</b>
    u.NAME LIKE 'demo%'

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!

Accepted Answer

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:

You add a preprocessor injecting some method call (e.g. .WherePermissionAllowed(...)) @ any place where you need it.
And finally, you add LINQ-to-SQL (or even LINQ-to-LINQ) compiler handling this method call.

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.

Answer 2

0

Hello again,

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:

You use 'Compiled queries' instead of regular ones.
We are providing a way to define corresponding SQL queries and bind it to a compiled queries. This might be settings in app.config file or something similar.
Query execution pipeline makes a lookup into a binding dictionary and takes the prepared SQL query, if it is found; otherwise, it compiles a LINQ query as usual and indicate/log that there is no SQL substitution for it.

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.

answered Feb 04 '11 at 08:32

Dmitri Maximov
2211●1●2●11

edited Feb 04 '11 at 08:33

Hello, Dmitri!

Thanks a lot for your reply! So, if I understand you right,

1) It's currently impossible to insert user-mode filters into SQL query generator,

2) SQL substitution engine, if is exists, wouldn't handle ordinary LINQ queries.

Unfortunately, this solution wouldn't meet the requirements of my first scenario. Row-level security rules written in plain SQL should be applied selectively (based on current user role). Same LINQ query should produce different SQL for users with different privileges. Security rules should work for both compiled and ordinary LINQ queries.

(Feb 04 '11 at 11:24) yallie

Hello, I'm afraid that my explanation wasn't detailed enough. Let me explain a bit the part about compiled LINQ queries vs. ordinary ones and the importance of the first.

Here is the regular LINQ query:

session.Query.All<Customer>()...;

here is the compiled one:

session.Query.Execute("myquery", () => session.Query.All<Customer>()...);

There is no much difference in the code, but having done this identification we could find a post-processing action related to "myquery" instance, no matter whether it is a security rule based on user role, or entire SQL query substitution.

(Feb 04 '11 at 11:45) Dmitri Maximov

In case the identifier is not set, the string representation of the LINQ Expression Tree hash will be used. In both cases the compiled query identifier could be found in SQL profiler log in a form of comment.

(Feb 04 '11 at 11:57) Dmitri Maximov

So, I can assume that transparent SQL substitution is actually possible for all LINQ queries? The only difference is that ordinary LINQ queries wouldn't have meaningful names associated with them? That's not a problem at all.

But what about dynamic security rules? In my first scenario I have mentioned a predicate restricting access to USERS table. Data layer should intercept any query to that particular table to insert extra SQL conditions depending on some context (user privileges, service privileges, etc). I can't imagine how could it be done with plain SQL substitution mechanism.

(Feb 05 '11 at 12:30) yallie

Right. As for security rules applied in runtime, you'd better evaluate the approach described by Alex (in addition, check out the Dynamic LINQ library).

(Feb 05 '11 at 15:24) Dmitri Maximov

Subscription:

Related questions