Count in subquery

[jimfoye]

How do I do a subquery using count? All the examples I've seen only use count in its own query.

For example, if I want expense reports and the count of items for each report, I can do it in a single SQL statement like this:

SELECT ExpenseReports.ExpenseReportID,
       ExpenseReports.Submitted,
       -- (etc.)
       (SELECT count(*) from ExpenseReportLineItems where ExpenseReportLineItems.ExpenseReportID = ExpenseReports.ExpenseReportID) as LineItemCount
FROM ExpenseReports 

I can get this to compile:

query {
    for entity in dbctx.Dbo.ExpenseReports do
    for employee in (!!) entity.``dbo.Employees by EmployeeID_2`` do
    for approver in (!!) employee.``dbo.Users by UserID`` do
    for status in entity.``dbo.ExpenseReportStatus by ExpenseReportStatusID`` do
    let itemCount = 
        query {
            for item in entity.``dbo.ExpenseReportLineItems by ExpenseReportID`` do
            select item.ExpenseReportId
            count 
        }
    where (entity.EmployeeId = targetUser.UserId && entity.Submitted.Year = year)
    select (entity, approver, status, itemCount)
}

But I get an exception:

System.ArgumentException: Method 'FSharp.Data.Sql.Common.SqlEntity GetSubTable(System.String, System.String)' declared on type 'FSharp.Data.Sql.Common.SqlEntity' cannot be called with instance of type 'Microsoft.FSharp.Linq.RuntimeHelpers.AnonymousObject`5[FSharp.Data.Sql.Common.SqlEntity,FSharp.Data.Sql.Common.SqlEntity,FSharp.Data.Sql.Common.SqlEntity,FSharp.Data.Sql.Common.SqlEntity,System.Int32]'

[Thorium]

We could add support for sub-queries in select, but the problem is that ExpenseReports.ExpenseReportID is a parameter for the sub-query (nested LINQ lambda) but not a parameter to the main query, so how to identify that.

As a workaround I would go for SQL view and query the view with SQLProvider.

[jimfoye]

OK, thanks.