DevForce Community Forum : left outer join with devforce

left outer join with devforce : thx, it works.Regards,Johannes...

Thu, 03 Nov 2011 02:40:46 -700

Author: JJANSSEN
Subject: 3056
Posted: 03-Nov-2011 at 2:40am

thx, it works.

Regards,
Johannes

left outer join with devforce : Hi Johannes;As it turns out, you...

Tue, 01 Nov 2011 16:32:43 -700

Author: DenisK
Subject: 3056
Posted: 01-Nov-2011 at 4:32pm

Hi Johannes;

As it turns out, you can actually cast the original query into an IEntityQuery and get ExecuteAsync from it.

var query = from c in mgr.Categories
                   join p in mgr.Products
                   on c equals p.Category into ps
                   from p in ps.DefaultIfEmpty()
                   select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

var ieq = query as IEntityQuery;
ieq.ExecuteAsync();

left outer join with devforce : Hi Johannes;Using PassthruEsqlQuery...

Mon, 31 Oct 2011 17:52:22 -700

Author: DenisK
Subject: 3056
Posted: 31-Oct-2011 at 5:52pm

Hi Johannes;

Using PassthruEsqlQuery is certainly one option. Another is using an RPC method where you can execute the exact same query on the server synchronously. http://drc.ideablade.com/xwiki/bin/view/Documentation/rsmc-query

One other possible solution is using a sub-query but I haven't been able to get that to work. I'm going to ask a senior engineer for suggestions. I'll let you know the result.

left outer join with devforce : Thx DenisK,the solution works...

Fri, 28 Oct 2011 07:36:56 -700

Author: JJANSSEN
Subject: 3056
Posted: 28-Oct-2011 at 7:36am

Thx DenisK,

the solution works for littel result.
But for big amount it is not a great and fast solution.
A DB-View can resolve the problem.
I search for a simple way to invoke a async left outer join Statement without a db-view.

Can it be a solution to invoke the statement with the PassthruEsqlQuery class?

Regards,
Johannes

left outer join with devforce : Hi Johannes;Yes, it's possible,...

Thu, 27 Oct 2011 20:32:32 -700

Author: DenisK
Subject: 3056
Posted: 27-Oct-2011 at 8:32pm

Hi Johannes;

Yes, it's possible, but unfortunately, in async world, things are not as straightforward as you'd like.

The query above won't work in async mode because the call "into ps" and "ps.DefaultIfEmpty()") require immediate execution. Async doesn't allow immediate execution.

At this point, we have to bring both categories and products first to the client before we can apply the join statement. There several ways to do this but one that I like to use is by way of Coroutines (http://drc.ideablade.com/xwiki/bin/view/Documentation/coroutines)

      var op = Coroutine.Start(AsyncLeftOuterJoinQueryCore);
      op.Completed += (s, args) => {
        var queryResult = args.Result;
      };

   private IEnumerable AsyncLeftOuterJoinQueryCore() {
      var mgr = new EntityManager();
      
      //bring down categories
      var catQuery = mgr.Categories;
      var catOp = catQuery.ExecuteAsync();
      yield return catOp;

      //bring down products
      var prodQuery = mgr.Products;
      var prodOp = prodQuery.ExecuteAsync();
      yield return prodOp;

      var categories = catOp.Results;
      var products = prodOp.Results;

      var result = from c in categories
                   join p in products
                   on c equals p.Category into ps
                   from p in ps.DefaultIfEmpty()
                   select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };
      
      yield return Coroutine.Return(result);
    }

Hope this helps.

left outer join with devforce : Hi,is it possible to invoke Async...

Wed, 26 Oct 2011 10:36:53 -700

Author: JJANSSEN
Subject: 3056
Posted: 26-Oct-2011 at 10:36am

Hi,

is it possible to invoke Async Query with a left out join between two tables?

This is an example from http://msdn.microsoft.com/en-us/vcsharp/ee908647#leftouterjoin . But DefaultIfEmpty() destroy the asyncable syntax, "query.ExecuteAsync()" is not valid.


   var query = from c in Manager.categories 
                    join p in products on c equals p.Category into ps     
                    from p in ps.DefaultIfEmpty() 
                    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName }; 
   return query.ExecuteAsync().OnComplete(onSuccess, onFail);

Regards,
Johannes