This control construct may only be used if the computation expression builder defines a 'For' method

[jamessdixon]

I am attempting to use the Sql Provider against the Adventure Works database. I can connect and the types are getting defined correctly. When I attempt to create this computational expression

r "../packages/SQLProvider.0.0.9-alpha/lib/net40/FSharp.Data.SQLProvider.dll"

open FSharp.Data.Sql

type AdventureWorks = SqlDataProvider
let context = AdventureWorks.GetDataContext()

let salesOrderQuery = query { for soh in context.[Sales].[SalesOrderHeader] do
select soh} |> Seq.toArray

I get this exception
This control construct may only be used if the computation expression builder defines a 'For' method

Can you give me a suggestion on how to fix? Thanks

[jamessdixon]

Interestingly, even though it returns that error in the script file, when I send it to the FSI, it works. I am having a different problem now. Iam trying the in operator and it is failing

let salesOrderQuery = query { for soh in context.[Sales].[SalesOrderHeader] do
join sod in context.[Sales].[SalesOrderDetail] on (soh.SalesOrderID = sod.SalesOrderID)
join p in context.[Production].[Product] on (sod.ProductID = p.ProductID)
where (soh.OrderDate > DateTime(2013,5,1) && p.ProductSubcategoryID |=| [|1 .. 3|])
select soh } |> Seq.toArray

The p.ProductSubcategoryID |=| [|1 .. 3|]) Throws a
System.ArgumentException: No mapping exists from object type System.Object[] to a known managed provider native type.

Any ideas?

[colinbull]

Can you try lifting the construction of the array and the datetime out of the query expression and just replacing them with variables instead. I suspect we won't support constructors in query expressions. If this doesn't work, let me know and I'll have a look deeper when I have something more than my phone to hand.

[jamessdixon]

let productSubcategoryIDs = [|1 .. 3|]
let minOrderDate = DateTime(2013,5,1)
let salesOrderQuery = query { for soh in context.[Sales].[SalesOrderHeader] do
join sod in context.[Sales].[SalesOrderDetail] on (soh.SalesOrderID = sod.SalesOrderID)
join p in context.[Production].[Product] on (sod.ProductID = p.ProductID)
where (soh.OrderDate > minOrderDate && p.ProductSubcategoryID |=| productSubcategoryIDs)
select (soh) } |> Seq.toArray

Still returns the same error

[pezipink]

there was a change to this stuff very recently. Coincidence?
880f77e

[pezipink]

@jamessdixon are you using the latest development-v1 branch? or the old package?

[jamessdixon]

Nuget

I am using it in the book I am writing so I need to use Nuget…

