Cannot replicate MSDN example for "IN a set of specified values"

[jimfoye]

I'm trying to follow this example from https://msdn.microsoft.com/en-us/library/hh225374.aspx. This sample code also appears on the FSharpComposableQuery queries example page, but it's not clear to me if that is required to work (I added FSharpComposableQuery to my project, but it made no difference).

// Select students where studentID is one of a given list.
let idQuery = query { for id in [1; 2; 5; 10] do
                      select id }
query { 
    for student in db.Student do
    where (idQuery.Contains(student.StudentID))
    select student
}

When I try it like this:

let idQuery = query { for id in [1; 2; 5; 10] do
                      select id }
let summary =
    query { for row in ctx.AzUnknownScenario.QryEpiSummary do
            where ((row.ScenarioId = scenario.Id) && 
                   (idQuery.Contains(row.PatientCategoryId)))
            select { Country = row.Country;
                     Segment = row.SegmentName;
                     PatientCategory = row.PatientCategory;
                     Year = row.CurrentYear;
                     Value = row.CurrentValue; } }

At runtime I get an exception:

"Unsupported expression. Ensure all server-side objects appear on the left hand side of predicates. The In and Not In operators only support the inline array syntax."

Beside scratching my head wondering why this is not working, I'm also looking around for an example of this "inline array syntax", but I haven't found anything.

[colinbull]

Hi,

Indeed this explicit syntax isn't supported. However we do have a way of providing what you need.

 query { 
     for student in db.Student do
     where (student.StudentID |=| [|1;2;5;10|])
     select student
 }

Hope this helps.

[jimfoye]

Thanks, that does compile, but now I get a different runtime error:

{System.Data.OleDb.OleDbException (0x80040E10): No value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader() at FSharp.Data.Sql.Runtime.QueryImplementation.executeQuery(ISqlDataContext dc, ISqlProvider provider, SqlExp sqlExp, List1 ti) at FSharp.Data.Sql.Runtime.QueryImplementation.SqlQueryable1.System-Collections-Generic-IEnumerable1-GetEnumerator() at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc2 action, IEnumerable1 source) at AzExcel.CreateEpiSummary@469.Invoke(Unit unitVar0) in C:\Users\James Foye\Documents\Visual Studio 2015\Projects\AZExcelOutput\AZExcelOutputLib\Excel.fs:line 519 at AzExcel.Run(Application app, ImportScenarioViewModel viewModel) in C:\Users\James Foye\Documents\Visual Studio 2015\Projects\AZExcelOutput\AZExcelOutputLib\Excel.fs:line 556 at AZExcelOutputLib.MyRibbon.OnButtonPressed(IRibbonControl control) in C:\Users\James Foye\Documents\Visual Studio 2015\Projects\AZExcelOutput\AZExcelOutputLib\Main.fs:line 40} System.Exception {System.Data.OleDb.OleDbException}

Any ideas?

[jimfoye]

Well, actually I have it working in FSI interactive, so there must be something else wrong in my code. I'll close the issue. Thanks very much for the help.

I know it should be easy to find the documentation on F# query expression operators, but my google skills are completely failing me. If anyone has a link for that, please share.

I searched the text for "Expert F# 4.0" for "|=|" and found no hits.

[colinbull]

This is a custom operator for the sql provider. It allows us to simplify the handling of the linq expression trees, which can become sort of epic.

On Tue, 1 Mar 2016 at 17:23, Jim Foye notifications@github.com wrote:

Well, actually I have it working in FSI interactive, so there must be something else wrong in my code. I'll close the issue. Thanks very much for the help.

I know it should be easy to find the documentation on F# query expression operators, but my google skills are completely failing me. If anyone has a link for that, please share.

I searched the text for "Expert F# 4.0" for "|=|" and found no hits.

— Reply to this email directly or view it on GitHub https://github.com/fsprojects/SQLProvider/issues/226#issuecomment-190820448 .

[colinbull]

FYI http://fsprojects.github.io/SQLProvider/core/querying.html

[pezipink]

Yes, our documentation is terrible at the moment, the MSDN documentation is the general documentation for F# Query expressions, it uses SQL as an example but the type provider it uses is the original non-erasing one that Microsoft released which uses SQLMetal under the hood. This is NOT the SQLProvider, there are several things from that page we do not support such as aggregation and nested queries, we also generate SQL slightly differently (hopefully better!) in certain situations.

I know this can be misleading, once we sort our own documentation out this should help clear things up! You can look at the tests for the kind of things we support in the query expressions.

[jimfoye]

Great, thanks for the link. A lot of the links are just stubs, and I guess I didn't try that one.

[jimfoye]

For the sake of completeness, the OleDbException I got above was due to the fact I was opening a database whose schema had gotten slightly out of sync with the one I was using at compile-time. One really has to be careful about that!

