1) The SQL query I want to obtain

SELECT third_party.Id
FROm third_party_customer
INNER JOIN third_party ON third_party.Id = third_party_customer.Id
WHERE EXISTS
(
  SELECT sale_order.Id
  FROM sale_order
  INNER JOIN third_party_customer ON third_party_customer.Id = sale_order.FK_third_party_customer_Id_Invoiced
)

2) How I write it in QuerySpec

    QueryFactory qf = new QueryFactory();

    var ordersQuery = qf.SaleOrder
        .CorrelatedOver(SaleOrderFields.FkThirdPartyCustomerIdInvoiced == ThirdPartyCustomerFields.Id)
        .Select(() => new
        {
            Id = SaleOrderFields.Id.ToValue<long>()
        });

    var query = qf.ThirdPartyCustomer.OfEntityType<ThirdPartyCustomerEntity>()
        .Select(() => new Customer
        {
            Id = ThirdPartyCustomerFields.Id.ToValue<long>()
        })
        .WhereExists(ordersQuery);

3) Error occuring during execution

Unknown column 'sale_order.Id' in 'field list'.

4) SQL query generated

SELECT `LPA_L2`.`Id`
FROM 
( 
  `third_party` `LPA_L1`
  LEFT JOIN `third_party_customer` `LPA_L2`  ON  `LPA_L1`.`Id`=`LPA_L2`.`Id`
) 
WHERE 
( 
  (
    ( 
      ( 
        ( `LPA_L2`.`Id` IS NOT NULL) 
        AND  EXISTS 
        (
          SELECT `sale_order`.`Id` 
          FROM 
          ( 
            `third_party`
            LEFT JOIN `third_party_customer`  ON  `third_party`.`Id`=`third_party_customer`.`Id`
          ) 
          WHERE 
          ( 
            `sale_order`.`FK_third_party_customer_Id_Invoiced` = `LPA_L2`.`Id`
          )
        )
      )
    )
  )
)

Can you help me to write this query? Thanks in advance

Note : I thought OfEntityType will convert the join in inner join but no, it just add a LPA_L2.Id IS NOT NULL clause.

Which runtime library version are you using? (please refer to the forum guidelines thread for more info).

The SQL query I want to obtain

Code: SELECT third_party.Id FROm third_party_customer INNER JOIN third_party ON third_party.Id = third_party_customer.Id WHERE EXISTS ( SELECT sale_order.Id FROM sale_order INNER JOIN third_party_customer ON third_party_customer.Id = sale_order.FK_third_party_customer_Id_Invoiced )

Why not using an InnerJoin instead of the Exists subquery?

SELECT third_party.Id
FROm third_party_customer
INNER JOIN third_party ON third_party.Id = third_party_customer.Id
INNER JOIN sale_order ON third_party_customer.Id = sale_order.FK_third_party_customer_Id_Invoiced

1) LLBLGen Pro 3.5 Final (January, 17th 2013).

2.a) Because the code I wrote in the message is a "simplified" version of my real code to focus on my matter and not technical information.

2.b) Because the query is built dynamically according to filter like "with orders", "without orders","with or without orders"

Are ThirdParty & ThirdPartyCustomer in an inheritance hierarchy? Could you please explain the relation between them?

Also could you please use the latest release of v.3.5?

ThirdPartyCustomer represents a customer which is also a third party, so yes there is an inheritance between both.

In database, third_party.Id = third_party_customer.Id

Note : Anyway, it's strange that when I use

qf.ThirdPartyCustomer.OfEntityType<ThirdPartyCustomerEntity>()

it's converted in LEFT JOIN instead of INNER JOIN as done in Linq when using .Cast<ThirdPartyCustomerEntity>

The inheritance filter works with left joins + a filter on not-null. This is a universal filter which works in all situations, and gives the same results as when you'd do an inner join in this case, however would need no extra logic in other situations: it can always be applied.

Cast or TypeOf in Linq should add the same type filter to the query, as they simply result in the same low level constructs as the queryspec query does.

The statement:

var query = qf.ThirdPartyCustomer.OfEntityType<ThirdPartyCustomerEntity>()

doesn't need the type filter, qf.ThirdPartyCustomer already produces a query which filters on ThirdPartyCustomer instances.

If you remove it (the OfEntityType), does it work then?

With OfEntityType<ThirdPartyCustomerEntity>()

