search

fsprojects / SQLProvider

A general F# SQL database erasing type provider, supporting LINQ queries, schema exploration, individuals, CRUD operations and much more besides.
https://fsprojects.github.io/SQLProvider
Other
579 stars 146 forks source link

query with EXTRACT (.Year / .Month ...) works in where condition but not in select #651

Open cil22 opened 4 years ago

cil22 commented 4 years ago

Description

The extraction of year, month etc. by adding .Year / .Month to the field does not seem to be reflected in the "select" part of the query. It works for the "where" part.

Repro steps

  1. query { for r in ctx.schema.mytable do where (r.myDate.Year > 2015) select (r.myDate.Year) distinct

  2. Add event for SqlQuery to inspect SQL statement generated.

Expected behavior

Only the "year" of the date field should be returned.

Actual behavior

Statement is generated in a way that the whole date is returned.

Known workarounds

No workaround known.

Related information

Thorium commented 4 years ago

Hmm, this might be an Oracle specific problem... Are you using SelectOperations.DatabaseSide?

cil22 commented 4 years ago

Good point - I wasn't using SelectOperations.DatabaseSide so far. I tried it in FSI and it works! Shouldn't SelectOperations.DotNetSide work as well? Thanks for your help!

Am 10.11.2019 19:55 schrieb Tuomas Hietanen:

Hmm, this might be an Oracle specific problem... Are you using SelectOperations.DatabaseSide [1]?

-- You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub [2], or unsubscribe [3].

Links:

[1] https://fsprojects.github.io/SQLProvider/core/querying.html#SelectOperations-DatabaseSide [2] https://github.com/fsprojects/SQLProvider/issues/651?email_source=notifications&email_token=AMYLXI57RBG4NWAXF6OIYG3QTBKKXA5CNFSM4JLNBRF2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDVD7DQ#issuecomment-552222606 [3] https://github.com/notifications/unsubscribe-auth/AMYLXI2R2TMTIZBLL2677YDQTBKKXANCNFSM4JLNBRFQ

Thorium commented 4 years ago

It should... I don't have Oracle, so hard to debug what goes wrong. If you have spare time you can try to see...

Thorium commented 4 years ago

Ok, just debugged this, the reason it will not work in DotNetSide operations is that DotNetSide will select the columns and do the operations in .NET. What happens is SQL generated looks like:

SELECT DISTINCT myDate 
FROM schema.mytable
...

...and because of myDate is a date-column, it actually fetches all the distinct dates and then takes the date.Year in .NET.