There is another thread that is related: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9407

However, I wanted to toss this concept around a bit more.

<Campaigning>

Table Return Type

I have a user defined function like this:

CREATE FUNCTION [dbo].[Udf_CompaniesInRoles_GetCompanies]
(
    @ApplicationName  nvarchar(256),
    @UserName        nvarchar(256),
    @RoleName        nvarchar(256)
)
RETURNS 
@CompanyList TABLE 
(
   CompanyID int,
   CompanyName varchar(45)
)
AS.............

That UDF could be a stored proc, but the results of the UDF are further filtered, sorted, etc from other places, so this kind of UDF works best. This UDF actually is a modified version of the roles functionality in aspnetdb. It checks everything, looks up all parameters, etc - much like the aspnetdb procs that ship with asp.net.

In order to get this to work with LLBLGen, I did this:

ALTER PROCEDURE [dbo].[CompaniesInRoles_GetCompaniesForUserRole]
    @ApplicationName  nvarchar(256),
    @UserName        nvarchar(256),
    @RoleName        nvarchar(256)
AS
BEGIN
    select * 
    from dbo.Udf_CompaniesInRoles_GetCompanies(@ApplicationName, @UserName, @RoleName)
    order by CompanyName
END

Then, I of course just call the stored proc. In short, I don't like it.

When I start having to code extra layers in the database to get around limitations in my LLBL Generator, a 'red light' goes off. The above works, and I have done what I have to do to use your product - as I love it - but I would like some help in this area.

You can determine exactly what columns and types these return using INFORMATION_SCHEMA.Routine_Columns and therefore, these could be treated exactly like a view from LLBLGens perspective (regarding making relationships, etc).

