Need help on complex query (double nested query with grouping)... "JOIN clause not supported" error

Posts   
 
    
Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 19-Nov-2010 17:01:40   

I'm trying to implement a complex query... but I'm encountering some issue disappointed

Here is the SQL version,

SELECT qp2.idpersona,
       personaevento.idpersonaprofessionista,
       personaevento.idpersonaevento,
       qp2.maxdidataevento,
       qp2.maxdiprogressivoevento,
       personaevento.codmacrostatus,
       personaevento.codregistro,
       personaevento.numiscrizione,
       personaevento.alfaiscrizione
    
FROM   (

        SELECT qp1.idpersona,
               qp1.maxdidataevento,
               MAX(personaevento.progressivoevento) AS maxdiprogressivoevento
            
        FROM   (
        
                SELECT personaprofessionista.idpersona,
                       MAX(personaevento.dataevento) AS maxdidataevento
                FROM   personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista
                WHERE  ( ( personaevento.dataevento <=# 11 / 6 / 2009 # ) AND ( personaprofessionista.flginterno = true ) )
                GROUP  BY personaprofessionista.idpersona
                
                
                ) AS qp1    INNER JOIN 
                                        (personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
                            ON  qp1.maxdidataevento = personaevento.dataevento  AND  qp1.idpersona = personaprofessionista.idpersona 
        GROUP  BY qp1.idpersona, qp1.maxdidataevento
        
        
        ) AS qp2 INNER JOIN 
                             (personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
                 ON  qp2.maxdiprogressivoevento = personaevento.progressivoevento  AND  qp2.maxdidataevento = personaevento.dataevento  AND  qp2.idpersona = personaprofessionista.idpersona

Involved Table: PersonaProfessionista PersonaEvento

The relation: PersonaProfessionista.IdPersonaProfessionista 1 --> n PersonaEvento.IdPersonaProfessionista

The Fields: PersonaProfessionista.IdPersonaProfessionista : Key (Int) PersonaProfessionista.IdPersona : Person unique identifier (Int)

PersonaEvento.IdPersonaProfessionista : hook many PersonaEventoEntity to a single PersonaProfessionistaEntity PersonaEvento.DataEvento: define a date at which some of the useful data changed PersonaEvento.ProgressivoEvento: define an incremental counter unique in a series of PersonaEventoEntity owned by a specific PersonaProfessionistaEntity PersonaEvento.codmacrostatus: Useful data that I need to retrieve PersonaEvento.codregistro: Useful data that I need to retrieve PersonaEvento.numiscrizione: Useful data that I need to retrieve PersonaEvento.alfaiscrizione: Useful data that I need to retrieve

The thing works like that: I have many people in the DB, each person own many PersonaProfessionistaEntity (via PersonaProfessionista.IdPersona) each PersonaProfessionistaEntity own many PersonaEventoEntity each PersonaEvento define some useful data at some point in time. So each PersonaProfessionistaEntity own a series of PersonaEventoEntity, and due to that each PersonaProfessionistaEntity cover a period of time, from a a starting date to an ending date. A single person own many PersonaProfessionistaEntity, but they do not overlap in time.

I need a query that respond to the following question: which was the value of the field PersonaEvento.numiscrizione at the date 15/6/2008? I need to fetch a list of all the people with the required data.

So the Inner query find the DataEvento of the most recent data (respect to the asked date) for each people, the second query looks for the max ProgressivoEvento, because it can happen that we have many data change in a single day, and so the last one is the one that apply. The third external query use the MaxDataEvento and the MaxProgressivoEvento to reach the useful data related to the Person/MaxDataEvento/MaxProgressivoEvento.

Do anyone know a better way for attaining this results?

Anyway, in SQL it's easy... the aforementioned query do exactly that. I've tried the LINQ way, but I'm a newbie in LINQ, and I've just discovered that it's harder than I tough, MS documentation about LINQ doesen't help, and I don't have time to read a 500 pages book... disappointed So, let's go the hard way, and try the derived table (after all some years ago I've asked for LLBLGen to support derived table exactly for this kind of query sunglasses )

To simplify a bit I've cut the external query, and I'm trying to implement the following SQL:

SELECT qp1.idpersona,
               qp1.maxdidataevento,
               MAX(personaevento.progressivoevento) AS maxdiprogressivoevento
            
        FROM   (
        
                SELECT personaprofessionista.idpersona,
                       MAX(personaevento.dataevento) AS maxdidataevento
                FROM   personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista
                WHERE  ( ( personaevento.dataevento <=# 11 / 6 / 2009 # ) AND ( personaprofessionista.flginterno = true ) )
                GROUP  BY personaprofessionista.idpersona
                
                
                ) AS qp1    INNER JOIN 
                                        (personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
                            ON  qp1.maxdidataevento = personaevento.dataevento  AND  qp1.idpersona = personaprofessionista.idpersona 
        GROUP  BY qp1.idpersona, qp1.maxdidataevento

I've come up with the following code:

Dim a As IDataAccessAdapter = DAL.DataAdapterFactory.GetThreadDataAdapter(enDalDB.eDBDati)

        Dim dataPar As Date
        dataPar = #11/6/2010#

        '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        Dim fieldsMaxDataEvento As New ResultsetFields(2)
        fieldsMaxDataEvento.DefineField(PersonaProfessionistaFields.Idpersona, 0)
        fieldsMaxDataEvento.DefineField(PersonaEventoFields.DataEvento.SetAggregateFunction(AggregateFunction.Max), 1)


        Dim rpbMaxDataEvento As New RelationPredicateBucket
        rpbMaxDataEvento.PredicateExpression.Add(PersonaEventoFields.DataEvento <= dataPar)
        rpbMaxDataEvento.PredicateExpression.Add(PersonaProfessionistaFields.FlgInterno = True)
        rpbMaxDataEvento.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)

        Dim groupByMaxDataEvento As New GroupByCollection(PersonaProfessionistaFields.Idpersona)
        Dim queryMaxDataEvento As New DerivedTableDefinition(fieldsMaxDataEvento, "queryMaxDataEvento", rpbMaxDataEvento.PredicateExpression, rpbMaxDataEvento.Relations, groupByMaxDataEvento)
        '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


        '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        Dim groupByMaxProgressivo As New GroupByCollection()
        groupByMaxProgressivo.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"))
        groupByMaxProgressivo.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"))
        '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



        Dim relationToQueryMaxDataEvento As New DBDati.RelationClasses.DynamicRelation(
                            queryMaxDataEvento,
                            JoinHint.Inner,
                            EntityType.PersonaProfessionistaEntity,
                            "",
                            (PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento") = PersonaEventoFields.DataEvento) And
                            (PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento") = PersonaProfessionistaFields.Idpersona)
                            )


        Dim resultSet As New EntityFields2(3)
        resultSet.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"))
        resultSet.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"))
        resultSet.Add(PersonaEventoFields.ProgressivoEvento.SetAggregateFunction(AggregateFunction.Max))


        Dim rpb As New RelationPredicateBucket
        rpb.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
        rpb.Relations.Add(relationToQueryMaxDataEvento)

        Dim dt As New DataTable

        a.FetchTypedList(resultSet, dt, rpb, 0, Nothing, True, groupByMaxProgressivo)

that generate the following query

SELECT [LPA_q1].[Idpersona], 
                [LPA_q1].[DataEvento], 
                MAX([PersonaEvento].[ProgressivoEvento]) AS [ProgressivoEvento] 
        FROM (( [PersonaProfessionista]  INNER JOIN [PersonaEvento]  ON  [PersonaProfessionista].[IDPersonaProfessionista]=[PersonaEvento].[IDPersonaProfessionista]) 

        INNER JOIN (
                SELECT [PersonaProfessionista].[IDPersona] AS [Idpersona], 
                        MAX([PersonaEvento].[DataEvento]) AS [DataEvento] 
                FROM ( [PersonaProfessionista]  INNER JOIN [PersonaEvento]  ON  [PersonaProfessionista].[IDPersonaProfessionista]=[PersonaEvento].[IDPersonaProfessionista])
                WHERE ( [PersonaEvento].[DataEvento] <= # 11 / 6 / 2009 # AND [PersonaProfessionista].[flgInterno] = true)
                GROUP BY [PersonaProfessionista].[IDPersona]
                ) [LPA_q1]  
                           ON  ( [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND 
                                 [LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona]))
        
        GROUP BY [LPA_q1].[Idpersona], [LPA_q1].[DataEvento]

the query seem perfect.... but doesn't run and give an error of "JOIN clause not supported"

with some trial and error I've found that Access engine have some problem with a pair of parentheses.

If I change the following part of the generated SQL (It's the On clause of the last Join)

 ON  ( [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND 
                                 [LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona]))

to

ON   [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND 
                                 [LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona])

it works. I've only removed the parentheses around the ON clause confused

I'm using LLBLGen 2.6 latest version, VS 2010, VB.Net, .Net 3.5, Database: Access (and SQL Server... but I've not tested the issue on SQL Server)

Now... I don't want to hardcode the query in the DB, It's bad to maintain when you have many customers. I'm not good enough at LINQ to create this query in the LLBLGen to LINQ way.

I'm stuck... What can I do? A fix? A better way to handle this kind of data? The LINQ way? but this will probably generate the same SQL giving the same error...

Thanks, Massimiliano

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Nov-2010 18:56:07   

Hi there,

I will try to reproduce it with a Northwind DB (Access). If you can try too would be helpful. What is your LLBLGen runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

David Elizondo | LLBLGen Support Team
Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 19-Nov-2010 22:12:49   

daelmo wrote:

Hi there,

I will try to reproduce it with a Northwind DB (Access). If you can try too would be helpful. What is your LLBLGen runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

I'm using LLBLGenPro 2.6 Final (09-October-2009) (Library Lib 2.6.10.0930) On Monday I'll try to reproduce this error on Northwind DB.

Thanks, Massimiliano

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 22-Nov-2010 10:55:29   

daelmo wrote:

Hi there,

I will try to reproduce it with a Northwind DB (Access). If you can try too would be helpful. What is your LLBLGen runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

I'm creating a sample project using a reduced version of our DB, it'll going to take less time than trying to reproduce the error on the Northwind DB simple_smile I'll post here the sample project once finished.

Thanks, Massimiliano

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 22-Nov-2010 11:52:51   

daelmo wrote:

Hi there,

I will try to reproduce it with a Northwind DB (Access). If you can try too would be helpful. What is your LLBLGen runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

I've posted the sample project on the HelpDesk forum (I don't like disclosing the structure of our DB simple_smile )

I've used the same post title "Need help on complex query (double nested query with grouping)... "JOIN clause not supported" error"

Thanks, Massimiliano

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Nov-2010 16:33:13   

I'll close this thread for the time being, so we can continue in the other thread.

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 23-Nov-2010 11:54:15   

Thanks to LLBLGen Support Team. Upon their advice I've created a custom PredicateExpression class that strip the external brackets from the ON clause of the Join. Here's the code:

''' <summary>
''' Remove the most external bracket from the generated QueryText.
''' If the generated QueryText start with a "(" and end with a ")", these brackets will be removed.
''' </summary>
''' <remarks>Useful for predicateExpression in the ON part of the JOIN in Access DB, where external brackets are not always supported</remarks>

Public Class BracketsRemoverPredicateExpression
    Inherits PredicateExpression

    Public Overrides Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String
        Return RemoveExternalBrackets(MyBase.ToQueryText(uniqueMarker, inHavingClause))
    End Function

    Private Function RemoveExternalBrackets(ByVal pQueryText As String) As String
        pQueryText = pQueryText.Trim

        'Trace.WriteLine(pQueryText)
        If pQueryText.StartsWith("(") AndAlso pQueryText.EndsWith(")") Then pQueryText = " " & pQueryText.Substring(1, pQueryText.Length - 2).Trim & " "
        'Trace.WriteLine(pQueryText)

        Return pQueryText
    End Function

End Class

Naturally there is some code that use this class only when I'm working on an Access DB simple_smile

Max avatar
Max
User
Posts: 221
Joined: 14-Jul-2006
# Posted on: 23-Nov-2010 15:31:24   

Using DerivedTableDefinition and DynamicRelation with grouping is not the easiest thing in the world simple_smile so, for the sake of completeness, here is the code for the full SQL (3 nestedselect, 2 using grouping) sunglasses

Needed SQL

SELECT qp2.idpersona,
     personaevento.idpersonaprofessionista,
     personaevento.idpersonaevento,
     qp2.maxdidataevento,
     qp2.maxdiprogressivoevento,
     personaevento.codmacrostatus,
     personaevento.codregistro,
     personaevento.numiscrizione,
     personaevento.alfaiscrizione
    
FROM (

        SELECT qp1.idpersona,
             qp1.maxdidataevento,
             MAX(personaevento.progressivoevento) AS maxdiprogressivoevento
            
        FROM (
        
                SELECT personaprofessionista.idpersona,
                     MAX(personaevento.dataevento) AS maxdidataevento
                FROM personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista
                WHERE ( ( personaevento.dataevento <=# 11 / 6 / 2009 # ) AND ( personaprofessionista.flginterno = true ) )
                GROUP BY personaprofessionista.idpersona
                
                
                ) AS qp1    INNER JOIN
                                        (personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
                            ON qp1.maxdidataevento = personaevento.dataevento AND qp1.idpersona = personaprofessionista.idpersona
        GROUP BY qp1.idpersona, qp1.maxdidataevento
        
        
        ) AS qp2 INNER JOIN
                             (personaprofessionista INNER JOIN personaevento ON personaprofessionista.idpersonaprofessionista = personaevento.idpersonaprofessionista)
                 ON qp2.maxdiprogressivoevento = personaevento.progressivoevento AND qp2.maxdidataevento = personaevento.dataevento AND qp2.idpersona = personaprofessionista.idpersona

Generated SQL

SELECT [LPA_q1].[Idpersona],
       [PersonaEvento].[IDPersonaProfessionista] AS [IdpersonaProfessionista],
       [PersonaEvento].[IDPersonaEvento]         AS [IdpersonaEvento],
       [LPA_q1].[DataEvento],
       [LPA_q1].[ProgressivoEvento],
       [PersonaEvento].[CodMacroStatus],
       [PersonaEvento].[CodRegistro],
       [PersonaEvento].[NumIscrizione],
       [PersonaEvento].[AlfaIscrizione]
FROM   ( (
            [PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista] = [PersonaEvento].[IDPersonaProfessionista]
         ) INNER JOIN (
                        SELECT [LPA_q2].[Idpersona], [LPA_q2].[DataEvento], MAX([PersonaEvento].[ProgressivoEvento]) AS [ProgressivoEvento]
                        FROM   ( 
                                 (
                                    [PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista] = [PersonaEvento].[IDPersonaProfessionista]
                                 ) INNER JOIN (
                                                SELECT [PersonaProfessionista].[IDPersona] AS [Idpersona], MAX([PersonaEvento].[DataEvento]) AS [DataEvento]
                                                FROM   ( [PersonaProfessionista] INNER JOIN [PersonaEvento] ON [PersonaProfessionista].[IDPersonaProfessionista] = [PersonaEvento].[IDPersonaProfessionista])
                                                WHERE  ( [PersonaEvento].[DataEvento] <= @DataEvento1 AND [PersonaProfessionista].[flgInterno] = @FlgInterno2 )
                                                GROUP  BY [PersonaProfessionista].[IDPersona]
                                              ) [LPA_q2]
                                   ON [LPA_q2].[DataEvento] = [PersonaEvento].[DataEvento] AND 
                                      [LPA_q2].[Idpersona] = [PersonaProfessionista].[IDPersona]
                               )
                        GROUP  BY [LPA_q2].[Idpersona], [LPA_q2].[DataEvento]
                       ) [LPA_q1]
            ON [LPA_q1].[DataEvento] = [PersonaEvento].[DataEvento] AND 
               [LPA_q1].[ProgressivoEvento] = [PersonaEvento].[ProgressivoEvento] AND 
               [LPA_q1].[Idpersona] = [PersonaProfessionista].[IDPersona] 
        )

Respect the needed SQL I've changed the order of table/query in join... the original order wasn't working.

here is the VB code

Dim a As IDataAccessAdapter = DAL.DataAdapterFactory.GetThreadDataAdapter(enDalDB.eDBDati)

        Dim dataPar As Date
        dataPar = #11/6/2010#


        '==========================================================================================================================================================
        ' Internal query
        '----------------------------------------------------------------------------------------------------------------------------------------------------------
        Dim fieldsMaxDataEvento As New ResultsetFields(2)
        fieldsMaxDataEvento.DefineField(PersonaProfessionistaFields.Idpersona, 0)
        fieldsMaxDataEvento.DefineField(PersonaEventoFields.DataEvento.SetAggregateFunction(AggregateFunction.Max), 1)

        Dim rpbMaxDataEvento As New RelationPredicateBucket
        rpbMaxDataEvento.PredicateExpression.Add(PersonaEventoFields.DataEvento <= dataPar)
        rpbMaxDataEvento.PredicateExpression.Add(PersonaProfessionistaFields.FlgInterno = True)
        rpbMaxDataEvento.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)

        Dim groupByMaxDataEvento As New GroupByCollection(PersonaProfessionistaFields.Idpersona)
        Dim queryMaxDataEvento As New DerivedTableDefinition(fieldsMaxDataEvento, "queryMaxDataEvento",
                                                         rpbMaxDataEvento.PredicateExpression,
                                                         rpbMaxDataEvento.Relations,
                                                         groupByMaxDataEvento)
        '==========================================================================================================================================================





        '==========================================================================================================================================================
        ' Relation to join the internal query with the central query
        '----------------------------------------------------------------------------------------------------------------------------------------------------------

        'the PredicateExpression joinONqueryMaxDataEvento is needed due to access not supporting brackets in fome JOIN clause
        Dim joinONqueryMaxDataEvento As PredicateExpression
        If DAL.DalConfig.DBDatiDatabaseType = enDbType.eAccess Then
            'DB is Access, I need to strip the external brackets of the ON part of the JOIN clause (access doesent support brackets in this query)
            joinONqueryMaxDataEvento = New BracketsRemoverPredicateExpression
        Else
            'It's not Access, let's LLBLGen do it's job as usal
            joinONqueryMaxDataEvento = New PredicateExpression
        End If
        joinONqueryMaxDataEvento.AddWithAnd(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento") = PersonaEventoFields.DataEvento)
        joinONqueryMaxDataEvento.AddWithAnd(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento") = PersonaProfessionistaFields.Idpersona)

        Dim relationToQueryMaxDataEvento As New DBDati.RelationClasses.DynamicRelation(
                            queryMaxDataEvento,
                            JoinHint.Inner,
                            EntityType.PersonaProfessionistaEntity,
                            "",
                            joinONqueryMaxDataEvento
                            )
        '==========================================================================================================================================================



        '==========================================================================================================================================================
        ' Central query
        '----------------------------------------------------------------------------------------------------------------------------------------------------------

        Dim fieldsMaxProgressivo As New ResultsetFields(3)
        fieldsMaxProgressivo.DefineField(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"), 0)
        fieldsMaxProgressivo.DefineField(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"), 1)
        fieldsMaxProgressivo.DefineField(PersonaEventoFields.ProgressivoEvento.SetAggregateFunction(AggregateFunction.Max), 2)

        Dim rpbMaxProgressivo As New RelationPredicateBucket
        'note that the order in wich the relation are added to the rpbMaxProgressivo matter, if you change the order it dosen't works
        rpbMaxProgressivo.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
        rpbMaxProgressivo.Relations.Add(relationToQueryMaxDataEvento)

        Dim groupByMaxProgressivo As New GroupByCollection()
        groupByMaxProgressivo.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxDataEvento"))
        groupByMaxProgressivo.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxDataEvento"))


        Dim queryMaxProgressivo As New DerivedTableDefinition(fieldsMaxProgressivo, "queryMaxProgressivo",
                                                          rpbMaxProgressivo.PredicateExpression,
                                                          rpbMaxProgressivo.Relations,
                                                          groupByMaxProgressivo)
        '==========================================================================================================================================================




        '==========================================================================================================================================================
        ' Relation to join the central query with the external query
        '----------------------------------------------------------------------------------------------------------------------------------------------------------

        'the PredicateExpression joinONqueryMaxProgressivo is needed due to access not supporting brackets in fome JOIN clause
        Dim joinONqueryMaxProgressivo As PredicateExpression
        If DAL.DalConfig.DBDatiDatabaseType = enDbType.eAccess Then
            'DB is Access, I need to strip the external brackets of the ON part of the JOIN clause (access doesent support brackets in this query)
            joinONqueryMaxProgressivo = New BracketsRemoverPredicateExpression
        Else
            'It's not Access, let's LLBLGen do it's job as usal
            joinONqueryMaxProgressivo = New PredicateExpression
        End If
        joinONqueryMaxProgressivo.AddWithAnd(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxProgressivo") = PersonaEventoFields.DataEvento)
        joinONqueryMaxProgressivo.AddWithAnd(PersonaEventoFields.ProgressivoEvento.SetObjectAlias("queryMaxProgressivo") = PersonaEventoFields.ProgressivoEvento)
        joinONqueryMaxProgressivo.AddWithAnd(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxProgressivo") = PersonaProfessionistaFields.Idpersona)

        Dim relationToQueryMaxProgressivo As New DBDati.RelationClasses.DynamicRelation(
                            queryMaxProgressivo,
                            JoinHint.Inner,
                            EntityType.PersonaProfessionistaEntity,
                            "",
                            joinONqueryMaxProgressivo
                            )
        '==========================================================================================================================================================






        '==========================================================================================================================================================
        ' External query
        '----------------------------------------------------------------------------------------------------------------------------------------------------------

        Dim resultSet As New EntityFields2(1)
        resultSet.Add(PersonaProfessionistaFields.Idpersona.SetObjectAlias("queryMaxProgressivo"))
        resultSet.Add(PersonaEventoFields.IdpersonaProfessionista)
        resultSet.Add(PersonaEventoFields.IdpersonaEvento)
        resultSet.Add(PersonaEventoFields.DataEvento.SetObjectAlias("queryMaxProgressivo"))
        resultSet.Add(PersonaEventoFields.ProgressivoEvento.SetObjectAlias("queryMaxProgressivo"))
        resultSet.Add(PersonaEventoFields.CodMacroStatus)
        resultSet.Add(PersonaEventoFields.CodRegistro)
        resultSet.Add(PersonaEventoFields.NumIscrizione)
        resultSet.Add(PersonaEventoFields.AlfaIscrizione)


        Dim rpb As New RelationPredicateBucket
        'note that the order in wich the relation are added to the RPB matter, if you change the order it dosen't works
        rpb.Relations.Add(PersonaProfessionistaEntity.Relations.PersonaEventoEntityUsingIdpersonaProfessionista)
        rpb.Relations.Add(relationToQueryMaxProgressivo)


        Dim dt As New DataTable
        a.FetchTypedList(resultSet, dt, rpb)
        '==========================================================================================================================================================

...and it works like a charm simple_smile