1

I'm trying to summarize an SQL table with multiple fields with F# query expressions. The summary hinges on the table being successfully joined with another table and filtered by a date. I can do the joining and filtering without issue but I'm challenged by adding some summary values to the query:

let summarySalesQuery = 
    query {
        for header in db.SalesHeader do
        where (header.ORDERDATE.HasValue && header.ORDERDATE.Value > (System.DateTime.Parse "2014-01-01"))
        join row in db.SalesDetail
            on ((header.ORDERNUMBER).ToString() = row.ORDERNUMBER)
        let totQty = sumByNullable row.QUANTITY
        let totOrderCo = row.ORIGINALORDERCOMPANY.Count()
        select (totQty, totOrderCo)
}

This query fails because of an error with the sumByNullable call:

error FS3095: 'sumByNullable' is not used correctly. This is a custom operation in this query or computation expression.

But, if I use this query instead where sumByNullable is used on its own:

let summarySalesQuery = 
    query {
        for header in db.SalesHeader do
        where (header.ORDERDATE.HasValue && header.ORDERDATE.Value > (System.DateTime.Parse "2014-01-01"))
        join row in db.SalesDetail
            on ((header.ORDERNUMBER).ToString() = row.ORDERNUMBER)
        sumByNullable row.QUANTITY
        }

a single value is returned, presumably summing all of the values in row.QUANTITY:

val summarySalesQuery : Nullable<decimal> = 2333336M

Trying to construct a query using the answers in this question

let summarySalesQuery = 
    query {
        for header in db.SalesHeader do
        where (header.ORDERDATE.HasValue && header.ORDERDATE.Value > (System.DateTime.Parse "2014-01-01"))
        join row in db.SalesDetail
            on ((header.ORDERNUMBER).ToString() = row.ORDERNUMBER)
        groupBy row.ORDERNUMBER into group
        let totQty = query { for g in group do sumByNullable g.QUANTITY }
        select (group.Key, totQty)
        }

produces the error

error FS0039: The field, constructor or member 'QUANTITY' is not defined

which I can't decipher because QUANTITY is clearly available, given the return value from the previous query.

I want to construct a query that can return multiple summarized values from a table in a dB. How do I construct a query that joins, filters, and summarizes multiple fields on a database table and returns a tuple--size to be decided--of the summarized values?

Note: I have no control over the types of the fields in the database, thus the .ToString() and parsing required in the queries are required.

Community
  • 1
  • 1
Steven
  • 3,238
  • 21
  • 50
  • 1
    Would you mind do the same in SQL? And is that achievable? To me it seems like youre trying to do a sum and group by. If you manage to do that in SQL then the query expression more or less writes it self. – Helge Rene Urholm Sep 26 '16 at 16:13
  • Can you also provide some more details? DB type and version, type provider version. Does this work when you use linq to objects? Also, usually you don't need to cast to string, tuples and records can hold different types but maybe you do have a reason. – s952163 Sep 27 '16 at 22:36

0 Answers0