Scalar Return Type I am hand coding scalar UDFs also!
I have uploaded some templates and such that generate helper routines for calling functions. Even with that, I end up writing something like this for every function: (SqlFunctionFactory and CachedDbFunctionCall are from my template)

    public partial class SqlFunctionFactory
    {

        public static IExpression SomeUDF_GetSomeValue(int companyId)
        {
            return new CachedDbFunctionCall("dbo", "SomeUDF_GetSomeValue", new object[] { companyId});
        }
.....
    public partial class SqlFunctionCalls
    {
        private SqlFunctionCalls()
        {
        }


        public static bool SomeUDF_GetSomeValue(int companyId, ITransaction transaction)
        {
            return (int)SqlFunctionFactory.GetScalar(SqlFunctionFactory.SomeUDF_GetSomeValue(companyId), transaction);
        }

The functions are in the schema, the parameters are in the schema, but I still have to do the above.

Microsoft SQL Server is probably a very popular database for users of LLBLGen and UDFs are not going to go away - so, what I am campaigning is for full support of them!

What if anything can we do/plan/etc for future versions to include support for things like this?

</Campaigning>

You can't call them directly, so you always have to wrap support for them in some SQL query. That's OK at some point, scalar functions are supported via DbFunctionCall, but they're not first class typed citizens because they're most of the time used in context of other SQL. So it's a little useless to wrap them into call code. (IMHO).

What I find a little contradicting is that you write a table function, but you refuse to write the wrapper proc to call it from the outside . Why is it acceptable to have a table function in hardcoded sql, but not a proc which makes it possible to call it?

Mapping entities onto a resultset is fragile (the resultset changes, and your whole app is shaky and you'll find that out at runtime. This is easy to do as the resultset is defined inside the select statement of the function). The drivers don't have meta-data for functions as well, because there's not that much the designer can do with them.

You can't call them directly, so you always have to wrap support for them in some SQL query. That's OK at some point, scalar functions are supported via DbFunctionCall, but they're not first class typed citizens because they're most of the time used in context of other SQL. So it's a little useless to wrap them into call code. (IMHO).

You need to wrap them unless you want the name of the function hard coded everywhere, you need them wrapped, much like I have done above.

What I find a little contradicting is that you write a table function, but you refuse to write the wrapper proc to call it from the outside . Why is it acceptable to have a table function in hardcoded sql, but not a proc which makes it possible to call it?

The reason is, I should not have to write a wrapper proc to call it from the outside!! In ado.net, I can just execute "select * from dbo.Udf_CompaniesInRoles_GetCompanies('appname', 'user', 'something')". I do not need a wrapper proc - unless I am using your product, which does not support this.

Mapping entities onto a resultset is fragile (the resultset changes, and your whole app is shaky and you'll find that out at runtime. This is easy to do as the resultset is defined inside the select statement of the function).

Wow - I am really confused. You map entities onto a resultset in everything you do! A view is a resultset and you map an entity on that. That is no different that mapping an entity on a function that returns a table.

The drivers don't have meta-data for functions as well, because there's not that much the designer can do with them.

The drivers could load the meta-data for functions (it is all there) - and it could work like a view, except the parameters to the function would have to be specified in the fetches.

Again - this is not going to go away as the largest database on the market. I fail to see why you do not think this is a big deal to your MS SQL users.

WayneBrantley wrote:

What I find a little contradicting is that you write a table function, but you refuse to write the wrapper proc to call it from the outside . Why is it acceptable to have a table function in hardcoded sql, but not a proc which makes it possible to call it?

The reason is, I should not have to write a wrapper proc to call it from the outside!! In ado.net, I can just execute "select * from dbo.Udf_CompaniesInRoles_GetCompanies('appname', 'user', 'something')". I do not need a wrapper proc - unless I am using your product, which does not support this.

And why is it a UDF and not a stored proc? The answer is why I said it is mostly used with other SQL.

Mapping entities onto a resultset is fragile (the resultset changes, and your whole app is shaky and you'll find that out at runtime. This is easy to do as the resultset is defined inside the select statement of the function).

Wow - I am really confused. You map entities onto a resultset in everything you do! A view is a resultset and you map an entity on that. That is no different that mapping an entity on a function that returns a table.

No I don't map onto a resultset, I map onto a db element and FORMULATE the query manually. I know what the code does, Wayne. A view is a fixed resultset, you can't control the resultset's columns based on a parameter like in a proc.

The main difference between a view and your function is input parameters. A target is a db element which is used on its own, without input parameters.

So let's get to a real example: fetch all Companies given the role, and appname, like you defined above. How would you formulate that in C# with LLBLGen Pro constructs? No, you can't use predicates, as you're calling a function as the target.

THAT's why this is a moot point.

OF COURSE you can formulate a RetrievalQuery object and fetch the data with a projection. That's not a big deal, however using a function as the target of an entity has fundamental problems which are unsolvable.

The drivers don't have meta-data for functions as well, because there's not that much the designer can do with them.

The drivers could load the meta-data for functions (it is all there) - and it could work like a view, except the parameters to the function would have to be specified in the fetches.

Yes, and how are you going to do that. ?

OrderCollection orders = c.Orders; oops, I have to specify the parameters somewhere... where? It's natural to do that in a predicate, but that's ending up in a where (naturally) and not in the function call.

Again - this is not going to go away as the largest database on the market. I fail to see why you do not think this is a big deal to your MS SQL users.

We also don't offer mapping entities on retrieval procs. Same reason (and another one: the resultset can change based on a parameter, which makes it very fragile). I never saw you making a big deal out of that. Btw, DB2 is the largest database, then oracle.

The function dictates the resultset. True, this is similar to a view, which is also less robust, but at least it doesn't require parameters to be used, so it can be used (but potentially is also a problem, if there's no true PK definable on the resultset of the view). A function has another problem with the parameters.

I know what the code does, Wayne.

I did not mean to imply that, it just seems (perhaps from misunderstandings of mine) that based on your answers I am not communicating clearly and 'missing something'. You are the man!

A view is a fixed resultset, you can't control the resultset's columns based on a parameter like in a proc.

The same is of course true of a UDF. A table returning UDF is a fixed resultset, you cannot control the resultset's columsn based on a parameter like in a proc.

The main difference between a view and your function is input parameters. A target is a db element which is used on its own, without input parameters.

Yep, that is the bottom line difference.

So let's get to a real example: fetch all Companies given the role, and appname, like you defined above. How would you formulate that in C# with LLBLGen Pro constructs? No, you can't use predicates, as you're calling a function as the target.

CompanyCollection c = new CompanyCollection(); .... c.GetMulti(applictionName, UserName, RoleName, predicate, other standard parameters);

however using a function as the target of an entity has fundamental problems which are unsolvable.

I guess it is the lack of this level of detail undetstanding with the inner workings of the product, that makes me not understand this.

Yes, and how are you going to do that. ?

OrderCollection orders = c.Orders; oops, I have to specify the parameters somewhere... where? It's natural to do that in a predicate, but that's ending up in a where (naturally) and not in the function call.

Ok....I concede that it probably cannot work like a view, where you can create relationships and mappings onto other entities, without major changes/work/etc. However, I would then move to couldn't it be made like a TypedView with a Fill method like the GetMulti from above?

We also don't offer mapping entities on retrieval procs. Same reason (and another one: the resultset can change based on a parameter, which makes it very fragile). I never saw you making a big deal out of that.

I conceded the resultset can change - sot that does make it fragile...and it was way back in my beginning days with your product, where I did want this feature! I am trying to be consistent http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5622

Btw, DB2 is the largest database, then oracle.

I guess that depends on who is counting and what they are counting.

<edit> BTW, arent you going to need this for Linq stuff anyway? It appears it is supported there: http://blogs.msdn.com/digital_ruminations/archive/2006/06/13/629121.aspx </edit>

The DLinq support for it is like the retrieval proc support we have now, so that's not usable for entities. Their examples are the same as what you can do with a retrievalquery object which fetches the UDF and which is fetched as a projection.

The parameters stay a problem. To me it's just a retrieval proc, and it might be in the future that we might obtain the meta-data for functions as well, but I don't have a lot of hopes for that. (so it will be that you have to specify that line yourself). Main problem is that often databases make no distinction between the gazillion system functions they have and the few UDFs.

So if you want to have support for this, I'd go the RetrievalQuery + projection route. In the future (read: v3) this will be declarative (definable), so meta-data retrieval isnt the sole source of info anymore

Well, that might help.

Are there technical difficulties in making it a TypedView, where the Fill method just has additional parameters for the parameters of the function??

WayneBrantley wrote:

Well, that might help.

Are there technical difficulties in making it a TypedView, where the Fill method just has additional parameters for the parameters of the function??

Yes, the main thing is the variable # of parameters, so it has to have another overload of the one with the most parameters and add additional parameters to that which are then passed to the function.

Pretty awkward syntaxis IMHO, especially because the predicates etc. aren't needed.

Pretty awkward syntaxis IMHO, especially because the predicates etc. aren't needed. 

You can still use the predicates, grouping, sorting, everything. So, it would be a little awkward due to the number of function parameters, but not near as awkward as me having to hand code/type everything!

What I would ask is for you to consider the following:

• Allow functions (both scalar and table) to be loaded into the metadata.
• Alter the MSSQL driver to load that metadata.

This would at least allow me to make a template to generate the code I am asking for. In this template, I would:

• Create functions to handle all of the scalar calls, so that I do not have to code them and so the 'function text' is in one place.
• Attempt to modify typedlist implementation and replace the Fill() function with one that starts with the parameters to the function. Replace the 'viewname' with the 'functionname and parameters'. Try and leave everything else alone in the typedlist.

As it is now, I am basically stuck handcoding all of these function related things, when the metadata is there and I could just generate it.

WayneBrantley wrote:

Pretty awkward syntaxis IMHO, especially because the predicates etc. aren't needed. 

You can still use the predicates, grouping, sorting, everything. So, it would be a little awkward due to the number of function parameters, but not near as awkward as me having to hand code/type everything!

Creating a projection from a function fetch isn't that much code IMHO.

What I would ask is for you to consider the following:

• Allow functions (both scalar and table) to be loaded into the metadata.
• Alter the MSSQL driver to load that metadata.

This would at least allow me to make a template to generate the code I am asking for. In this template, I would:

• Create functions to handle all of the scalar calls, so that I do not have to code them and so the 'function text' is in one place.
• Attempt to modify typedlist implementation and replace the Fill() function with one that starts with the parameters to the function. Replace the 'viewname' with the 'functionname and parameters'. Try and leave everything else alone in the typedlist.

As it is now, I am basically stuck handcoding all of these function related things, when the metadata is there and I could just generate it.

Still, you haven't answered the question why the UDF isn't a stored proc.

Creating a projection from a function fetch isn't that much code IMHO.

...........frustrated........neither is writing the code to call a stored proc. I could create the parameters, construct a call to proc, etc with about the same amount of code to do the above. But you HAVE to have this in your product as it would be completely silly to have to write a bunch of code to call a stored procedure.

Still, you haven't answered the question why the UDF isn't a stored proc.

Sorry about that. The reasons they are functions varies. For scalar return functions, I generally make them functions if I want to use them in views or directly in some query. When they are table return values, I generally make them functions if I need very 'general' functionality, but then want to further filter or sort that.

Of course, with stored procs you cannot do that. In my specific example, suppose I wanted to look for a certain company in the result set? I could do that with a where clause on the function call. Suppose I wanted to sort the results differently - I could do that with a sort clause! Suppose I wanted to JOIN the results of that with the Company table - I could easily do that. You cannot do that with a stored proc on any of the above. You could write 10 stored procs - each with the exact same code with a bunch of different parameters to achive all the results you might want.

It is not my intent to debate with you about these things. I am sure you are well aware of the need for function support in MSSQL. You may not be aware of the value of functions over stored procedures in many circumstances - but there definately are, which is why they were put in the SQL Server product years ago.

This is a real need and I would just like consideration on at least including the data in the meta-data where I can generate what I need. Why not use LLBLGen to to generate code from meta-data so that I do not have to write it?

My summary from the above conversations, you believe:

------- It is ok/good to manually code things like this anywhere you need them - instead of having them generated with wrappers using the metadata provided in the database:

fields(3).ExpressionToApply = New DbFunctionCall( "YEAR", New Object() { OrderFields.OrderDate } ) fields(4).ExpressionToApply = New DbFunctionCall( "dbo", "fn_CalculateOrderTotal", New Object() { OrderFields.OrderId, 1 } ) fields(5).ExpressionToApply = New DbFunctionCall( "dbo", "fn_CalculateOrderTotal", New Object() { OrderFields.OrderId, 0 } )

instead of:

fields(3).ExpressionToApply = SqlFunctionCalls.Year("YEAR", OrderFields.OrderDate ) fields(4).ExpressionToApply = SqlFunctionCalls.fn_CalculateOrderTotal(OrderFields.OrderId, 1) fields(5).ExpressionToApply = SqlFunctionCalls.fn_CalculateOrderTotal(OrderFields.OrderId, 0)

------In order to have support for table valued functions - It is ok/good to include a manually constructed 'text' SQL clause complete with where and order by sections to get a RetrievalQuery and then project that onto a class.

As opposed to just using the existing infrastructure provided by TypedViews and the predicate system to have the system construct them from the meta-data provided by the database.

Quite simply - I disagree with these two 'perceived positions; from above.

I guess I would have to get you to spend more time with functions and their features and benefits - PLUS be stuck having to use some existing ones from LLBLGen to understand the real need.

Just to be sure - all the above is just a conversation - I do not mean for it to come off as an attack or to have a bad attitude. I would like to thank you for your time on the above topic/conversation and would just like it to be put back on the table for consideration in the future. I do love the product and it does just about anything you can imagine and does it well. I just feel it falls short in this area.

I'll add a research item to the v3 todo list for this feature. I'm currently not convinced it will add any value, but perhaps my views change in the couple of months to come

There is a nice way to use table valued UDFs as a filter for an entity collection (even though it's a bit of a hack). It allows using a table valued UDF to retrieve entity collections through making a INNER JOIN between a table and the UDF (which contains the filtered primary key id's of the table).

Here some quick sample code: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11573&StartAtMessage=0&#64617

Hope it helps somebody. All the best, Patrick

Cool. Thanks.