Accessing Table-Valued Functions?

[tforkmann]

Is there a way to access a table-valued function with the SQL Provider?

[colinbull]

Not that I am aware of but I think @pezipink was looking at this, maybe?

[pezipink]

ah, sorry, I missed this one. Right now we do not support table valued parameters for functions or stored procedures in SQL server (or any UDTs). It is possible, but we have not done it yet.

[TheJayMann]

I am having this issue too, and, just to clarify, It's not about supporting table valued parameters (which would also be nice), rather, it's about being able to access functions which return a table value rather than a scalar value. I've also noticed that, while stored procedures are accessible, they return 'untyped' result sets rather than result sets with columns as properties. I've noticed the MapTo method, and that, along with using stored procedures, is an acceptable workaround for now, but it would be nice to use functions which return table values (which can be further composed on the database side), assuming there is time and ability to get this done.

[pezipink]

(Table valued things aside...)

The original version of the SQL TP did indeed attempt to infer the results of sprocs, by using the usual trick. The problem is, given that sprocs can return many sets of data, and each one can differ completely depending on its logic in combination with the input parameters, it makes it relatively impossible to reliably infer types - this is a problem with sprocs in general, and is the reason you can't get metadata for them. SQL Server 2012 has some new bits in it that allow you to get some more metadata about sprocs, but it still doesn't work any better for anything more than simple ones, and most of the other databases don't even have that.

Because of this we decided to drop support for inferred types and force the users to specify their own types. This is something we could perhaps allow the user to select if they wanted inferred sproc types, as a static parameter, that might be a useful thing ? What do you think?

[TheJayMann]

I figured something like that might have been the reason that stored procedures are untyped, and why I was happy to use stored procedures with the MapTo method to convert the results to a record type. If I had any complaint about that, it would be that MapTo appears to only allow me to alter the value and not the column name before assigning them to properties. But, as long as I am the one authoring the stored procedure, this isn't a very large problem.

As far as the value of inferring types from stored procedures, I can see it being useful in some cases, especially in the case where someone is trying to sell teammates on the idea of how simple data access can be by using F# (which is half of the reason I'm using this project). However, I believe there are too many cases where cleverly written stored procedures would likely cause unexpected results with the inferred types. We even have some stored procedures that were written to write their own sql queries which then get executed, a sort of meta stored procedure.

Rather, the simple cases where stored procedures can easily have an inferred type could be represented even easier as a table valued function (that is, a function that returns a table, not necessarily one that takes a table). This is because a table valued function actually describes the table format it will return, and thus cannot return data in a different format. Such functions, once they are supplied with the appropriate parameters, can then be treated just like tables, including being further used in queries, as, any place a table is allowed to be used, a function with supplied parameters can be used. For example, "SELECT Value1, Value2 FROM MyTableFunction(42) WHERE Value3 > 5" is completely valid, where MyTableFunction is a table valued function with at least the columns Value1, Value2, and Value3. Even better are the inline table valued functions, as they perform very well when being further composed, as they are essentially views which take parameters, and don't require a temporary table to be filled in before being further processed.

I am speaking about this with decent knowledge in MSSQL, but with little knowledge in MySQL, SQLite, or any of the other database protocols supported, and I don't know how or if such concepts can be translated to them.

[prosconi]

+1

[FranciscoMontanez]

Hi @pezipink, I've been looking forward to accessing table-valued functions. I see your comment stating this is possible. Do you have any insights as to how this could be implemented, or any docs that you could point me to that could help me get started?

[torbonde]

I'm also curious about the possibility of adding support for table-valued functions. Not having worked with type providers in general or this project in particular, is there any way I can help? Or any way someone could help me get started on this?

[torbonde]

@Thorium, sorry to pull you into this, but I noticed you are active on this repository. Do you know which challenges needs to be overcome, in order to be able to use table valued functions through the SQLProvider?

Information about input parameters and output table is quite easy to find - at least in MSSQL - so I guess generating the relevant types should also be relatively simple. Similar with identifying names/schemas for table valued functions. However, I have no idea how to hook this up in the project. If you have any ideas/pointers on how to start this off, I'd be happy to give it a go.

[Thorium]

I've never needed those, so I'm not an expert here. Is there corresponding things for Postgres and MySQL?

If you want to just have the ability to call this functions, then the easies place is would be adding a new method to ISqlProvider interface like there is now for the Stored Procedures __.ExecuteSprocCommand.

But if you want to be able to do F# query syntax and join these to views and tables, then the better idea would be extending the current __.GetTables etc information_schema-queries, and to SqlDesignTime. It's more work, but the plus-side of this approach is that you wouldn't need to decide per provider everything, just implement the providers you need. If we now have ctx.MyTable, would the best be generating those as methods rather than properties, but the same level the tables are generated now: ctx.MyTablevalued(123) ?

I will accept PRs if you get something useful done. :-)

[torbonde]

I haven't really used PostgreSQL or MySQL, but a quick googling tells me PostgreSQL supports table valued functions, while MySQL doesn't. Is that an issue?

In order for it to be useful to me, I'd like to be able to use it exactly like a table or view, with a couple of differences, such as

1. It accepts parameters (like stored procedures)
2. It can't be inserted into, updated or deleted from (like views, I guess)

The restriction to be able to use it like a table or view (e.g. joins) speaks towards extending the __.GetTables as you suggest, to ensure the table-types are generated. But I would need to flag them, to make sure that CRUD-operations aren't generated for them, in order to align with 2.

At the same time, in order to be able to generate them as methods, I would need to carry the parameter-information as well, which makes it look very much not like a table. Of course, I can extend whatever type __.GetTables returns, to have parameters (empty list for tables/views), but that doesn't really seem like the best solution.

[pezipink]

I am far out of touch with this project these days. My original thoughts were that this should be generalized as a UserDefinedTypes concept.

• Query UDT metadata lazily when unknown types are encountered in functions, sprocs, etc and add them to the entity metadata and ProvidedTypes (or perhaps they have their own cache).
• Extend functions / sprocs to be able to accept such a type as an input; The return types you should get mostly for free with the existing mechanics
• There'll be some LINQ work to accept passing or instantiation of the types as input and have them properly translate to SQL, plus accepting them in the correct places (eg, join, qurery source .. ). This will probably be the hard bit.
• Actual SQL gen work for the various providers that can sensibly support them

If the metadata and provided types are separated from the tables, it will be easy to not include the CRUD methods.

I am sure @Thorium will correct me on the things that changed and / or I have forgot :)

[torbonde]

@pezipink I think the solution you propose is far bigger than what is required to resolve the current issue, which is basically to support functions returning tables. The return tables need not be predefined as a UDT. Supporting functions/SPs that accepts such UDTs as paremeter (including user-defined table types) is in my view a different issue altogether.

[pezipink]

Fair enough, though they are certainly related. It would be a shame not to support them as inputs as well, but I guess outputs is a start.

The easiest way is then to simply create a new provided type for every TVF result, even if they match an existing type in the schema

The LINQ bit might be a fair bit of work though. Once you are able to place the function in a join, you need to generate cross apply instead of join, right?

[torbonde]

Well, you can cross apply, but you can also do regular joins. Both of the below are valid (T-SQL) queries

select *
from (values (1), (2), (100)) as Vals(v)
full outer join dbo.MyFunction(1) as f on Vals.v = f.MyCol

select *
from (values (1), (2), (100)) as Vals(v)
cross apply dbo.MyFunction(Vals.v) as f