This is a reproduction using Northwind. This

from c in Customer
select new
{
    c.ContactName,
    c.CustomerId,
    OrdersCount = c.Orders.Count()
}

produces

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

from c in Customer
select new
{
    c.ContactName,
    c.CustomerId,
    OrdersCount = c.Orders.Count
}

produces

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.

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

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()

.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.

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

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

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

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])'.

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

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

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.

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

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.

On second thought, LinqUtils.DetermineEntityTypeFromEntityCollectionType can help here. We'll fix the selfservicing count issue in v5

Selfservicing count issue: Fixed in v5.0.3 hotfix