Tuples in query expression with grouping cannot be translated to SQL

[KevinRansom]

Opened by tomasp on CodePlex

When answering this StackOverflow question, I came across an odd behavior that appears to be a bug:

#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.Linq.dll"
open System
open System.Data
open System.Linq
open Microsoft.FSharp.Data.TypeProviders

type dbSchema = SqlDataConnection< @"Server=.\SQLExpress;AttachDbFilename=C:\Temp\Northwind.mdf;Database=dbname;Trusted_Connection=Yes;">
let db = dbSchema.GetDataContext()

query {
    for p in db.Products do
    join c in db.Categories on (p.ProductID = c.CategoryID)
    groupBy p.CategoryID.Value into g
    select (g.Key, g.Select(fun (p, c) -> if p.ProductName = c.CategoryName then 1.0 else 0.0).Average()) }
|> Array.ofSeq

Running this gives the following message:

System.InvalidOperationException: Member access 'Products Item1' of 'System.Tuple2[Products,Categories]' not legal on type 'Microsoft.FSharp.Linq.RuntimeHelpers.Grouping2[System.Int32,System.Tuple`2[Products,Categories]].

It appears that somewhere during the translation, the translator attempts to generate (or process) code that accesses the Item1 property (which should be a property of the elements in the collection) as if it was a property of the entire grouping.

Is the F# library doing some pre-processing here? I remember that tuples were always a problem in F# queries, because they are immutable and so they behave differently than C# anonymous types (which the translator handles well)...

comments
latkin wrote Apr 24, 2014 at 12:56 PM [x]
Yes, we have rather nasty issues that arise due to immutable/mutable tuple conversions in queries. See this issue, for example.

tomasp wrote Apr 25, 2014 at 8:44 AM [x]
Thanks for the clarification! This makes sense - I remember trying to get that to work in some earlier versions of F# LINQ and it was pretty messy.
Could someone who knows the code share a few pointers to the relevant bits in the F# codebase? >>This sounds like an issue that people from the F# community could help to fix (because it is quite separate stand-alone component that can be modified without knowing everything about the compiler :-)).
I'm sure that with a bit of help & starting points, we could get a nice pull request for this one!

dsyme wrote Apr 28, 2014 at 7:28 AM [x]
The general area is here:
https://github.com/fsharp/fsharp/blob/65eb229125713caa2a97d85e6541313f9da4d9f7/src/fsharp/FSharp.Core/Query.fs#L1065
The implementation is non-trivial I'm afraid, one of the hardest parts of FSharp.Core

vladima wrote Apr 28, 2014 at 9:21 AM [x]
Some time ago I've prototyped implementation that 1) converts quotations to the shape similar to ETs produced by C# compiler 2) delays translation Tuple <-> AnonymousObject to the last step so query composition should not be affected by it. Maybe it is time to publish it as possible alternative that can replace existing implementation somewhere in the future (far far away)

[octaviordz]

Is there any document describing current limitations of "query expression" being used against MSSQL,
Thanks.

[dsyme]

@octaviordz I think this is one of the only known limitations.

[btrepp]

Is there a way of working around this?. Using something like

query{
    .....
    groupBy something into groups
    select (groups.Key, 
               groups.Select(fun (a,b,c) -> ... ).Max()
}

Results in the exception

Member access 'PgEventType Item3' of 'System.Tuple`3[Matrikon+dbml+PgEvent,Matrikon+dbml+PgTag,Matrikon+dbml+PgEventType]' not legal on type 'Microsoft.FSharp.Linq.RuntimeHelpers.Grouping`2[Matrikon+dbml+PgTag,System.Tuple`3[Matrikon+dbml+PgEvent,Matrikon+dbml+PgTag,Matrikon+dbml+PgEventType]].

[Brains]

Is there a way of working around this?

Solution from F# 3 SQL Database using Type Provider/ groupBy on multiple columns works for me

You have to use AnonymousObject. The following should work:

let key = AnonymousObject<_,_>(cal.APYear, cal.APMonth)
groupValBy dsd.InvoiceQuantity key into g
select (key.Item1, key.Item2, g.Count())