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
581 stars 146 forks source link

No sql is generated for skip clauses, in any provider. #133

Closed toburger closed 8 years ago

toburger commented 9 years ago

I don't have tested the other providers, but MSSQLSERVER and MSACCESS seem to ignore the Skip command.

colinbull commented 9 years ago

It seems skip isn't not implemented on any of the providers. This should be an easy enough fix I'm hoping to get to this at the weekend.

pezipink commented 9 years ago

I didn't do this originally for sql server, it is easy to do in 2012 but you have to mess around a bit in earlier versions using row number. Completely forgot about it until now :) It should be throwing there really ..

toburger commented 9 years ago

Yes, I think it is advisable to throw an exception, if a query feature isn't implemented.

Thorium commented 9 years ago

There are some other operators missing from the F# Query Expressions API as well: https://msdn.microsoft.com/en-us/library/hh225374.aspx

E.g. headOrDefault is not working, but head will return the result that I would expect as headOrDefault (in the NuGet version). That is ok, I don't mind, it would just be nice to support the whole API.

pezipink commented 9 years ago

The whole query expression API is big and we have several large, complicated bits missing, such as nested queries and aggregation (and indeed left join outside my !! operator requires group join to be implemented) - these are big projects. Certainly more operators like headOrDefault are fairly simple to implement, we have a bunch of them already.

The original non-erasing sql provider is simply a wrapper around sqlmetal so you get the whole linq expression tree for free :)

It is also worth noting that in some cases, the examples there generate different sql to what we do, sometimes better and sometimes worse.

Edit: in hindsight I should not have used query { } as the LINQ trees are crazy and hard to deal with.

Ross

colinbull commented 9 years ago

FYI https://github.com/colinbull/SqlProvider/blob/feat_nicer_naming/tests/SqlProvider.Tests/QueryTests.fs this shows what is currently supported an not.

colinbull commented 9 years ago

@pezipink This looks like it might be fixed? Is this the case?

pezipink commented 9 years ago

I fixed it in the V1 branch for sql server 2012 and up... mysql and postgres it was already working for I think.

Earlier versions of sql server this is a bit of a pain and hacky to achieve.

pezipink commented 9 years ago

I lied, postgres is not implemented. MySql is though, and they both use LIMIT .. maybe postgres can use the same implementation? https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/Providers.MySql.fs#L531

colinbull commented 8 years ago

I think I can close this now. As it look as if we have as much support is as possible across all the providers. ODBC is the only one I'm not sure about.