3
Vote

Support property-based joins in Linq queries

description

1) let userRolesInGroup (group : Group) = query <@ seq{ for u in dataCtx.Users do
                                                        if u.Id = user.Id then
                                                            for role in dataCtx.Roles do
                                                                if role.GroupId = group.Id && role.Users.Contains(u) then
                                                                    yield role} @>
 
throw
 
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at Microsoft.FSharp.Collections.MapTreeModule.find[TValue,a](IComparer1 comparer, TValue k, MapTree2 m)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvObjArg(FSharpMap2 env, FSharpOption1 objOpt, FSharpOption1 coerceTo)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvExpr(FSharpMap2 env, FSharpExpr inp)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvObjArg(FSharpMap2 env, FSharpOption1 objOpt, FSharpOption1 coerceTo)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvExpr(FSharpMap2 env, FSharpExpr inp)
at Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc2 mapping, FSharpList1 x)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvExprs(FSharpMap2 env, FSharpList1 es)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvExpr(FSharpMap2 env, FSharpExpr inp)
at Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc2 mapping, FSharpList1 x)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvExprs(FSharpMap2 env, FSharpList1 es)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvExpr(FSharpMap2 env, FSharpExpr inp)
at Microsoft.FSharp.Linq.QuotationEvaluation.ConvExpr(FSharpMap2 env, FSharpExpr inp)
at Microsoft.FSharp.Linq.QuotationEvaluation.Compile[a](a e)
at Microsoft.FSharp.Linq.QuotationEvaluation.Expr.EvalUntyped(FSharpExpr )
at Microsoft.FSharp.Linq.QueryModule.query[T](FSharpExpr1 p)
 
Why exception? Should be null or None Value.
 
2)
 
let userRolesInGroup (group : Group) = query <@ seq{ for u in dataCtx.Users do
                                                        if u.Id = user.Id then
                                                            for role in u.Roles do
                                                                if role.GroupId = group.Id then
                                                                    yield role} @>
 
throw
System.Exception: The following construct was used in query but is not recognised by the F#-to-LINQ query translator:
PropertyGet (Some (u),
         System.Data.Objects.DataClasses.EntityCollection`1[DevGroups.Domain.Role] Roles,
         [])
This is not a valid query expression. Check the specification of permitted queries and consider moving some of the query out of the quotation
at Microsoft.FSharp.Linq.QueryModule.TransInner@639-4.Invoke(String message)
at Microsoft.FSharp.Core.PrintfImpl.go@512-3[b,c,d](String fmt, Int32 len, FSharpFunc2 outputChar, FSharpFunc2 outa, b os, FSharpFunc2 finalize, FSharpList1 args, Int32 i)
at Microsoft.FSharp.Core.PrintfImpl.run@510[b,c,d](FSharpFunc`2 initialize, String fmt, Int32 len, FSharpList1 args)
at <StartupCode$FSharp-Core>.$Reflect.Invoke@617-4.Invoke(T1 inp)
at Microsoft.FSharp.Linq.QueryModule.TransInner@496(FSharpExpr tm)
at Microsoft.FSharp.Linq.QueryModule.TransMapConcatSelector@505(Type targetTy, Type srcTy, FSharpExpr sq, FSharpVar selectorVar, FSharpExpr t)
at Microsoft.FSharp.Linq.QueryModule.TransMapConcatSelector@505(Type targetTy, Type srcTy, FSharpExpr sq, FSharpVar selectorVar, FSharpExpr t)
at Microsoft.FSharp.Linq.QueryModule.EvalOuterWithPostProcess@643(FSharpExpr tm)
at Microsoft.FSharp.Linq.QueryModule.query[T](FSharpExpr1 p)

comments

dmilom wrote May 12, 2010 at 5:15 PM

Thank you for the bug report!
The underlying issue is missing support for property access on interation variable.
Recasting the second example in Northwnd terms, for easier testing:
let db = new NW.Northwnd()
db.Log <- System.Console.Out
let userRolesInGroup (customer :NW.Customers) (order : NW.Orders) = 
query <@ seq{ for c in db.Customers do
                if c.CustomerID = customer.CustomerID then
                    for o in c.Orders do
                        if o.OrderID = order.OrderID then
                            yield o} @>
let [c] = query <@ seq { for c in db.Customers do yield c } |> Seq.take 1 |> Seq.toList @>
let [o] = query <@ seq { for o in db.Orders do yield o } |> Seq.take 1 |> Seq.toList @>

userRolesInGroup c o

dmilom wrote May 12, 2010 at 5:29 PM

Also, as a workaround, you can use plain old SQL joins to achieve the same effect with current implementation:
let userRolesInGroup1 (customer :NW.Customers) (order : NW.Orders) = 
query <@ seq{ for c in db.Customers do
                if c.CustomerID = customer.CustomerID then
                    for o in db.Orders do
                        if o.OrderID = order.OrderID then
                            if o.CustomerID = c.CustomerID  then
                                yield o} @>
You get a very decent SQL out of the above:
SELECT [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry]
FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1]
WHERE ([t0].[CustomerID] = @p0) AND ([t1].[CustomerID] = [t0].[CustomerID]) AND ([t1].[OrderID] = @p1)

jameskerry110 wrote Apr 9, 2011 at 10:31 AM

jameskerry110 wrote Today at 3:29 AM
If the correct answer is that I need to move a DLL from the F# SDK (on Windows), that is fine. But I'd like to know whether anyone else has tried this and discovered any other important dependencies to make the PowerPack work in general. [http://www.realdissertationwriting.com dissertation help]
[[http://www.realdissertationwriting.com|dissertation help]]

jameskerry110 wrote Apr 9, 2011 at 10:32 AM

If the correct answer is that I need to move a DLL from the F# SDK (on Windows), that is fine. But I'd like to know whether anyone else has tried this and discovered any other important dependencies to make the PowerPack work in general. [url=http://www.realthesiswriting.com]thesis help[/url] "linktext":www.linktext.com

Sign in to add a comment or to set email notifications