Unable to use strings with isNotIn |<>| operation?

[sachinshahredmane]

Hello,

I have tried the following with both .NET 6 and .NET 7 and am receiving an error.

  selectTask HydraReader.Read (Create openContext) {
    for dl in dlTable do
      where (isNotIn dl.SystemName ignoreSystemNames)
      select (dl.DataListID, dl.Name, dl.SystemName) into selected
      mapList (//...
      )
  }

dl.SystemName is a nvarchar(120) column in the DB. ignoreSystemNames has type seq<string option>.

When I try to run the program, it fails immediately with the following exception. My question is - is isNotIn supported with strings? If so, how can I get it to work?

PS C:\projects2\graph\ConsoleApp1\bin\Debug\net6.0> .\mCaseDiagramGenerator.exe
Unhandled exception. System.TypeInitializationException: The type initializer for '<StartupCode$mCaseDiagramGenerator>.$com.znprojects.app.Program' threw an exception.
 ---> System.AggregateException: One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (No mapping exists from object type Microsoft.FSharp.Core.FSharpOption`1[[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] to a known managed provider native type.)))
 ---> System.AggregateException: One or more errors occurred. (One or more errors occurred. (No mapping exists from object type Microsoft.FSharp.Core.FSharpOption`1[[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] to a known managed provider native type.))
 ---> System.AggregateException: One or more errors occurred. (No mapping exists from object type Microsoft.FSharp.Core.FSharpOption`1[[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] to a known managed provider native type.)
 ---> System.ArgumentException: No mapping exists from object type Microsoft.FSharp.Core.FSharpOption`1[[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] to a known managed provider native type.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__207_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
   --- End of inner exception stack trace ---
   --- End of inner exception stack trace ---
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
   at System.Threading.Tasks.Task`1.get_Result()
   at com.znprojects.apps.DatabaseConnection.buildModel() in C:\projects2\mCase-graph\ConsoleApp1\DatabaseConnection.fs:line 117
   at <StartupCode$mCaseDiagramGenerator>.$com.znprojects.app.Program..cctor() in C:\projects2\mCase-graph\ConsoleApp1\Program.fs:line 9
   --- End of inner exception stack trace ---
   at com.znprojects.app.Program.main(String[] argv) in C:\projects2\mCase-graph\ConsoleApp1\Program.fs:line 193

[JordanMarr]

It looks like a bug. Can you give me more info about the type? It's hard to say without being able to see the properties being used in your query.

For a temporary workaround, you can use a manual query in conjunction with the generated HydraReader like this example:

let getForecast (lower: DateTime, upper: DateTime) = task {
  let sql = """
    SELECT * FROM myschema.weather w
    WHERE     w.time >= @lower AND w.time <= @upper
      AND w.timestamp = (
        SELECT MAX(timestamp) FROM weather inner 
        WHERE inner.location = w.location AND inner.instrument = w.instrument AND inner.time =  w.time
    )
  """

  use conn = new NpgsqlConnection(DB.connectionString)
  do! conn.OpenAsync()
  use cmd = new NpgsqlCommand(sql, conn)
  cmd.Parameters.Add(NpgsqlParameter("lower", lower)) |> ignore
  cmd.Parameters.Add(NpgsqlParameter("upper", upper)) |> ignore
  let reader = cmd.ExecuteReader()
  let hydra = HydraReader(reader :?> NpgsqlDataReader)

  return [ while reader.Read() do hydra.``myschema.weather``.Read() ]
}

Obviously you will need to swap out Npgsql____ with whichever ADO.NET provider you are using (NpgsqlConnection -> SqlConnection for SQL Server).

[sachinshahredmane]

Which types are you looking for? Happy to provide more information. Also, I should note that I am using SQL Server as the database, not sure if that affects the behavior or not.

Here is the definition of the ignoreSystemNames (it's a constant that is passed in elsewhere):

let DefaultIgnoreListSystemNames =
  [ "TODO"
    "DataMover"
    "DataFieldMover"
    "AnalysisReference"
    "CHECKINOPTIONS"
    "UploadDataList"
    "UploadErrorDataList" ]
  |> List.map (fun sn -> sn.ToUpper())
  |> List.map Option.Some

Here is the column in my schema:

Here is the exact query that it is running:

let getLists ignoreSystemNames =
  selectTask HydraReader.Read (Create openContext) {
    for dl in dlTable do
      where (dl.SystemName |<>| ignoreSystemNames)
      select (dl.DataListID, dl.Name, dl.SystemName) into selected

      mapList (
        let dlid, n, sn = selected

        { DataListID = dlid
          Name = n
          SystemName = (Option.defaultValue "" sn).ToUpper()
          Category = ListCategory.Unknown
          TopLevel = false }
      )
  }

[sachinshahredmane]

I should note, my workaround right now is to get all values from the DB since I want the majority (minus the 7 in my list), then filter on the results of the Task. However, I will definitely keep the workaround in mind (and may end up implementing it anyway).

[JordanMarr]

It looks like dl.SystemName is an option type, which explains why you are mapping your list to Option.Some. |<>| / isNotIn doesn't know how to deal with a list of option values (or at least not at the moment).

Try using where (dl.SystemName.Value |<>| ignoreSystemNames) (and not mapping the list to option values).

There is a similar bug from a few days ago around option types that I was just about to release a fix for today, and this looks like it may be the same bug.

[sachinshahredmane]

You're absolutely right, I was just about to comment that another query where the column is not-nullable works as expected (string |<>| stringList).

I will try to force the .Value and see if that fixes it for now. Thank you!

[JordanMarr]

Please let me know if that works for you.

[sachinshahredmane]

Using .Value has fixed the issue for now.

I take that back - no luck. I get a different error now, specifically as follows:

System.TypeInitializationException: 'The type initializer for '<StartupCode$mCaseDiagramGenerator>.$com.znprojects.app.Program' threw an exception.'

Inner Exception
NotImplementedException: The method or operation is not implemented.

This exception was originally thrown at this call stack:
    [External Code]
    com.znprojects.apps.DatabaseConnection.getLists(System.Collections.Generic.IEnumerable<string>) in DatabaseConnection.fs
    com.znprojects.apps.DatabaseConnection.buildModel(Microsoft.FSharp.Collections.FSharpList<string>) in DatabaseConnection.fs
    <StartupCode$mCaseDiagramGenerator>.$com.znprojects.app.Program.Program() in Program.fs

I assume the NotImplemented is for an implementation of |<>| for string

[sachinshahredmane]

For now, I'm filtering outside the CE and that, of course, works.

[JordanMarr]

Are you using the latest SqlHydra.Query v2.0.1?

[sachinshahredmane]

I am. I found the issue.

If I use this, it works: dl.SystemName.Value.

If I try to do something with the string, it fails: dl.SystemName.Value.ToUpper().

I changed the DB to use an case-insensitive collation and that solved the problem. I believe this issue can be closed now.

[JordanMarr]

Thanks for the update.