Hi,

Would it be possible to have an easy to use method to get the SQL for a given query? (when available) This is pratical to debug the application (or to create views )

I use the following method:

public string GetSqlForQuery<T>(IQueryable<T> query)
    {
      var recordSet = ((Xtensive.Storage.Linq.Queryable<T>)query).Compiled;
      var executableProvider = CompilationContext.Current.Compile(recordSet.Provider);
      Xtensive.Storage.Providers.Sql.SqlProvider sqlProvider = executableProvider as Xtensive.Storage.Providers.Sql.SqlProvider;
      DomainHandler domainHandler = (DomainHandler)typeof(Xtensive.Storage.Providers.Sql.SqlProvider).GetProperty("DomainHandler", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).GetValue(sqlProvider, null);
      var compiled = sqlProvider.Request.GetCompiledStatement((Xtensive.Storage.Providers.Sql.DomainHandler)domainHandler);
      return compiled.ToString();
    }

The reflection part is a bit hackish : is there a better way to do this?

Bonus question : the domain provides Model (from assemblies) and Schema (database schema after Domain.Build() ) How di I link them? (Ie get the tables names for a given TypeInfo, the column name for a given property, ...)


Updated at 24.06.2010 9:35:16

Thanks for your response!

session.Services.Demand<SessionHandler>();

does not exists in DO 4.3 RC

What's the equivalent?

PS: I apologize for the poor english of my title ;)


Updated at 24.06.2010 14:41:29

Ok here is the final version :

using Xtensive.Core;

...

    public string GetSqlForQuery<T>(IQueryable<T> query, Session session)
    {
      var recordSet = ((Xtensive.Storage.Linq.Queryable<T>)query).Compiled;
      var executableProvider = Xtensive.Storage.Rse.Compilation.CompilationContext.Current.Compile(recordSet.Provider);
      Xtensive.Storage.Providers.Sql.SqlProvider sqlProvider = executableProvider as Xtensive.Storage.Providers.Sql.SqlProvider;
      var sessionHandler = session.Services.Demand<Xtensive.Storage.Providers.SessionHandler>();
      var domainHandler = sessionHandler.Handlers.DomainHandler;
      var compiled = sqlProvider.Request.GetCompiledStatement((Xtensive.Storage.Providers.Sql.DomainHandler)domainHandler);
      return compiled.ToString();
    }

Alex : thanks for your help! This will be put to good use ;)

This thread was imported from our support forum. The original discussion may contain more detailed answer.

asked Jun 23 '10 at 15:23

olorin's gravatar image

olorin
358848792


One Answer:

Alex (Xtensive) wrote:

1) Public way of getting DomainHandler (and all the other handlers) is:

var sessionHandler = session.Services.Demand<SessionHandler>();
var domainHandler = sessionHandler.Handlers.DomainHanlder; // See HandlerAccessor & HandlerFactory for further details

2) They're linked only by names - i.e. there are no direct links from Model to Schema.

Actually, Model contains its own representation of database schema (columns & indexes, where primary indexes describe whole tables). Schema is used only during upgrade - it is based on Xtensive.Modelling classes supporting hierarchical comparison.


psulek wrote:

Ok here is the final version : public string GetSqlForQuery<t>(IQueryable<t> query, Session session)

You should change this to extension method of IQueryable<t> like this:

public static string ToSqlQuery<T>(this IQueryable<T> query, Session session)

which can be later used like this:

Query.All<Person>.Where(p => p.Name == "Alex").ToSqlQuery(Session.Current)

answered Jun 24 '10 at 05:46

Editor's gravatar image

Editor
46154156157

Ups, I forgot to mention - Demand() is extension method to IServiceContainer defined in Xtensive.Core.

It relies on IServiceContainer.Get, but throws an exception instead of returning null.

(Jun 24 '10 at 05:46) Alex Yakunin Alex%20Yakunin's gravatar image

Yep, the snippet is very useful.

Btw, all the queries are logged as Debug events as well.

(Jun 24 '10 at 05:46) Alex Yakunin Alex%20Yakunin's gravatar image

olorin wrote: Yes we could, but getting the sql for a query should not be too easy ;)

To Alex: I didn't see the new chapter for the logs in manual: I will check it out.

(Jun 24 '10 at 05:46) Editor Editor'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