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

    u.GROUP_ID as UserGroupID
    USERS u
    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:

    u.GROUP_ID as UserGroupID
    USERS u
    <b>(u.GROUP_ID NOT IN 
       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!

asked Feb 03 '11 at 07:28

yallie's gravatar image


edited Feb 03 '11 at 09:56

Sergey's gravatar image


Hello yallie, welcome to the DataObjects.Net community.

Currently, we are discussing the scenarios you've posted and will be ready with answers later today or even tomorrow, as the case is not elementary.

(Feb 03 '11 at 08:31) Dmitri Maximov Dmitri%20Maximov's gravatar image

2 Answers:

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.

answered Feb 04 '11 at 12:57

Alex%20Yakunin's gravatar image

Alex Yakunin

edited Feb 04 '11 at 12:59

Thanks for explanation!

Usually, we don't need anything more complex than inserting one or more extra conditions into WHERE clauses. But,

1) Conditions are often modified after deployment and need to be applied on-the-fly, without restarting application server.

2) Predicates are managed by client's own personnel (who has no access to the source code and has no C#/VB knowledge).

3) Conditions may use objects which are not present in the model (views, stored procs, etc).

Our current database access layer allows such SQL injection, so I'm trying to figure out whether it's possible with DO.

(Feb 05 '11 at 12:31) yallie yallie's gravatar image

Anyway, I'll try to play with APIs you've suggested. Seems like all I need is indeed achievable with 1&2 combination. Thanks again!

(Feb 05 '11 at 12:42) yallie yallie's gravatar image

I'd recommend you to take a look at Dynamic LINQ library, it provides a way to write/complement regular LINQ queries in runtime. It might be useful in your case.

(Feb 05 '11 at 15:10) Dmitri Maximov Dmitri%20Maximov's gravatar image

Yes, DLinq library is quite handy. I'm curious why didn't they make it part of .NET Framework and distribute as separate code sample.

(Feb 06 '11 at 18:36) yallie yallie's gravatar image

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:

  1. You use 'Compiled queries' instead of regular ones.
  2. 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.
  3. 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%20Maximov's gravatar image

Dmitri Maximov

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 yallie's gravatar image

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:


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 Dmitri%20Maximov's gravatar image

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 Dmitri%20Maximov's gravatar image

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 yallie's gravatar image

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 Dmitri%20Maximov's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA