- Home
- LLBLGen Pro
- Bugs & Issues
Inner query OR being converted to AND
Joined: 24-Aug-2018
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)
Joined: 21-Aug-2005
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();
}
Joined: 17-Aug-2003
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 ?
Joined: 24-Aug-2018
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.
Joined: 17-Aug-2003
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.
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).