Have the following two linq queries

            var testKioskId = "Test";
            var baseResult = (from k in mData.TKiosk
               where k.KioskId == testKioskId
               select new KioskInfoDto
               {
                  //KioskId = k.KioskId,
                  //IsActive = k.Active,
                  //KioskType = (ArrivalConstants.KioskType) k.Type,
                  //SabDeptId = k.SabDeptId,
                  //AutoClearTimer = k.AutoClearTimer,
                  //WelcomeMessage = k.WelcomeText,
                  //InvalidAppointmentMessage = k.InvalidAppText,
                  //AllowedSabDepartments = k.TKioskAllowedDepartment.Select(d => d.SabDeptId).ToList(),
                  //AllowedAppointmentContacts = k.TKioskAllowedContact.Select(c => c.ContactId).ToList(),
                  KioskLinkedBillingServices = (from kb in mData.TKioskService
                     where k.KioskId == kb.KioskId || kb.KioskId == null
                     select new KioskLinkedServiceDto
                     {
                        ServiceInt = kb.ServiceInt,
                        BillingUnits = kb.Units
                     }).ToList()
                  //ContactLinkedBillingServices = (from cb in mData.TKioskContactService where (cb.KioskId == null || cb.KioskId == k.KioskId) && cb.Active
                  //   select new ContactLinkedServiceDto
                  //   {
                  //      ContactInt = cb.ContactInt,
                  //      ServiceInt = cb.ServiceInt,
                  //      BillingUnits = cb.Units
                  //   }).Distinct().ToList(),
               }).FirstOrDefault();

SQL output:

Executed Sql Query: 
    Query: SELECT 1 AS "LPFA_2", "LPLA_1"."KIOSK_ID" AS "KioskId" FROM "SAGA"."T_KIOSKS" "LPLA_1" WHERE ( ( ( ( ( ( "LPLA_1"."KIOSK_ID" = :p1)))))) AND rownum <=  1
    Parameter: :p1 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Test".

Executed Sql Query: 
    Query: SELECT "LPLA_2"."SERVICE_INT" AS "ServiceInt", "LPLA_2"."UNITS" AS "BillingUnits", "LPLA_2"."KIOSK_ID" AS "KioskId" FROM "SAGA"."T_KIOSK_SERVICES" "LPLA_2" WHERE ( ( ( ( ( ( "LPLA_2"."KIOSK_ID" IS NULL)))) AND ( "LPLA_2"."KIOSK_ID" = :p1)))
    Parameter: :p1 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Test".

(cb.KioskId == null || cb.KioskId == k.KioskId) statement gets converted to AND sql statement and KioskLinkedBillingServices is always an empty list.

Working non-inner query:

           var kioskLinkedBillingServicesResult = (from kb in mData.TKioskService
               where (kb.KioskId == null || kb.KioskId == testKioskId) && kb.Active
               select new KioskLinkedServiceDto
               {
                  ServiceInt = kb.ServiceInt,
                  BillingUnits = kb.Units
               }).ToList();

SQL output:

Executed Sql Query: 
    Query: SELECT "LPLA_1"."SERVICE_INT" AS "ServiceInt", "LPLA_1"."UNITS" AS "BillingUnits" FROM "SAGA"."T_KIOSK_SERVICES" "LPLA_1" WHERE ( ( ( ( ( ( ( "LPLA_1"."KIOSK_ID" IS NULL) OR ( "LPLA_1"."KIOSK_ID" = :p1)) AND ( "LPLA_1"."ACTIVE" = :p2))))))
    Parameter: :p1 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Test".
    Parameter: :p2 : Decimal. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Which gives expected results.

Setup info:

We have 2 tables T_KIOSKS and T_KIOSK_SERVICES with 1:n relation. The KIOSK_ID column in both tables is VARCHAR2(50) with a foreign key relation. KIOSK_ID is nullable in T_KIOSK_SERVICES

Designer version: 5.4 (5.4.2) RTM Build Date: 18-júl.-2018 SD.LLBLGen.Pro.ORMSupportClasses 5.4.0.0 Oracle.ManagedDataAccess 4.121.2.0 Oracle 10g (10.2.0.4.0)

Reproduced, using latest v.5.4.3

