LLBLGen 4.1 Runtime: 4.1.13.1114 Adapter Template, .NET 4.5 SQL Server

I'm trying to create a query using QuerySpec that will result in this SQL:

SELECT [Supporter].[SupporterId]
FROM dbo.Supporter
LEFT JOIN (
    Bidder 
        INNER JOIN dbo.AffiliateType 
        ON dbo.AffiliateType.AffiliateTypeId = dbo.Bidder.AffiliateTypeId
) ON Supporter.SupporterId = Bidder.BidderID
WHERE Supporter.SupporterId = 0

Note the "nested" or "grouped" join syntax where Bidder is INNER JOINed with AffiliateType, and then that result is LEFT JOINed with Supporter.

I tried creating this query like this:

var qf = new QueryFactory();
var q = qf.Create()
            .From(
                qf.Supporter.LeftJoin(
                    qf.Bidder
                    .InnerJoin(qf.AffiliateType)
                    .On(AffiliateTypeFields.AffiliateTypeId == BidderFields.AffiliateTypeId)
                )
                .On(SupporterFields.ClientId == ClientFields.ClientId)
            )
            .Where(SupporterFields.SupporterId == 0)
            .Select(
                 SupporterFields.SupporterId);

It compiles fine, but it doesn't generate valid SQL:

SELECT [AESOP_ES_DEV].[dbo].[Supporter].[SupporterId]
FROM   ( [AESOP_ES_DEV].[dbo].[Supporter]
         LEFT JOIN [AESOP_ES_DEV].[dbo].[AffiliateType]
             ON [AESOP_ES_DEV].[dbo].[Supporter].[ClientId] = [AESOP_ES_DEV].[dbo].[Client].[ClientId])
WHERE  (([AESOP_ES_DEV].[dbo].[Supporter].[SupporterId] = 0 /* @p1 */))

The SQL error is:

Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "AESOP_ES_DEV.dbo.Client.ClientId" could not be bound.

Note that the Client table wasn't included in the JOINs.

Is this supposed to be able to work?

I know that there are a couple of different ways of writing this query without using the nested join syntax, but in my actual query, this syntax leads the SQL optimizer to choose the best query plan, so I'd like to generate the nested join if possible.

Thanks,

I think the nested join needs an alias. Could you try with specifying an alias on the nested join (which is actually a subquery) or a .Select(Projection.Full).

I'll try to see whether I can reproduce it and solve it.

(same query, as you said, can be rewritten as bidder INNER JOIN AffilliateType RIGHT JOIN suppporter btw)

'Client' is also not in the join list: In the join are Supporter, Bidder and AffiliateType. Not client. Query still crashes if that's corrected though, just a FYI

[Test]
public void LeftJoinWithBareInnerJoinTest()
{
    var qf = new QueryFactory();
    var q = qf.Create()
                .From(
                    qf.Customer.LeftJoin(
                        qf.Order
                        .InnerJoin(qf.OrderDetail)
                        .On(OrderDetailFields.OrderId == OrderFields.OrderId)
                    )
                    .On(CustomerFields.CustomerId== OrderFields.CustomerId)
                )
                .Where(CustomerFields.Country=="France")
                .Select(CustomerFields.CustomerId);

    var results = new DataAccessAdapter().FetchQuery(q);
    Assert.AreEqual(11, results.Count);
}

which I think represents it (corrected the where clause). This fails with the same error, the inner join is missing, likely because it doesn't expect a join to join with.

Using a projection in the inner query fixes it, it makes the code work as it now works with elements it understands:

[Test]
public void LeftJoinWithBareInnerJoinTest()
{
    var qf = new QueryFactory();
    var q = qf.Create()
                .From(
                    qf.Customer.LeftJoin(
                        qf.Order
                            .From(QueryTarget.InnerJoin(qf.OrderDetail)
                                .On(OrderDetailFields.OrderId == OrderFields.OrderId))
                            .Select(OrderFields.CustomerId)
                            .As("O")
                    )
                    .On(CustomerFields.CustomerId== OrderFields.CustomerId.Source("O"))
                )
                .Where(CustomerFields.Country=="France")
                .Select(CustomerFields.CustomerId)
                .Distinct();

    var results = new DataAccessAdapter().FetchQuery(q);
    Assert.AreEqual(11, results.Count);
}

SQL:

SELECT DISTINCT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
FROM   ( (SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId]
        FROM   ( [Northwind].[dbo].[Orders]
                 INNER JOIN [Northwind].[dbo].[Order Details]
                     ON [Northwind].[dbo].[Order Details].[OrderID] = [Northwind].[dbo].[Orders].[OrderID])) [LPA_O1]
         RIGHT JOIN [Northwind].[dbo].[Customers]
             ON [Northwind].[dbo].[Customers].[CustomerID] = [LPA_O1].[CustomerId])
WHERE  (([Northwind].[dbo].[Customers].[Country] = 'France' /* @p1 */))

Execution plan is equal to :

select distinct c.*
from customers c left join (orders o inner join [order details] od on o.OrderID = od.OrderID) on c.CustomerId = o.CustomerID
where c.Country='France'