New Posts New Posts RSS Feed: left outer join with devforce
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

left outer join with devforce
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
JJANSSEN View Drop Down
Newbie
Newbie


Joined: 25-Aug-2011
Posts: 10 		Post Options Post Options   Quote JJANSSEN Quote  Post ReplyReply Direct Link To This Post Topic: left outer join with devforce
    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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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<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.
Back to Top
JJANSSEN View Drop Down
Newbie
Newbie


Joined: 25-Aug-2011
Posts: 10 		Post Options Post Options   Quote JJANSSEN Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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();
Back to Top
JJANSSEN View Drop Down
Newbie
Newbie


Joined: 25-Aug-2011
Posts: 10 		Post Options Post Options   Quote JJANSSEN Quote  Post ReplyReply Direct Link To This Post Posted: 03-Nov-2011 at 2:40am
thx, it works.

Regards,
Johannes
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down