Print Page | Close Window

How to use ESQL with “join” in IdeaBlade?

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2012
Forum Discription: For .NET 4.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=4068
Printed Date: 06-Sep-2025 at 2:51pm


Topic: How to use ESQL with “join” in IdeaBlade?
Posted By: JasonYan
Subject: How to use ESQL with “join” in IdeaBlade?
Date Posted: 21-Mar-2013 at 7:41pm
What I am looking for is actually the equivalence of the following code that I used in a EntityFramework project:
 ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(@"select so.ORDER_NUMBER,note.DESPATCH_NUMBER
                from SALES_ORDER_HEADERS as so
             INNER JOIN DESPATCH_NOTES as note 
                    on note.ORDER_NUMBER = so.ORDER_NUMBER
            ", entityContext);
             grid.ItemsSource = query;

the purpose here is to write Entity Sql string with INNER JOIN key word inside, and execute the string(of course it's composed during runtime) within IdeaBlade.

I wrote something like this:

input.Text = @"SELECT so.OrderNumber,note.DespatchNumber FROM SalesOrderHeaders AS so
 INNER JOIN DespatchNote AS note on note.OrderNumber = so.OrderNumber
";
            var query1 = new PassthruEsqlQuery(typeof(object), typeof(SalesOrderHeader), input.Text);
            var results1 = query1.With(mgr).Execute();

and I got the error of this:
'DespatchNote' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near simple identifier, line 2, column 13.

So my question is how should I make the second entity known to the scope or context?


Replies:
Posted By: sbelini
Date Posted: 22-Mar-2013 at 6:10pm
The issue is that DevForce eSQL queries unfortunately do not support anonymous projections.

You can, however, build the same query using linq: 


      var query = mgr

        .SalesOrderHeaders 

        .Join(mgr.DespatchNote,

            so => so.OrderNumber,

            note => note.OrderNumber,

            (so, note) => new { so.OrderNumber, note.DespatchNumber });


Is there any particular reason you chose to use eSQL?

Posted By: JasonYan
Date Posted: 23-Mar-2013 at 6:58am
The reason is we want to let some of our users with technical skills directly query data from our entity datasource. We don't have to maintain a UI to help them descibe their exact need in getting data.
we just execute the content.This is a quick solution and with the feature of using the relations(navigation properties) we already built into our entities. That was expected to be an advantage over pure SQL.

Posted By: sbelini
Date Posted: 26-Mar-2013 at 8:32am
Hi Jason,

I'm creating a feature request for eSQL to support anonymous types. 
At this point I can't guarantee if this is possible or not, so it will take some time to research and implement. (if possible)

I'd suggest using LinqPad. You can find more information about LinqPad at http://drc.ideablade.com/devforce-2012/bin/view/Documentation/linqpad-driver - http://drc.ideablade.com/devforce-2012/bin/view/Documentation/linqpad-driver .


Print Page | Close Window