SELECT `LPA_L2`.`Id` 
FROM ( `third_party` `LPA_L1`  
LEFT JOIN `third_party_customer` `LPA_L2`  ON  `LPA_L1`.`Id`=`LPA_L2`.`Id`) 
WHERE ( ( ( ( ( `LPA_L2`.`Id` IS NOT NULL) AND  EXISTS 
(
  SELECT `sale_order`.`Id` 
  FROM ( `third_party`  
   LEFT JOIN `third_party_customer`  ON  `third_party`.`Id`=`third_party_customer`.`Id`) 
   WHERE ( `sale_order`.`FK_third_party_customer_Id_Invoiced` = `LPA_L2`.`Id`)))))
)

Without OfEntityType<ThirdPartyCustomerEntity>()

SELECT `LPA_L2`.`Id`
FROM ( `third_party` `LPA_L1`
LEFT JOIN `third_party_customer` `LPA_L2`  ON  `LPA_L1`.`Id`=`LPA_L2`.`Id`)
WHERE ( ( ( (  EXISTS 
(
SELECT `sale_order`.`Id` 
FROM ( `third_party`  
LEFT JOIN `third_party_customer`  ON  `third_party`.`Id`=`third_party_customer`.`Id`)
WHERE ( `sale_order`.`FK_third_party_customer_Id_Invoiced` = `LPA_L2`.`Id`)))))
)

In fact, we already had this discussion on one of my previous thread "LEFT JOIN instead of INNER JOIN when table inherits from another one" and you told me to use Cast method with Linq so I found OfEntityType which seems to be the same on QuerySpec.

To come back to my initial matter, if I write the WHERE EXISTS query without using inheritance, the query returns me the expected results

SELECT `third_party`.`Id`
FROM `third_party`
WHERE ( ( ( (
EXISTS
(
  SELECT `sale_order`.`Id`
  FROM `sale_order`
  WHERE ( `sale_order`.`FK_third_party_customer_Id_Invoiced` = `third_party`.`Id`)))))
)

sybjeb wrote:

With OfEntityType<ThirdPartyCustomerEntity>()

SELECT `LPA_L2`.`Id` 
FROM ( `third_party` `LPA_L1`  
LEFT JOIN `third_party_customer` `LPA_L2`  ON  `LPA_L1`.`Id`=`LPA_L2`.`Id`) 
WHERE ( ( ( ( ( `LPA_L2`.`Id` IS NOT NULL) AND  EXISTS 
(
  SELECT `sale_order`.`Id` 
  FROM ( `third_party`  
   LEFT JOIN `third_party_customer`  ON  `third_party`.`Id`=`third_party_customer`.`Id`) 
   WHERE ( `sale_order`.`FK_third_party_customer_Id_Invoiced` = `LPA_L2`.`Id`)))))
)

Without OfEntityType<ThirdPartyCustomerEntity>()

SELECT `LPA_L2`.`Id`
FROM ( `third_party` `LPA_L1`
LEFT JOIN `third_party_customer` `LPA_L2`  ON  `LPA_L1`.`Id`=`LPA_L2`.`Id`)
WHERE ( ( ( (  EXISTS 
(
SELECT `sale_order`.`Id` 
FROM ( `third_party`  
LEFT JOIN `third_party_customer`  ON  `third_party`.`Id`=`third_party_customer`.`Id`)
WHERE ( `sale_order`.`FK_third_party_customer_Id_Invoiced` = `LPA_L2`.`Id`)))))
)

In fact, we already had this discussion on one of my previous thread "LEFT JOIN instead of INNER JOIN when table inherits from another one" and you told me to use Cast method with Linq so I found OfEntityType which seems to be the same on QuerySpec.

With more than 100,000 posts on this forum I don't recall all of them, sorry. As described in the sticky posts on all the forums, please provide all the information needed. I had to puzzle together how things are related in your code/entities. If I also have to re-read all the posts of the user, it will take too much time to answer questions . So next time, please provide the info needed.

To come back to my initial matter, if I write the WHERE EXISTS query without using inheritance, the query returns me the expected results

SELECT `third_party`.`Id`
FROM `third_party`
WHERE ( ( ( (
EXISTS
(
  SELECT `sale_order`.`Id`
  FROM `sale_order`
  WHERE ( `sale_order`.`FK_third_party_customer_Id_Invoiced` = `third_party`.`Id`)))))
)

I can reproduce this problem in v4.2, using adventure works:

    var qf = new QueryFactory();
    var sohs = qf.SalesOrderHeader
                    .CorrelatedOver(SalesOrderHeaderFields.SalesPersonId == SalesPersonFields.EmployeeId)
                    .Select(SalesOrderHeaderFields.SalesOrderId);

    var q = qf.SalesPerson
                .Select(() => new { id = SalesPersonFields.EmployeeId.ToValue<int>() })
                .WhereExists(sohs);

    var results = new DataAccessAdapter().FetchQuery(q);

