Using a where clause with leftJoin

[sydsutton]

Hi Jordan,

First, thank you for the great product! Second, I was wondering if you have any suggestions for using a where clause after leftJoin'ing tables, like in the example below. When I try to dot into it with .Value in the where clause, I get this error: "Nested property value extraction is not supported in 'where' statements. Try manually unwrapping and passing in the value." I would appreciate any advice you might have. Thanks.

[JordanMarr]

Hmm.. that looks like a regression bug.

[sydsutton]

Any suggestions and/or workarounds for the time being?

[JordanMarr]

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

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).

[JordanMarr]

Also, it appears you are still manually creating table bindings in your Tables module, but those are now generated for you as of v1.2.0: https://github.com/JordanMarr/SqlHydra/releases/tag/v1.2.0

[JordanMarr]

I have made the fix, but I had to blow away my docker containers the other day, so it may take a few days for me to get set up again.

[sydsutton]

Thanks so much for the help, @JordanMarr !

[JordanMarr]

SqlHydra.Query v1.2.2 is available now with this fix.