DevForce Community Forum : How to use ESQL with “join” in IdeaBlade?

How to use ESQL with “join” in IdeaBlade? : Hi Jason,I'm creating a feature...

Tue, 26 Mar 2013 08:32:32 -700

Author: sbelini
Subject: 4068
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.

How to use ESQL with “join” in IdeaBlade? : The reason is we want to let some...

Sat, 23 Mar 2013 06:58:29 -700

Author: JasonYan
Subject: 4068
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.

How to use ESQL with “join” in IdeaBlade? : The issue is that DevForce eSQL...

Fri, 22 Mar 2013 18:10:27 -700

Author: sbelini
Subject: 4068
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?

How to use ESQL with “join” in IdeaBlade? : What I am looking for isactuallytheequivalenceof...

Thu, 21 Mar 2013 19:41:55 -700

Author: JasonYan
Subject: 4068
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 query = new ObjectQuery(@"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?