{"action":"opened","issue":{"url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/141","labels_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/141/labels{/name}","comments_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/141/comments","events_url":"https://api.github.com/repos/fsprojects/SQLProvider/issues/141/events","html_url":"https://github.com/fsprojects/SQLProvider/issues/141","id":95662195,"number":141,"title":"Joining with multiple keys","user":{"login":"colinbull","id":585546,"avatar_url":"https://avatars.githubusercontent.com/u/585546?v=3","gravatar_id":"","url":"https://api.github.com/users/colinbull","html_url":"https://github.com/colinbull","followers_url":"https://api.github.com/users/colinbull/followers","following_url":"https://api.github.com/users/colinbull/following{/other_user}","gists_url":"https://api.github.com/users/colinbull/gists{/gist_id}","starred_url":"https://api.github.com/users/colinbull/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/colinbull/subscriptions","organizations_url":"https://api.github.com/users/colinbull/orgs","repos_url":"https://api.github.com/users/colinbull/repos","events_url":"https://api.github.com/users/colinbull/events{/privacy}","received_events_url":"https://api.github.com/users/colinbull/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-07-17T14:03:49Z","updated_at":"2015-07-17T14:03:49Z","closed_at":null,"body":"On lots of databases the relationships aren't explict thus the `GetSchema` call doesn't pick things up. So we can't use the relationship navigation properties that the provider supplies. To get around this you need to create an explict join. This works nicely for a single key but when you have to join on multiple keys it doesn't work. For example, \r\n\r\n    query {\r\n        for price in ctx.Dbo.Price do\r\n        join commodityMarket in ctx.Dbo.CommodityMarket on (price.CommktKey = commodityMarket.CommktKey)\r\n        join tradingPeriod in ctx.Dbo.TradingPeriod on ((price.TradingPrd, price.CommktKey) = (tradingPeriod.TradingPrd, tradingPeriod.CommktKey))\r\n        join commodity in ctx.Dbo.Commodity on (commodityMarket.CmdtyCode = commodity.CmdtyCode)\r\n        where (price.PriceQuoteDate >= quoteDate \r\n            && tradingPeriod.TradingPrd <> \"SPOT\"\r\n            && price.PriceSourceCode = \"INTERNAL\"\r\n            && commodity.CmdtyType = \"P\"\r\n            && commodityMarket.MktCode <>% \"OSP%\")\r\n        select (price.PriceQuoteDate, \r\n                commodityMarket.MktCode,\r\n                commodityMarket.CmdtyCode,\r\n                tradingPeriod.FirstDelDate,\r\n                tradingPeriod.LastDelDate,\r\n                price.AvgClosedPrice,\r\n                tradingPeriod.TradingPrd)\r\n        \r\n    } \r\n\r\nHowever, currently this doesn't work yielding the following error. \r\n\r\n    System.Exception\r\n       at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e)\r\n       at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence)\r\n       at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedOuter(CanEliminate canElim, FSharpExpr tm)\r\n       at Microsoft.FSharp.Linq.QueryModule.clo@1737-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 )\r\n       at FSI_0003.Lacima.Loader.Symphony.getPrices(DateTime quoteDate) in C:\\Appdev\\lacima loader\\Lacima.Loader\\Lacima.Loader\\Symphony.fs:line 16\r\n       at <StartupCode$FSI_0005>.$FSI_0005.main@() in C:\\Appdev\\lacima loader\\Lacima.Loader\\Lacima.Loader\\Script.fsx:line 23\r\n    Stopped due to error\r\n\r\n\r\n"}}