Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Working without UNION
 

Pages: 1
LLBLGen Pro Runtime Framework
Working without UNION
Page:1/1 

  Print all messages in this thread  
Poster Message
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 21-Jan-2009 08:21:44.  
I am working on porting a major financial system from a VB6 Client-Server architecture to .NET. One of the pains the old system suffered from is maintenance as most of the logic is written in SPROCs.
In upgrading to .NET it was decided to port all the logic into the BL tier. A lot of the SPROC's logic depends on using table variables that collect data based on different queries.
To simulate this in LLBL's LINQ one would write the same queries in LINQ but then there is no way to UNION the queries together so the resulting query can be used later in a new query.
The only way out is to actual bring the data of the queries from the DB to the BL but that can prove un-practical or very slow.

Is there a workaround in LLBL for the missing UNION operator???
We don't know who we are until we see what we can do  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 21-Jan-2009 10:46:58.  
The options are:

1- If you need to perform the UNION on the database side, then you should use a database View to host the entire posted SQL, and then map this to a TypedView or an Entity to be fetched by LLBLGen Pro.

2- If you want to pass paramteres, then instead of a View use a Stored Procedure which then can be mapped to a StoredProcedureCall.

3- If the Union can be done at client side (outside the database and inside your application), then maybe you need to use 2 DynamicLists for which the resultsSets (dataTables) can be merged.


  Top
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 21-Jan-2009 15:47:46.  
My case is number 3 but also I cannot use your suggested solution. My case is that I am re-writing a big sproc that has logic which progresses through building complex queries by using table variables. The queries insert into the same table variable in effect making a union of the result of each query.
I wanted to follow the same logic by using a LINQ query for each table variable in the sproc.

The problem is to how union each query to the next as LLBL's LINQ does not support UNION.
We don't know who we are until we see what we can do  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
# Posted on: 21-Jan-2009 17:17:38.  
At the moment there's no way to pass a union command to LLBLGen Pro, as it doesn't have a query object, so chaining queries together isn't possible. Normally this isn't a problem, as the commands are oriented towards fetching a given resultset and that specification is used to build the query, though if the query is actually build from two or more resultsets, it gets suddenly much more complicated, as you need for every subset the set is build from a new specification.

I'm not sure if you can re-write things towards derived tables and join these together? Derived tables can have a similar effect, and by joining them together you could create a set from which you can fetch the end resultset again, but it might be too complex to produce this as it might be that the current query itself is extremely big.





Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
mohamed
User



Location:
Salmia, kuwait
Joined on:
10-Mar-2008 19:55:21
Posted:
136 posts
# Posted on: 21-Jan-2009 18:37:08.  
The nice thing about chaining queries together using UNION is that you get to build complicated queries progressively and using whatever like business logic.
Then at the end you get to execute the chained resulting query in one go without the need to make multiple trips to the DB or fetch result sets into derived tables.

Ofcourse this does not cover ALL the possible cases that require derived tables and fetched data but it does give one a very valuable tool to think in the direction of single-trip execution.

One way the team was discussing is to use left joins all queries to try to simulate a union of their content but I am still not sure this is safe or valid!!
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
# Posted on: 22-Jan-2009 10:43:57.  
I know, though at the moment there's not really much I can offer you, as there's no way you can specify a UNION in a query within llblgen pro.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 23-Jan-2009 19:12:19.  
FRANS.. just for the sake of sake of knowing:
1- is it possible to explain why the architecture of LLBL cannot support UNION and UNION ALL
2- Any plans to add this support in an upcoming version (hopeful for this to go the same way as Derived Tables did)
We don't know who we are until we see what we can do  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37797 posts
# Posted on: 25-Jan-2009 11:27:06.  
omar wrote:
FRANS.. just for the sake of sake of knowing:
1- is it possible to explain why the architecture of LLBL cannot support UNION and UNION ALL

The reason is simple: say you want to fetch a set of entities. To specify that fetch, you'd probably call FetchEntityCollection by specifying some parameters. The resultset is defined by the list of fields of the entity type to fetch, and the parameters are to limit the resultset, to sort it.

When UNION should be supported, you should be able to specify 2 or more FetchEntityCollection calls together which should be migrated into 1 query, as every call results into a full query and UNION is actually just a way to chain multiple SQL queries which can be unrelated, together.

If there would be a query object in the API, where the query object would be the parameter of the FetchEntityCollection method (and other fetch methods), it would be rather easy to support UNION, as the Query object would get an option to get an additional query object or a master query object would be introduced which would simply chain containing query objects together.

There are other reasons why it's not supported: inheritance and union is very complex and not really doable, as union suggests that you chain queries together which are not always related, though with inheritance the resulting query isn't known up front (it depends on the # of subtypes for example).

Quote:

2- Any plans to add this support in an upcoming version (hopeful for this to go the same way as Derived Tables did)

Well, definitely not for entity types. We could look into supporting UNION for sets of data (projections) which are then usable for either projections or in filters (so in that light it would be useful).

I'll add it to the todo list for v3, but it's not 100% sure this will be in v3.0. (as v3.0 already has a huge truckload of changes in the designer and also in the runtime (value types for example) so it depends if there is enough time)


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.