[yetanotherlogonfail]

ok if we get this problem caused by some bug in the sqlprovider

The In and Not In operators only support the inline array syntax. op_BarEqualsBar(tupledArg.Item1.GetColumn("FullDate").DayOfWeek.ToString(), Cons("1.0", get_Empty()))

where is the example of a non inline array syntax?

[Thorium]

Not valid commend, tested, and the MSDN syntax is supported.

[Thorium]

@yetanotherlogonfail , what is your query like? Maybe something like this:

query {
   //...
   where fst(myTuple).DayOfWeek.ToString() |=| ["1.0"; ""]
   //...
}

The problem is that SQL doesn't really understand .NET functions like getting first from tuple or "DayOfWeek". Can you format your query { ... } in such way that it could be thought as simple SQL-query, and do the main .NET logic of parsing and conversions outside the query { ... }?

SQLProvider is doing some effort to clean the queries but it's magic is limited.

[yetanotherlogonfail]

Tuomas

Many thanks for your reply, this has been driving me nuts for 1.5 days now

I copied and pasted your example

let arr = [|1; 2; 5; 10|];query { for student in db.Student do where (arr.Contains(student.StudentID)) select student}

I get a red squiggle under Contains I only have CopyTo and GetHashCode

have I missed namespace ?

I have "open System" in my script file and sent to interactive window

On 30 December 2016 at 10:25, Tuomas Hietanen notifications@github.com wrote:

Why do you want to do this:

// Select students where studentID is one of a given list.let idQuery = query { for id in [1; 2; 5; 10] do select id }

SQLProvider is executing query { ... } in database. So what is the purpose of that query?

You should be able to do nested IN-query:

// Select students where studentID is one of a given list.let idQuery = query { for student in db.Sometable do ...something... } query { for student in db.Student do where (idQuery.Contains(student.StudentID)) select student}

or contains-IN with array:

let arr = [|1; 2; 5; 10|];query { for student in db.Student do where (arr.Contains(student.StudentID)) select student}

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/fsprojects/SQLProvider/issues/226#issuecomment-269755583, or mute the thread https://github.com/notifications/unsubscribe-auth/AGIVWsscbo98-1o6Qw66kpHVl2y0EtJLks5rNNwqgaJpZM4Hl_qR .

-- Regards, Carl

``Evolution is cleverer than you are.'' --Francis Crick http://whitedwarf.org/metcalfe/node15.htm#cri84

[Thorium]

In .NET Contains comes from System.Linq so you have to open that also.

Actually this is working in my machine:

    let dc = sql.GetDataContext()
    let arr = query { for x in ["ALFKI"; "ANATR"; "AROUT"] do select x }
    let qry = 
        query {
            for cust in dc.Main.Customers do
            where (arr.Contains(cust.CustomerId))
            select cust.CustomerId
        } |> Seq.toArray

    // Executing SQL: 
    // SELECT [cust].[CustomerID] as 'CustomerID' FROM main.Customers as [cust] WHERE (([cust].[CustomerID] IN (@param1,@param2,@param3))) - params @param1 - "ALFKI"; @param2 - "ANATR"; @param3 - "AROUT"; 

Edit: Also, note that by default query { ... } is lazy, so it won't be evaluted if you don't do Seq.toArray or Seq.toList or something. E.g. if you just add more conditions later, they will be combined to the same SQL-query.

[yetanotherlogonfail]

Tuomas

works fine thank you.

will now hold 2 minutes silence one in your honour and one for my stupidity.

:-)

On 30 December 2016 at 11:38, Tuomas Hietanen notifications@github.com wrote:

In .NET Contains comes from System.Linq so you have to open that also.

Actually this is working in my machine:

let dc = sql.GetDataContext()
let arr = query { for x in ["ALFKI"; "ANATR"; "AROUT"] do select x }
let qry =
    query {
        for cust in dc.Main.Customers do
        where (arr.Contains(cust.CustomerId))
        select cust.CustomerId
    } |> Seq.toArray

// Executing SQL:
// SELECT [cust].[CustomerID] as 'CustomerID' FROM main.Customers as [cust] WHERE (([cust].[CustomerID] IN (@param1,@param2,@param3))) - params @param1 - "ALFKI"; @param2 - "ANATR"; @param3 - "AROUT";

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/fsprojects/SQLProvider/issues/226#issuecomment-269762115, or mute the thread https://github.com/notifications/unsubscribe-auth/AGIVWtljrQPgyaJNlI9N8m1AzdyPrJrsks5rNO05gaJpZM4Hl_qR .

-- Regards, Carl

``Evolution is cleverer than you are.'' --Francis Crick http://whitedwarf.org/metcalfe/node15.htm#cri84

[Thorium]

The problem is obviously that our documentation is not clear enough. Please report any other usability issues and we'll try to improve our documentation.