Comments (15)
The subsequent functions have to have "Year" anyway. How would you set it working ideally? How does it work normally? It's just a tuple of value types you are dealing with at this stage..
from sqlprovider.
See*
from sqlprovider.
yep I know it's a tuple and Deedle cannot infer the names.
Better would something like C#'s anonymous types.
from sqlprovider.
If the query returned something like IQueryable<T>
(not sure?) then Frame.ofRecords
could try to look at the select
statement (it should be possible to analyze the quotation, right?) and pick the names from there (and if x.Year
was somehow marked as primary key, it could even use that information).
(Probably not something that Frame.ofRecords
should do, but perhaps we could try adding Frame.ofQuery
?)
from sqlprovider.
@tpetricek that would totally rock this!
from sqlprovider.
IQueryable is almost never a good idea, avoid it all costs! I think we might be overcomplicating this - I have not played with Deedle yet so forgive my ignorance.
It seems to me the real problem here is that you need a way of labelling the data - we can address this problem directly. I can introduce a static parameter that will cause either all the column properties to return a tuple where the first item is the column name, or for it to generate sister properties for each column (perhaps suffixed "WithName" or similar) which would return said tuple.
I would then further suggest that the new Frame.ofQuery function assumes the first tuple in the sequence to always be the primary key, or if one is not always required then have this is a bool argument which when true will use the first element as the primary key. You could then strip the labels out and remember them or do whatever you like with them.
Would something like this work ?
from sqlprovider.
The idea is that we somehow need to get the names of the properties. This will work fine if you do:
let debtData =
query {for x in ctx.``[dbo].[Test$USDebt]`` do
where (...) }
|> Frame.ofRecords
... because Deedle uses reflection to generate data frame with all properties of the objects it gets. However, when you add select
and get just a tuple back, the names will be Item1
, Item2
, ... The idea was that if the source is a query, we could look at the tuple construction and extract the names from there (without any additional syntactic noise).
You could make that work by doing something like:
let debtData =
query {for x in ctx.``[dbo].[Test$USDebt]`` do
select (series ["Year" => x.Year, "Debt" => x.Debt]) }
|> Frame.ofRows
.. but then, this makes the syntax uglier... But perhaps there is some better way for attaching the names to the results?
from sqlprovider.
Does what I suggested not solve that problem then? You'd just not use reflection in this case but extract the labels from the expected tuple instead ?
Analyzing of the select statement is already happening to work out which columns to select, if you wanted to go down that route I'd suggest some custom version of the provider rather than trying to return another IQueryable and doing the work afterwards. (Saying that, analysing the projection tree is pretty tough to get it working in all cases, as it can be literally any F# expression of any size. And the various linq-generated tuples from earlier in the query are hard to disambiguate from user defined ones. I'm pretty sure what I have done will break in certain crazy circumstances ;) )
from sqlprovider.
I think Tomas is saying all the work would happen on the deedle side, for any IQueryable input where the expression tree has enough expression content to allow him to fetch inside and infer some labels. It seems like a nice idea.
from sqlprovider.
Yes - I understand that. It's just that exact same work is already being performed inside the provider, and to get the select to return an IQueryable would require a custom change anyway (and likely not a simple one!). Therefore, this seems overkill when we could simply return the labels directly (and still transparently with no syntax noise) and not have to have Deedle mess about with expression tress - I am probably missing something :)
I'd like to see a prototype!
from sqlprovider.
Deedle's pretty print for data frames would be a killer feature for me, e.g.
/// Pretty print
let print (xs:seq<'a>) = (Deedle.Frame.ofRecords xs).Print()
query { for row in ctx.``[database].[table]`` do
select (row.Field1, row.Field2) } |> print
Which prints the results in a highly readable tabular format:
Item1 Item2
0 -> A 6f8f8022-9997-49ce-b7dd-cf6fc2885c0b
1 -> B 014c05ac-4c03-40e9-a141-f348064bbb3b
I think it would be nice if we could either:
- find a way for Deedle to get the column names
- or perhaps more simply provide a pretty print function for query results in the SQLProvider library
from sqlprovider.
We could get ctx.``[database].[table]``|> Frame.ofObjects
to work quite easily if the type representing row implemented IDictionary<string, obj>
or something like that.
It is not built in yet, but the following does the trick and could be added to Deedle:
let ofObjects objects =
[ for o in objects -> series ["?" => o]]
|> Frame.ofRowsOrdinal
|> Frame.expandAllCols 1
|> Frame.mapColKeys (fun k -> k.Substring(2))
Then you can write:
> [ for i in 0 .. 10 ->
dict ["Number", i; "Sqare", i*i] ]
|> ofObjects
Which prints:
val it : Frame<int,string> =
Number Sqare
0 -> 0 0
1 -> 1 1
2 -> 2 4
This relies on the fact that the objects implement IDictionary
, which is not the case for tuples. I still think doing this for tuples would be doable (by analyzing the IQueryable
returned by query { .. }
) but it is harder (and ugly).
from sqlprovider.
I like the IDictionary idea for integration and raise you System.Dynamic.DynamicObject which would allow queries (albeit without types) to be consumed from IronPython, VB.Net, C#, etc. as well as Deedle.
from sqlprovider.
Great ideas for both IDictionary and DynamicObject, both of these should be fairly easy to add to the SqlEntity, as it is essentially just a dictionary.
Also +1 for having a decent pretty print for normal FSI use with just the SQL provider - whilst you can easily 2 way databind to a grid in a ~4 lines of code it would be nice to have a better FSI experience.
from sqlprovider.
@ptrelford I await your pull request :)
from sqlprovider.
Related Issues (20)
- [Microsoft,System].Data.SqlClient is not supported on this platform HOT 17
- Issue with connecting to PostgreSQL database HOT 1
- MSSQL stored procedures does not correctly works with schemas
- SQLProvider + PostgreSql works with Rider and VSCode but not visual studio 2022
- Incorrect FROM statement is produced when a table name contains periods HOT 2
- [Question] How to use Pragma with SQLITE? HOT 6
- Simple group by query with postgresql causes doubling up of double quotes when using double quoted declared fields HOT 3
- OnConflict implementation for SQLite provider uses wrong feature of SQLite HOT 3
- `SqlRuntime.DataContext.addCache` timeout on first query when application starts HOT 8
- Auto-generate types by `dotnet publish` time HOT 3
- dotnet 8.0.100-preview.6.23330.14 error. HOT 12
- Performance in SQL Server when comparing `varchar` column against `string` parameter HOT 23
- Is it possible to pull down a partial row and then update it? HOT 1
- Migration guide to 1.3.x HOT 3
- Typeprovider not working when compiled with dotnet build HOT 11
- Could not load file or assembly 'Microsoft.Extensions.Logging.Abstractions, Version=8.0.0.0 HOT 24
- OnConflict.Update ignores None/ValueNone values when using DatabaseProviderTypes.POSTGRESQL
- SQLite Provider using Microsoft.Data.Sqlite library does not perform updates transactionally HOT 7
- SQLite provider ResolutionPath fail HOT 8
- Support for DuckDB HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlprovider.