Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Different SQL for Count() vs Count
 

Pages: 1
LLBLGen Pro Runtime Framework
Different SQL for Count() vs Count
Page:1/1 

  Print all messages in this thread  
Poster Message
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 20-May-2016 11:44:14.  
This is a reproduction using Northwind. This
Code:
from c in Customer
select new
{
    c.ContactName,
    c.CustomerId,
    OrdersCount = c.Orders.Count()
}
produces
Code:
SELECT [LPLA_1].[ContactName],
[LPLA_1].[CustomerID] AS [CustomerId],
(SELECT COUNT(*) AS [LPAV_]
FROM
[dbo].[Orders] [LPLA_2]
WHERE
( ( [LPLA_1].[CustomerID] = [LPLA_2].[CustomerID]))) AS [OrdersCount]
FROM
[dbo].[Customers] [LPLA_1]
whereas this
Code:
from c in Customer
select new
{
    c.ContactName,
    c.CustomerId,
    OrdersCount = c.Orders.Count
}
produces
Code:
SELECT [LPLA_1].[ContactName],
[LPLA_1].[CustomerID] AS [CustomerId],
(SELECT COUNT([LPA_L2].[CustomerId]) AS [LPAV_]
FROM
(SELECT [LPLA_2].[CustomerID] AS [CustomerId],
[LPLA_2].[EmployeeID] AS [EmployeeId],
[LPLA_2].[Freight],
[LPLA_2].[OrderDate],
[LPLA_2].[OrderID] AS [OrderId],
[LPLA_2].[RequiredDate],
[LPLA_2].[ShipAddress],
[LPLA_2].[ShipCity],
[LPLA_2].[ShipCountry],
[LPLA_2].[ShipName],
[LPLA_2].[ShippedDate],
[LPLA_2].[ShipPostalCode],
[LPLA_2].[ShipRegion],
[LPLA_2].[ShipVia]
FROM
[dbo].[Orders] [LPLA_2]
WHERE
( ( [LPLA_1].[CustomerID] = [LPLA_2].[CustomerID]))) [LPA_L2]) AS [OrdersCount]
FROM
[dbo].[Customers] [LPLA_1]
Both queries produce the same result set in SQL Server but the second one blows up against Oracle (I think, I don't have an Oracle Northwind).

This is the SQL the does blow up against Oracle in our real code.
Code:
SELECT "LPLA_1"."WR_NUMBER" AS "WRNumber",
"LPLA_1"."WR_PERIOD" AS "WRPeriod",
"LPLA_1"."WR_SORT_KEY" AS "WRSortKey",
"LPLA_1"."WR_TYPE" AS "WRType",
(SELECT COUNT("LPA_L2"."AircraftID") AS "LPAV_"
FROM
(SELECT "LPLA_2"."AIRCRAFT_ID" AS "AircraftID",
"LPLA_2"."ASSIGNED_TO" AS "AssignedTo",
"LPLA_2"."ASSIGNED_TO_ID" AS "AssignedToID",
"LPLA_2"."CHECK_LIST_CODE" AS "CheckListCode",
"LPLA_2"."CHECKLIST_ITEM_ID" AS "ChecklistItemID",
"LPLA_2"."DATE_DISCOVERED" AS "DateDiscovered",
"LPLA_2"."DEFAULT_ACTION_TRACKER_ID" AS "DefaultActionTrackerID",
"LPLA_2"."DEFAULT_ACTION_TRACKER_MODE" AS "DefaultActionTrackerMode",
"LPLA_2"."ORG_ITEM_ID" AS "DepartmentID",
"LPLA_2"."DOCUMENT_ID" AS "DocumentID",
"LPLA_2"."DRAFT" AS "Draft",
"LPLA_2"."ENTERED_ON" AS "EnteredOn",
"LPLA_2"."ERROR_HEADER" AS "ErrorHeader",
"LPLA_2"."ERROR_ITEM_ID" AS "ErrorItemID",
"LPLA_2"."EVIDENCE" AS "Evidence",
"LPLA_2"."EXT_AUDIT_REFERENCE" AS "ExtAuditReference",
"LPLA_2"."EXT_FINDING_REFERENCE" AS "ExtFindingReference",
"LPLA_2"."FINDING" AS "Finding",
"LPLA_2"."FINDING_CATEGORY_CODE" AS "FindingCategoryCode",
"LPLA_2"."FINDING_NO" AS "FindingNo",
"LPLA_2"."FINDING_NO_SORTABLE" AS "FindingNoSortable",
"LPLA_2"."FINDING_SOURCE_ID" AS "FindingSourceID",
"LPLA_2"."FINDING_TITLE" AS "FindingTitle",
"LPLA_2"."IMMEDIATE_ACTION_TEXT" AS "ImmediateActionText",
"LPLA_2"."ISDELETED" AS "IsDeleted",
"LPLA_2"."LAST_ACTIVITY_TEXT" AS "LastActivityText",
"LPLA_2"."LEGISLATION_ID" AS "LegislationID",
"LPLA_2"."LIKELIHOOD" AS "LikelihoodCode",
"LPLA_2"."LIKELIHOOD_ID" AS "LikelihoodID",
"LPLA_2"."MANUAL_REFERENCE" AS "ManualReference",
"LPLA_2"."ORIGINAL_DUE_DATE" AS "OriginalDueDate",
"LPLA_2"."CAA_CLIENT_ID" AS "PartyID",
"LPLA_2"."REGISTERED_BY" AS "RegisteredBy",
"LPLA_2"."REGISTERED_BY_ID" AS "RegisteredByID",
"LPLA_2"."REPEAT_FINDING" AS "RepeatFinding",
"LPLA_2"."RESPONSE_DUE_DATE" AS "ResponseDueDate",
"LPLA_2"."RESPONSE_STATUS" AS "ResponseStatus",
"LPLA_2"."RISK_REVIEW_CONSIDERED" AS "RiskReviewConsidered",
"LPLA_2"."RISK_SOURCE" AS "RiskSource",
"LPLA_2"."RULE_REFERENCE" AS "RuleReference",
"LPLA_2"."FINDING_TYPE" AS "SeverityCode",
"LPLA_2"."SEVERITY_ID" AS "SeverityID",
"LPLA_2"."STANDARD" AS "Standard",
"LPLA_2"."STANDARD_CATEGORY_ID" AS "StandardCategoryID",
"LPLA_2"."STANDARD_ITEM_ID" AS "StandardItemID",
"LPLA_2"."WR_NUMBER" AS "WRNumber",
"LPLA_2"."WR_PERIOD" AS "WRPeriod",
"LPLA_2"."WR_TYPE" AS "WRType"
FROM
"AQD"."QAOC_FINDING" "LPLA_2"
WHERE
( ( "LPLA_1"."WR_NUMBER" = "LPLA_2"."WR_NUMBER" AND "LPLA_1"."WR_PERIOD" = "LPLA_2"."WR_PERIOD" AND "LPLA_1"."WR_TYPE" = "LPLA_2"."WR_TYPE"))) "LPA_L2") AS "FindingsCount"
FROM
"AQD"."QAOC_WORK_REQUEST_VIEW" "LPLA_1"
ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ORA-00904: "LPLA_1"."WR_TYPE": invalid identifier.

Is there any general advice for Count() vs Count in this scenario?

LLBL Version 4.2.16.0414
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 20-May-2016 15:11:17.  
Inside linq, always use Count(), it's the safest. .Count as property is only useful on materialized collections, and inside queries that's never the case unless you first did a .ToList() Wink

.Count is discovered later on in the expression tree handling than Count(). Count() is an aggregate call so the tree handler handles it as an aggregate. Count as property is simply a property read, and can only be discovered much later as the expression tree looks different. This leads to the wrapping and a select of the first field of the nested query to count on. Why it blows up on oracle is unclear to me, it might be due to the amount of nested queries, which it isn't very fond of (like with the paging queries).

IIRC at the beginning we gave an exception when .Count was used where .Count() was meant, but found a way to fix this at runtime with a fall back query which you see in the 2nd sql query.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 23-May-2016 03:21:20.  
Otis wrote:
Why it blows up on oracle is unclear to me, it might be due to the amount of nested queries, which it isn't very fond of (like with the paging queries).
No doubt blow up is because of the problem when a correlated subquery references a column from a table referred to in a non-immediate parent query, as described: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=19892 and
https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16159
Simpler example using our DB
Code:
SELECT "LPLA_1".*,
(SELECT COUNT(*) FROM "AQD"."QAOC_ACTION" "LPLA_2" WHERE "LPLA_1"."FINDING_NO" = "LPLA_2"."FINDING_NO") AS "ActionsCount"
FROM
"AQD"."QAOC_FINDING" "LPLA_1""
works but
Code:
SELECT "LPLA_1".*,
(SELECT COUNT("LPA_L2"."Action") FROM (SELECT "LPLA_2".* FROM "AQD"."QAOC_ACTION" "LPLA_2" WHERE "LPLA_1"."FINDING_NO" = "LPLA_2"."FINDING_NO") "LPA_L2") AS "ActionsCount"
FROM
"AQD"."QAOC_FINDING" "LPLA_1""
doesn't.
Could the Oracle (and MySQL i think) DQE detect this situation and throw a more meaning full error rather than sending it on to the DB?

Otis wrote:
IIRC at the beginning we gave an exception when .Count was used where .Count() was meant, but found a way to fix this at runtime with a fall back query which you see in the 2nd sql query.
Selfservicing still gives this with the Northwind query
Code:
Expression of type 'SD.LLBLGen.Pro.Examples.Auditing.CollectionClasses.OrderCollection' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable`1[SD.LLBLGen.Pro.Examples.Auditing.CollectionClasses.OrderCollection]' of method 'Int32 Count[OrderCollection](System.Collections.Generic.IEnumerable`1[SD.LLBLGen.Pro.Examples.Auditing.CollectionClasses.OrderCollection])'.
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 23-May-2016 14:21:46.  
TomDog wrote:
Otis wrote:
Why it blows up on oracle is unclear to me, it might be due to the amount of nested queries, which it isn't very fond of (like with the paging queries).
No doubt blow up is because of the problem when a correlated subquery references a column from a table referred to in a non-immediate parent query, as described: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=19892 and
https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16159
Simpler example using our DB
Code:
SELECT "LPLA_1".*,
(SELECT COUNT(*) FROM "AQD"."QAOC_ACTION" "LPLA_2" WHERE "LPLA_1"."FINDING_NO" = "LPLA_2"."FINDING_NO") AS "ActionsCount"
FROM
"AQD"."QAOC_FINDING" "LPLA_1""
works but
Code:
SELECT "LPLA_1".*,
(SELECT COUNT("LPA_L2"."Action") FROM (SELECT "LPLA_2".* FROM "AQD"."QAOC_ACTION" "LPLA_2" WHERE "LPLA_1"."FINDING_NO" = "LPLA_2"."FINDING_NO") "LPA_L2") AS "ActionsCount"
FROM
"AQD"."QAOC_FINDING" "LPLA_1""
doesn't.
Could the Oracle (and MySQL i think) DQE detect this situation and throw a more meaning full error rather than sending it on to the DB?

No unfortunately not, as the rules are unknown to me when what fails, plus it would require to parse the sql query and detect scopes which is very complicated as parsing sql isn't simple. Things are generated in fragments so a nested query doesn't know how deep it will be embedded into an outer query when sql is generated and the outer query doesn't know how many nested scopes it is embedding and whether that will cause a problem.

Quote:

Otis wrote:
IIRC at the beginning we gave an exception when .Count was used where .Count() was meant, but found a way to fix this at runtime with a fall back query which you see in the 2nd sql query.
Selfservicing still gives this with the Northwind query
Code:
Expression of type 'SD.LLBLGen.Pro.Examples.Auditing.CollectionClasses.OrderCollection' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable`1[SD.LLBLGen.Pro.Examples.Auditing.CollectionClasses.OrderCollection]' of method 'Int32 Count[OrderCollection](System.Collections.Generic.IEnumerable`1[SD.LLBLGen.Pro.Examples.Auditing.CollectionClasses.OrderCollection])'.


True, I can reproduce that. But I won't fix that: it is because selfservicing uses non-generic collections and without instantiating them we can't determine what the entity type is so can't create an IQueryable<EntityType>.Count() call expression.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 23-May-2016 14:33:13.  
On second thought, LinqUtils.DetermineEntityTypeFromEntityCollectionType can help here. We'll fix the selfservicing count issue in v5
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 23-May-2016 14:41:40.  
Selfservicing count issue: Fixed in v5.0.3 hotfix

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.