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