Hi,

The following query crashes with error "Error missing FROM-clause entry for table"

 var query = from action in LinqMetaData.ActionFormation
             where action.Id == request.Id
             select new ActionFormationViewModel
             {
                 Id = action.Id,
               
                 Financeurs =     (from financeurActionFormation in LinqMetaData.FinanceurActionFormation
                                   join typeFinanceur in LinqMetaData.TypeFinanceur on financeurActionFormation.IdTypeFinanceur equals typeFinanceur.Id

                                   join financeur in LinqMetaData.Financeur on financeurActionFormation.IdFinanceur equals financeur.Id into leftJoinedFinanceurs
                                   from leftJoinedFinanceur in leftJoinedFinanceurs.DefaultIfEmpty()

                                   where financeurActionFormation.IdActionFormation == action.Id
                                   select new FinanceurActionFormationViewModel
                                   {
                                       LibelleTypeFinanceur = typeFinanceur.Libelle,
                                       Nom = leftJoinedFinanceur.Nom,
                                   }).ToList()      
            };

The error is due to the lpla_17 alias which is present in the select of the subquery but not in the FROM. In the FROM, it's LPA_L3 which is used.

SELECT LPA_L2.libelle             AS LibelleTypeFinanceur,
       lpla_17.nom                AS Nom,
       LPA_L1.id_action_formation AS IdActionFormation
FROM   ((PUBLIC.financeur_action_formation LPA_L1
         INNER JOIN PUBLIC.type_financeur LPA_L2 ON LPA_L1.id_type_financeur = LPA_L2.id)
        LEFT JOIN PUBLIC.financeur LPA_L3 ON LPA_L1.id_financeur = LPA_L3.id)
WHERE  ((( LPA_L1.id_action_formation = 1 ))) 

• if I execute the subquery on its own (one query for the action then one for the financeurs), all is OK
• If I remove the LEFT JOIN from the subquery all is OK

Which exact LLBLGen Pro runtime library version are you using?

LLBLGEN 5.9.1

It's sadly a limitation in the runtime: it apparently in this edge case it doesn't define a proper scope for the left join, leaking the alias to the outside projection. We define alias scopes around blocks where an alias of an object inside a scope, referred to an element outside this scope should use the alias of the scope itself (as the alias of the object inside the scope is local to that scope. E.g. select a.* from (select * from t as a) won't work as 'a' is an alias local to the scope of the inner query. ) However it's sometimes very hard to determine if a scope should be placed somewhere. (it's not possible to bluntly define one where a DefaultIfEmpty() is present but sometimes it has to)

The problem is particular problematic to solve as the left join is merged first into an object (the into clause) where the expression tree assigns the two branches to properties on the anonymous type. This requires our runtime to puzzle back which sides were originating from where and it looks like some information is lost to whether to define an alias scope there.

We could reproduce it with this query on SQL Server adventure works:

var metaData = new LinqMetaData(adapter);
var q = from c in metaData.Contact
        where c.ContactId == 3
        select new
               {
                   Id = c.ContactId,
                   Values = (from soh in metaData.SalesOrderHeader
                             where soh.ContactId == c.ContactId
                             join t in metaData.SalesTerritory on soh.TerritoryId equals t.TerritoryId into ljTerritories
                             from ljt in ljTerritories.DefaultIfEmpty()
                             select new
                                    {
                                        Name = ljt.Name
                                    }
                       ).ToList()
               };
var results = q.ToList();

the left join is over an FK relationship that's nullable I presume? (the one above is), otherwise an inner join is sufficient. As the left join through the DefaultIfEmpty() construct is required for the end result, we can remove it by using the navigator:

var metaData = new LinqMetaData(adapter);
var q = from c in metaData.Contact
        where c.ContactId == 3
        select new
               {
                   Id = c.ContactId,
                   Values = (from soh in metaData.SalesOrderHeader
                             where soh.ContactId == c.ContactId
                             select new
                                    {
                                        Name = soh.SalesTerritory.Name
                                    }
                       ).ToList()
               };
var results = q.ToList();

As the fk in 'soh' is nullable, it'll automatically use a left join here, avoiding the DefaultIfEmpty() construct.

Your query then becomes:

var query = from action in LinqMetaData.ActionFormation
             where action.Id == request.Id
             select new ActionFormationViewModel
             {
                 Id = action.Id,
                 Financeurs = (from financeurActionFormation in LinqMetaData.FinanceurActionFormation
                               join typeFinanceur in LinqMetaData.TypeFinanceur on financeurActionFormation.IdTypeFinanceur equals typeFinanceur.Id
                               where financeurActionFormation.IdActionFormation == action.Id
                               select new FinanceurActionFormationViewModel
                               {
                                   LibelleTypeFinanceur = typeFinanceur.Libelle,
                                   Nom = financeurActionFormation.Financeur.Nom,
                               }).ToList()      
            };  

This is a workaround for the limitation but should make the query work. With Linq there will always be these kind of edge cases where things might keel over, as it's impossible to make an error free linq provider (DefaultIfEmpty and group by/groupjoin are two of the constructs which are impossible to get right due to expression trees being produced by the compiler which lose information that's present in the actual query, information that's not needed if the query is interpreted for objects in memory but which is required for SQL in a database as the SQL query uses different scoping rules than the linq query does. )

Would this work for you? (I presume your original query is much bigger)

OK thanks for the explanation.

I found another solution : execute the subquery as a query by its own and reaffect the elements to the main query but...

gilles.marceau wrote:

OK thanks for the explanation.

I found another solution : execute the subquery as a query by its own and reaffect the elements to the main query but...

That requires you to do the merging yourself. I'd opt for the navigator workaround