F# Query Exception, "unrecognized method call value"

Question

Q: What does the following exception mean?

System.Exception: 'unrecognised method call value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable`1[FSharp.Data.Sql.Common.SqlEntity]).GroupJoin(value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable`1[FSharp.Data.Sql.Common.SqlEntity]), arClientRow => arClientRow.GetColumn("CLIENT_ID"), dsItemRow => dsItemRow.GetColumn("CLIENT_ID"), (arClientRow, dsItemGroup) => new AnonymousObject`2(Item1 = arClientRow, Item2 = dsItemGroup.DefaultIfEmpty()))'

I'm attempting to translate a Pervasive SQL query to an F# query using SqlDataProvider with an ODBC connection, building up slowly bit by bit. I'm getting an exception with the following query with a somewhat cryptic exception.

let Q2KQuery = query {
    for arClientRow in Q2KDb.Dbo.ArClient do
    leftOuterJoin dsItemRow in Q2KDb.Dbo.DsItem 
        on (arClientRow.ClientId = dsItemRow.ClientId) 
        into dsItemGroup
    select (arClientRow, dsItemGroup) (*select statement simplified here for demonstration purposes*)
}

printfn "%i" (Seq.length Q2KQuery)

When the printfn statement is executed and the expression is actually evaluated, the exception is hit:

System.Exception: 'unrecognised method call value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable`1[FSharp.Data.Sql.Common.SqlEntity]).GroupJoin(value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable`1[FSharp.Data.Sql.Common.SqlEntity]), arClientRow => arClientRow.GetColumn("CLIENT_ID"), dsItemRow => dsItemRow.GetColumn("CLIENT_ID"), (arClientRow, dsItemGroup) => new AnonymousObject`2(Item1 = arClientRow, Item2 = dsItemGroup.DefaultIfEmpty()))'

This is all part of a larger query which has the exact same exception when it is executed:

let Q2KQuery = query {
    for arClientRow in Q2KDb.Dbo.ArClient do
    leftOuterJoin dsItemRow in Q2KDb.Dbo.DsItem 
        on (arClientRow.ClientId = dsItemRow.ClientId) 
        into dsItemGroup
    for dsItemRow in dsItemGroup do
    leftOuterJoin dsWhseInvHeaderRow in Q2KDb.Dbo.DsWhseInvHeader
        on ((dsItemRow.ClientId, dsItemRow.ItemId) = (dsWhseInvHeaderRow.ClientId, dsWhseInvHeaderRow.ItemId))
        into dsWhseInvHeaderGroup
    for dsWhseInvHeaderRow in dsWhseInvHeaderGroup do
    where (
        dsItemRow.ObsoleteFlg <> "Y"
        && arClientRow.IsInactive <> "Y"
        && dsWhseInvHeaderRow.WhseId = "B1"
        && dsItemRow.InvunitQty = 1
    )
    select (arClientRow, dsItemRow, dsWhseInvHeaderRow) (*select statement simplified here for demonstration purposes*)
}

FYI, the SQL query from which this F# query is translated is as follows (the SELECT statement is not as important as the JOIN clauses where the F# exception is thrown):

SELECT
-- LOTS of selects here... --
-- ... --
----- the important part of the F# queries begin here -----
FROM 
    ArClient
    LEFT JOIN DsItem 
    ON ArClient.CLIENT_ID = DsItem.CLIENT_ID
    LEFT JOIN DsWhse_Inv_Header 
    ON (DsItem.CLIENT_ID = DsWhse_Inv_Header.CLIENT_ID)
    AND (DsItem.ITEM_ID = DsWhse_Inv_Header.ITEM_ID)
WHERE (
    ((DsItem.OBSOLETE_FLG) <> 'Y')
    AND ((ArClient.IS_INACTIVE) <> 'Y')
    AND ((DsWhse_Inv_Header.WHSE_ID) = 'B1')
    AND ((DsItem.INVUNIT_QTY) = 1)
    )
ORDER BY
    CONCAT(LEFT(DsItem.CLIENT_ID, 4), RTRIM(LTRIM(DsItem.ITEM_ID)));

score 2 · Accepted Answer · answered Nov 01 '20 at 20:03

After reading some SO answers and GitHub issues (linked below), it seems that the exact operation on this kind of SQL database in the way I am doing it just isn't supported at the moment (maybe? at least as I understand what I read), although apparently left joins in query expressions are possible with the !! operator -- but I have not had success with it as of yet.

I have however found the following clunky workaround using the same query expression I had been using by forcing the data to be read into memory by transforming the data into a List (or Array, etc) first:

let ArClient = Seq.toList <| query {
    for row in Q2KDb.Dbo.ArClient do
    where (row.IsInactive <> "Y")
    select row
}

let DsItem = Seq.toList <| query {
    for row in Q2KDb.Dbo.DsItem do
    where (row.ObsoleteFlg <> "Y" && row.InvunitQty = 1)
    select row
}

let DsWhseInvHeader = Seq.toList <| query {
    for row in Q2KDb.Dbo.DsWhseInvHeader do
    where (row.WhseId = "B1")
    select row
}

Which can then be used in the original query expression with minimal change.

let results = Seq.toList <| query {
    for arClientRow in ArClient do
    leftOuterJoin dsItemRow in DsItem
        on (arClientRow.ClientId = dsItemRow.ClientId) 
        into dsItemGroup
    for dsItemRow in dsItemGroup do
    leftOuterJoin dsWhseInvHeaderRow in DsWhseInvHeader
        on ((dsItemRow.ClientId, dsItemRow.ItemId) = (dsWhseInvHeaderRow.ClientId, dsWhseInvHeaderRow.ItemId))
        into dsWhseInvHeaderGroup
    for dsWhseInvHeaderRow in dsWhseInvHeaderGroup do
    select (arClientRow (*etc etc*))

This is of course very inefficient (reading in 62k+ rows of DsItem uses around 500MB+ of memory and takes 30+ seconds to read), but it does actually work...

Links:

SO: F# groupBy - System.Exception : unrecognized method call

GitHub: "unrecognised method call value" on a left outer join #588

GitHub: Ordering of joins can cause runtime exception with (!!) #614

GitHub: leftOuterJoin causing "unrecognized method call" with Postgresql #235

F# Query Exception, "unrecognized method call value"

1 Answers1