using (var adapter = new DataAccessAdapter())
{
    var mData = new LinqMetaData(adapter);
    var testCustomerId = "ALFKI";

    var baseResult = (from c in mData.Customer
                      where c.CustomerId == testCustomerId
                      select new 
                      {
                          customerOrders = (from o in mData.Order
                                                        where (o.CustomerId == null || o.CustomerId == c.CustomerId) && o.EmployeeId == 1
                                                        select new
                                                        {
                                                            OrderId = o.OrderId,
                                                            OrderDate = o.OrderDate
                                                        }).ToList()
                      }).FirstOrDefault();
}

We'll look into it.

I hate the sentence, but ... this is by design. I'll try to explain it

The main issue is the 'kb.KioskId == null' predicate. Nested queries are required to be related to the parent query. However a row where kb.KioskId == null is true isn't related to any row in the parent. This means that all TKioskService rows which are not related to any TKiosk are fetched as well and stored with the row read from TKiosk.

The query Walaa used as reprocase has effectively the same thing: any order which isn't assigned to a customer will be fetched as well, but those rows aren't related to any parent row.

What the linq provider does is to try to find a correlation predicate (which ties both queries together), and 'and' the rest (which can contain or's and which are left alone, which I'll show below). The correlation predicate is needed to merge both resultsets (as the nested query is fetched separately, and rows are merged like prefetch paths at the spot of the ' 1' in the parent rows).

It's shown best if you define the predicate implicitly by a navigation:

var results = (from c in mData.Customer
               where c.CustomerId == testCustomerId
               select new
                      {
                          customerOrders = (from o in c.Orders
                                            where (o.CustomerId == null) || o.EmployeeId == 1
                                            select new
                                                   {
                                                       OrderId = o.OrderId,
                                                       OrderDate = o.OrderDate
                                                   }).ToList()
                      }).ToList();

Here, the nested query is tied to the parent by the navigation over c.Orders. This results in a correlation predicate, c.CustomerId==o.CustomerId. The predicates in the query itself are AND-ed to that, as otherwise the correlation predicate is useless.

This results in a nested query:

SELECT [LPLA_2].[OrderID]   AS [OrderId],
       [LPLA_2].[OrderDate],
       [LPLA_2].[CustomerID] AS [CustomerId]
FROM   [Northwind].[dbo].[Orders] [LPLA_2]
WHERE  (((((([LPLA_2].[CustomerID] IS NULL)
             OR ([LPLA_2].[EmployeeID] = @p1))))
     AND ([LPLA_2].[CustomerID] = @p2))) 

Where you see the query's own predicate is left as-is but is anded with another predicate which is used to filter the set based on the parent's set (which is just 1 row, hence the single value predicate).

In your case, you fetch unrelated rows from TKioskService as related rows into the parent' set from TKiosk, through the '== null' predicate.

Is there a reason why you explicitly want to fetch unrelated rows from TKioskService into the set fetched from TKiosk ?

TKioskService rows with kioskId row as null apply to all TKiosk rows Basically, a global config value.

You can normally trust the logic in linq query to translate directly to sql logic with LLBLGen. I can understand the performance implications of what I’m doing but if you don’t intend to support this logic in a subquery wouldn’t it be more intuitive to simply have the query throw an exception like when you have no correlation logic between parent and subquery? Either way, the workaround for us is simple and the performance issues minimal since this is cached config data.

But this behavior struck me like a bug and that’s why I reported it.

Gilbert64 wrote:

TKioskService rows with kioskId row as null apply to all TKiosk rows Basically, a global config value.

Ok, but you can do that differently Fetch that global object once to a value in memory, then simply assign it in the projection as a value. No need for the separate query.

In general though, the situation where you have rows without the FK set can be a large set, and semantically they don't belong in that resultset, as they're no related to the parent query. That's the concept of the nested queries: as stated in the docs:

The nested query has to have a connection with the outer query, otherwise Linq to LLBLGen Pro can't find a proper parent element for a child element or set of child elements. In the query above, a where clause is used to tie the nested query with the outer query.

https://www.llblgen.com/Documentation/5.4/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_generalusage.htm#hierarchical-sets

You can normally trust the logic in linq query to translate directly to sql logic with LLBLGen. I can understand the performance implications of what I’m doing but if you don’t intend to support this logic in a subquery wouldn’t it be more intuitive to simply have the query throw an exception like when you have no correlation logic between parent and subquery? Either way, the workaround for us is simple and the performance issues minimal since this is cached config data. But this behavior struck me like a bug and that’s why I reported it.

It's a subtle thing, and I first didn't see it either. I agree an exception would perhaps be better. We'll look into changing this for a future release (it will be a breaking change for the people who have queries with nested queries without a predicate).