Back to Questions
8.5k
views2
comments
LINQ join and anonymous types for composite keys.
introductionf#linq

Hello.

I recently started reading the introduction of how to use LINQ queries to execute SQL statements.

I would like to use Query.join from F# to join two tables using a composite key. Using a tuple for the composite key doesn't seem to work (it compiles fine but fails at run time). In C# the problem can be solved by using 'anonymous' types, as in the following snippet:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

static public IQueryable<Tuple<TableA, TableB>>
 JoinMyQueries(
  IQueryable<TableA> queryA,
  IQueryable<TableB> queryB )
 {
   return
     from a in queryA
     join b in queryB
     // on        new Tuple<string,string>( a.KeyOne, a.OtherKeyHalf )
     //    equals new Tuple<string,string>( b.FirstKey, b.OtherKeyHalf )
        on        new { item1 = a.KeyOne, item2 = a.OtherKeyHalf }
           equals new { item1 = b.FirstKey, item2 = b.OtherKeyHalf }
     select new Tuple<TableA,TableB>(a,b);
 }

As can be seen, my 'problem' is solved by replacing the explicit Tuple type with an 'anonymous' type. Both versions can be compiled in C# (my keys are strings) and have the stated return type, but only the version with the 'anonymous' types shown in the snippet actually translates into SQL (as far as I can tell).

I did a quick search of the forum and on google, but didn't see an answer there. Is there a similar/related fix I can employ to the situation in F# ?

Best regards
Robert Nielsen

.

Hello again

Maybe I should elaborate a bit. Consider the following

three F#-LINQ-SQL-queries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

let qCompareTuple = query <@ seq {
     for a in queryA
      do for b in queryB
       do if (a.Field1, a.Field2) = (b.Field1, b.Field2)
        then yield (a,b) } @>


let qStraight = query <@ seq {
     for a in queryA
      do for b in queryB
       do if a.Field1 = b.Field1 && a.Field2 = b.Field2
        then yield (a,b) } @>


let qJoinOnTuple = query <@ Query.join queryA queryB
                                   (fun a -> a.Field1, a.Field2)
                                   (fun b -> b.Field1, b.Field2)
                                   (fun a b -> (a,b)) @>

The first one, “qCompareTuple”, throws this exception:
Exception

of type "NotSupportedException" with message "The

member 'System.Tuple`2[System.String,System.String].Item1' has no

supported translation to SQL.".

I can repair it into “qStraight”, which

works fine.

The corresponding explicit join, “qJoinOnTuple”, throws the

same exception as “qCompareTuple”, and for the same reason.

In my first

post (above) I show how the explicit join can be repaired in the C#-context

by using an anonymous type. I.e. replace

1

new Tuple<string,string>( a.Field1, a.Field2 )

with

1

new { item1 = a.Field1, item2 = a.Field2 }

(and

likewise for b of course).

Is there anything I can do that allows me to use Microsoft.FSharp.Linq.Query.join with composite keys?

Best

regards
Robert Nielsen

By on 9/14/2010 12:06 PM ()

Yes, that certainly is annoying... After playing around a bit, it looks to me like the issue is that Linq to SQL expects a very particular type of Expression tree, which the PowerPack's query method is not generating. In particular, for NewExpression nodes, there is an Expression.New factory method which allows you to specify the properties or fields that constructor parameters correspond to, but the F# code is not using these methods, which is leading to the error you're seeing. It looks like there's already a bug (well, feature request) logged at the F# PowerPack site: [link:fsharppowerpack.codeplex.com]. Unfortunately, I can't see any easy workarounds for this issue...

By on 9/14/2010 10:34 PM ()

    Topic tags

    IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
    Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy     		Built with WebSharper