DevForce 2010: left outer join with devforce

left outer join with devforce

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3056
Printed Date: 13-May-2026 at 7:18pm

Topic: left outer join with devforce

Posted By: JJANSSEN
Subject: left outer join with devforce
Date 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 - 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

Replies:

Posted By: DenisK
Date 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 - http://drc.ideablade.com/xwiki/bin/view/Documentation/coroutines )

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

   private IEnumerable<INotifyCompleted> 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.

Posted By: JJANSSEN
Date 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

Posted By: DenisK
Date 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 - 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.

Posted By: DenisK
Date 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();

Posted By: JJANSSEN
Date Posted: 03-Nov-2011 at 2:40am

thx, it works.

Regards,
Johannes