I have no idea why this is, especially because the original from clause is missing from the query passed to WhereExists, and which causes the problem.

Btw, the new {} in the query passed to WhereExists is irrelevant, you can just pass the field, the projection is ignored.

I'll look into why this fails

About the left / inner join issue. That's indeed odd too. When I do:

var q = qf.SalesPerson.Select(SalesPersonFields.EmployeeId);

(salesperson is subtype of employee) It produces

SELECT [LPA_L2].[SalesPersonID] AS [EmployeeId]
FROM   ( [AdventureWorksUnitTests].[HumanResources].[Employee] [LPA_L1]
         LEFT JOIN [AdventureWorksUnitTests].[Sales].[SalesPerson] [LPA_L2]
             ON [LPA_L1].[EmployeeID] = [LPA_L2].[SalesPersonID]) 

Which is of course wrong. When I fetch the full query (qf.SalesPerson) I do get the inner join instead of the left join. Left joins are used for subtypes of the type fetched, inner joins are used for fetching the types from the type fetched to the hierarchy root.

Linq gives the same issue:

var q = metaData.SalesPerson.Select(s => s.EmployeeId);

gives the same query.

So it's an issue in the section in the pipeline where additional inheritance filters are added to the query to fulfill the query requirements. (the issue with the inner/left join. The other issue is separate of that).

The inner/left join is by design in the current codebase: the inheritance relationships added are determined in a generic method which can only decide to use a left join as choosing inner join at that point could lead to 0 rows, e.g. when there are fields in the projection from multiple siblings in the inheritance tree.

This is unfortunate in some situations, however unavoidable at this point, so the type filter is required to append the proper filter to avoid nulls. (making the left join effectively an inner join).

So that's the inner/left join mystery. The other one is the problem with the query crashing. Looking into that now.

Your initial sql query is not correct, imho. Shouldn't it be:

SELECT third_party.Id
FROm third_party_customer c
INNER JOIN third_party ON third_party.Id = third_party_customer.Id
WHERE EXISTS
(
    SELECT sale_order.Id
    FROM sale_order
    WHERE sale_order.FK_third_party_customer_Id_Invoiced= c.Id
)

? (tying the exists query to the outer query). At least I think it is, as you used CorrelatedOver which should do this indeed.

Fact remains: without inheritance it works, with inheritance it doesn't: we seem to have a bug somewhere where the exists query's from clause is replaced by the inheritance related join for the field in the correlatedover predicate. With using aliases this isn't currently solvable.

What I think happens is that it finds the subtype field in the correlation predicate, obtains inheritance information for it and appends that to the query fragment. As there's no join defined between SalesOrder and ThirdPartyCustomer in the subquery, it goes wrong. Specifying that join isn't correct as the nested query is correlated to the outer query.

I.o.w.: the inheritance information shouldn't be added, as it's from a correlation predicate. Looking into that.

It's a bug in our framework, as it doesn't wrap the correlation predicate passed to .CorrelatedOver() into CorrelationPredicateExpression in some cases (only in nested query cases, not in the case of an exists query).

To fix this, simply do:

qf.SaleOrder
        .CorrelatedOver(new CorrelationPredicateExpression(SaleOrderFields.FkThirdPartyCustomerIdInvoiced == ThirdPartyCustomerFields.Id))
        .Select(() => new
        {
            Id = SaleOrderFields.Id.ToValue<long>()
        });

In v4.2 we've added the fix to automatically do this behind the scenes. Please let me know whether this solves your problem with v3.5.

Where can I find the CorrelationPredicateExpression class ?

Sorry for this, I was investigating the problem on v4 and I now realize the required class/feature isn't present in v3. This class was introduced in v4 to avoid the problem that correlation predicates were seen as normal predicates. It's not present in v3. The main issue is that the functionality to fix this isn't present in v3, and back porting it isn't easy, as it relies on classes in ORM support classes dll (the name finder visitor has to skip correlation predicates when asked) and queryspec dll (the queryspec / nested query spec has to specify that a predicate is a correlation predicate) so if backported, the queryspec dll relies on a change in ormsupportclasses. This means introducing a breaking change (hence we postponed it to v4 to introduce it) and we want to avoid introducing that, even though v3 isn't updated that much anymore anyway (it's more than 3 years old).

I do realize this is a problem for you though, and the required query you want to write isn't working at the moment. As this isn't the full query, is there a different way you can write the query so this problem is avoided? (yes I know it's less ideal, but a breaking change isn't either)

I have been able to change my query using a LEFT JOIN and a DISTINCT clause (to remove the doublons).

So no matter, thanks for your help.