Working without UNION

Posts   
 
    
omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# 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???

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# 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.

omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# 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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
mohamed avatar
mohamed
User
Posts: 136
Joined: 10-Mar-2008
# 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!!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# 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)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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).

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 | Lead developer LLBLGen Pro