sql - F# 查询异常， "unrecognized method call value"

Question

问:以下异常是什么意思？

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

我正在尝试使用 SqlDataProvider 将 Pervasive SQL 查询转换为 F# 查询用一个 ODBC 连接，一点一点地慢慢建立起来。我收到以下查询的异常，但有一个有点神秘的异常。

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)

当 printfn语句被执行并且表达式被实际计算，异常被命中:

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

这是一个更大的查询的一部分，它在执行时有完全相同的异常:

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*)
}

仅供引用，此 F# 查询所源自的 SQL 查询如下( SELECT 语句不如抛出 F# 异常的 JOIN 子句重要):

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

Answer 1

在阅读了一些 SO 答案和 GitHub 问题(链接如下)后，目前似乎不支持以我正在执行的方式对此类 SQL 数据库进行确切操作(也许？至少在我理解的情况下)阅读)，虽然显然左加入 query表达式可以使用 !!运营商——但我还没有成功。
然而，我发现以下笨拙的解决方法使用相同的 query我一直使用的表达式是通过将数据转换为 List 来强制将数据读入内存。 (或 Array 等)首先:

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
}

然后可以在原来的 query中使用表达变化最小。

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

这当然非常低效(读取 62k+ 行 DsItem 使用大约 500MB+ 的内存并需要 30+ 秒才能读取)，但它确实有效......

链接:
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