From: Ross McKinlay [mailto:notifications@github.com]
Sent: Thursday, November 19, 2015 8:03 AM
To: fsprojects/SQLProvider SQLProvider@noreply.github.com
Cc: Jamie Dixon jamessdixon@gmail.com
Subject: Re: [SQLProvider] This control construct may only be used if the computation expression builder defines a 'For' method (#170)

@jamessdixon https://github.com/jamessdixon are you using the latest development-v1 branch? or the old package?

—
Reply to this email directly or view it on GitHub #170 (comment) . https://github.com/notifications/beacon/ABVH1Eo7XVvK3xqTrThrHdYhvCcDwL5tks5pHcAIgaJpZM4GlM3h.gif

[pezipink]

ah ok ouch.... that should really be working. the package is over a year old though and massively out of date (sorry). It is likely working in the dev v1 branch. We did make a push for the new package, there is not a lot left to do but I / we got waylaid with other stuff and I will be tied up until the end of the year now :(

[jamessdixon]

I’ll pull the dev branch

Any objections if I create a nugget package from the latest on github and out it out there?

I really want to use it in my book.

From: Ross McKinlay [mailto:notifications@github.com]
Sent: Thursday, November 19, 2015 8:13 AM
To: fsprojects/SQLProvider SQLProvider@noreply.github.com
Cc: Jamie Dixon jamessdixon@gmail.com
Subject: Re: [SQLProvider] This control construct may only be used if the computation expression builder defines a 'For' method (#170)

ah ok ouch.... that should really be working. the package is over a year old though and massively out of date (sorry). It is likely working in the dev v1 branch. We did make a push for the new package, there is not a lot left to do but I / we got waylaid with other stuff and I will be tied up until the end of the year now :(

—
Reply to this email directly or view it on GitHub #170 (comment) . https://github.com/notifications/beacon/ABVH1JWLvJiliwArSEnoOTcetWQuuTcbks5pHcJ2gaJpZM4GlM3h.gif

[pezipink]

Sure thing, hopefully by the time you are finished, the V1 branch will be the published version anyway!

Regarding the In operator, in this particular example you can get around using in by using > and < but I know that is not ideal.

[jamessdixon]

Thanks

From: Ross McKinlay [mailto:notifications@github.com]
Sent: Thursday, November 19, 2015 8:18 AM
To: fsprojects/SQLProvider SQLProvider@noreply.github.com
Cc: Jamie Dixon jamessdixon@gmail.com
Subject: Re: [SQLProvider] This control construct may only be used if the computation expression builder defines a 'For' method (#170)

Sure thing, hopefully by the time you are finished, the V1 branch will be the published version anyway!

Regarding the In operator, in this particular example you can get around using in by using > and < but I know that is not ideal.

—
Reply to this email directly or view it on GitHub #170 (comment) . https://github.com/notifications/beacon/ABVH1ECWXffqoOvHchmVizwaP3Zko0Ugks5pHcOOgaJpZM4GlM3h.gif

[jamessdixon]

So that did it

There are some changes:

type AdventureWorks = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER,connectionString>

And SCREEMING CAPS for the entities and attributes

let salesOrderQuery = query { for soh in context.[SALES].[SALESORDERHEADER] do

                          join sod in context.``[SALES].[SALESORDERDETAIL]`` on (soh.SALESORDERID = sod.SALESORDERID)

                          join p in context.``[PRODUCTION].[PRODUCT]`` on (sod.PRODUCTID = p.PRODUCTID)

                          where (soh.ORDERDATE > DateTime(2013,5,1) && p.PRODUCTSUBCATEGORYID  |=| [|1..3|])

                          select (soh) } |> Seq.toArray

Returns data. Still getting the REPL complaining that For method is not implemented…

I’ll hold off on deploying to nugget until it is ready. Until then, I will use it assuming that the neew version will be out by the time the books goes March 31, 2016

Thanks

From: Ross McKinlay [mailto:notifications@github.com]
Sent: Thursday, November 19, 2015 8:18 AM
To: fsprojects/SQLProvider SQLProvider@noreply.github.com
Cc: Jamie Dixon jamessdixon@gmail.com
Subject: Re: [SQLProvider] This control construct may only be used if the computation expression builder defines a 'For' method (#170)

Sure thing, hopefully by the time you are finished, the V1 branch will be the published version anyway!

Regarding the In operator, in this particular example you can get around using in by using > and < but I know that is not ideal.

—
Reply to this email directly or view it on GitHub #170 (comment) . https://github.com/notifications/beacon/ABVH1ECWXffqoOvHchmVizwaP3Zko0Ugks5pHcOOgaJpZM4GlM3h.gif

[pezipink]

cool, I have not seen the For thing before, that is strange. Is the actual schema in caps? We have changed the way stuff is named, there was a time when everything was in caps but it is not anymore. I think there is even a static parameter to control it.

Also do you know that you don't have to write those joins explicitly? you can just for over the relevant relationship property of the source type instance.

[pezipink]

is that deffo the dev-v1 branch? not just the latest of master? you want this one

https://github.com/fsprojects/SQLProvider/tree/Development_V1

I think you might be using the wrong one as the static parameters are in a different order now and I believe master still has the ALL CAPS!

[jamessdixon]

The schema is not in caps

I built the Developerment_V1 branch

Joins - no, I did not know that

Can you refactor my sample to show me?

From: Ross McKinlay [mailto:notifications@github.com]
Sent: Thursday, November 19, 2015 8:41 AM
To: fsprojects/SQLProvider SQLProvider@noreply.github.com
Cc: Jamie Dixon jamessdixon@gmail.com
Subject: Re: [SQLProvider] This control construct may only be used if the computation expression builder defines a 'For' method (#170)

cool, I have not seen the For thing before, that is strange. Is the actual schema in caps? We have changed the way stuff is named, there was a time when everything was in caps but it is not anymore. I think there is even a static parameter to control it.

Also do you know that you don't have to write those joins explicitly? you can just for over the relevant relationship property of the source type.

—
Reply to this email directly or view it on GitHub #170 (comment) . https://github.com/notifications/beacon/ABVH1OVYg2hEdpNrusMZjWVMH6f8MQs4ks5pHcjPgaJpZM4GlM3h.gif

[pezipink]

I can't show you exactly as the names will depend on the schema, but it will be like this, as opposed to the test above it :

https://github.com/fsprojects/SQLProvider/blob/Development_V1/tests/SqlProvider.Tests/QueryTests.fs#L268