Is there any way that you can see the query that will be generated when using predicate expressions to add where clauses?

I get confused when nesting them together to get the exact query that I need, so I often figure it out in MS SQL first. However, if I could see the result of my predicate expressions, in SQL syntax, I could make better decisions about how they are working.

Thanks!

Leo

leoduran wrote:

Is there any way that you can see the query that will be generated when using predicate expressions to add where clauses?

I get confused when nesting them together to get the exact query that I need, so I often figure it out in MS SQL first. However, if I could see the result of my predicate expressions, in SQL syntax, I could make better decisions about how they are working.

Thanks!

Leo

You can see teh SQL statements by using the SQL profiler. It is included with SQL Server

bert

leoduran wrote:

Is there any way that you can see the query that will be generated when using predicate expressions to add where clauses?

I get confused when nesting them together to get the exact query that I need, so I often figure it out in MS SQL first. However, if I could see the result of my predicate expressions, in SQL syntax, I could make better decisions about how they are working.

If you use adapter, you can also create a DataAccessAdapter subclass, and override for example OnSaveEntity(), which is called prior to the save action, and gets the query passed in. (there are other methods for other actions).

I was looking for this the other day.

Wouldn't it make sense for the .ToString() methods of these objects to return a representation like this (in some database nuetral form)?

arschr wrote:

I was looking for this the other day.

Wouldn't it make sense for the .ToString() methods of these objects to return a representation like this (in some database nuetral form)?

In Adapter you can call ToString() and you get the query (call ToString() of hte passed in IRetrievalQuery or IActionQuery object).

In selfservicing this is very hard to do, as the query is created by a call in the DAO object and executed by another call in the DAO object, so there is no way the query object gets returned back to the entity object where the action originates... I've puzzled with a kind of tracing mechanism, but this is already available in the various databases anyway (and the output would be comparable to these systems)...

In Adapter you can call ToString() and you get the query (call ToString() of hte passed in IRetrievalQuery or IActionQuery object).

I was thinking of sooner, getting it out of the the predicate expressions and buckets as an aid in debugging and maybe somehow using it to describe to a user the logic of what they may be dynamically building.

arschr wrote:

In Adapter you can call ToString() and you get the query (call ToString() of hte passed in IRetrievalQuery or IActionQuery object).

I was thinking of sooner, getting it out of the the predicate expressions and buckets as an aid in debugging and maybe somehow using it to describe to a user the logic of what they may be dynamically building.

Ok, well, that's possible. You have to create a subclass of the DataAccessAdapter class and add a method which calls the base class' Insertpersistenceinfo(relations) and Insertpersistenceinfo(predicateexpression) methods.

Then, create in that method a DatabaseSpecificCreator object, for example for SqlServer that's the SqlServerSpecificCreator. This object is required for creating parameters.

Then do: myBucket.Relations.DatabaseSpecificCreator = myDatabaseSpecificCreator; myBucket.PredicateExpression.DatabaseSpecificCreator = myDatabaseSpecificCreator;

THen, you can call myBucket.Relations.ToQueryText(ref uniqueMarker) and myBucket.PredicateExpression.ToQueryText(ref uniqueMarker)

uniqueMarker is an int parameter which is used to create unique parameters.

The two ToQueryText() methods produce the sql you're looking for. It seems more than it looks, it's about 5 lines of code. You can wrap this in 1 method which accepts a RelationPredicateBucket and does the work for you, place that method in the DataAccessAdapter derived class.

SelfServicing is a little easier, you just have to create a databasespecificcreator and call the toQueryText methods.

Hi Frans,

This is also something I would like, so I set about trying your suggestion. Its almost there (sSQL works), but the myBucket.PredicateExpression.ToQueryText fails in my code below (hence no sSQL2). For the sake of just testing this I added this code straight into the gen'd dataAccessadaptor, obviously I wont do that usually!!

The error:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

The code:

        public void GetQuery(IRelationPredicateBucket myBucket)
        {

            InsertPersistenceInfoObjects(myBucket.Relations);
            
            SqlServerSpecificCreator myCreator = new SqlServerSpecificCreator();
            myBucket.Relations.DatabaseSpecificCreator = myCreator;
            myBucket.PredicateExpression.DatabaseSpecificCreator = myCreator;

            int i = 0;
            int i2 = 0;
            string sSQL = myBucket.Relations.ToQueryText(ref i);
            string sSQL2 = myBucket.PredicateExpression.ToQueryText(ref i2);

        }

The stack trace:

[NullReferenceException: Object reference not set to an instance of an object.]
   SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateParameter(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ParameterDirection direction)
   SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareValuePredicate.ToQueryText(Int32& uniqueMarker)
   SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker)
   Entropy.Envoy.GeneratedSQLServer.DataAccessAdapter.GetQuery(IRelationPredicateBucket myBucket) in d:\development\envoy enterprise\entropysystemsolution\entropy.envoy.generatedsqlserver\databasespecific\dataaccessadapter.cs:93
   Entropy.Envoy.BLL.SystemModules.ListSystemModules(Int32 profileID, String areaCode, String langCode) in d:\development\envoy enterprise\entropysystemsolution\entropy.envoy.bll\systemmodules.cs:51
   Entropy.Envoy.Web.Nav_Side1.Page_Load(Object sender, EventArgs e) in d:\development\envoy enterprise\entropysystemsolution\entropy.envoy.web\nav_side.aspx.cs:46
   System.Web.UI.Control.OnLoad(EventArgs e)
   System.Web.UI.Control.LoadRecursive()
   System.Web.UI.Page.ProcessRequestMain()

P.S. Can I also get hold of the "SELECT...FROM.." part so I have the full query before it executes?

You have to add the persistence info before calling ToQueryText.

Do that by calling InsertPersistenceInfoObjects(myBucket.Relations); and after that InsertPersistenceInfoObjects(myBucket.PredicateExpression);

Thanks Frans, I actually had that done for the relations but not the predicate expressions - my stupid error!

Code for anyone interested:

        public void GetQuery(IRelationPredicateBucket myBucket)
        {

            InsertPersistenceInfoObjects(myBucket.Relations);
            InsertPersistenceInfoObjects(myBucket.PredicateExpression);
            
            SqlServerSpecificCreator myCreator = new SqlServerSpecificCreator();
            myBucket.Relations.DatabaseSpecificCreator = myCreator;
            myBucket.PredicateExpression.DatabaseSpecificCreator = myCreator;
            

            int i = 0;
            int i2 = 0;
            string sSQL = myBucket.Relations.ToQueryText(ref i);
            string sSQL2 = myBucket.PredicateExpression.ToQueryText(ref i2);

        }