User Defined Functions do not generate

[GoogleCodeExporter]

When generating , the output does not contain any UDF's that were in the 
database.

This has limitations . 

Workaround 
Generate the DBML  in Plinqo. Create a seperate Linq to SQL project and 
drag all of the desired functions to the designer. 
Open the DBML in the linq to SQL project in a XML editor and copy all of 
the functions.
Then open the Plinqo generated DBML and past in the function XML.
You can then generate the Queries and Entities.

This causes to many manual steps

Original issue reported on code.google.com by steveatc...@gmail.com on 19 Oct 2009 at 8:40

[GoogleCodeExporter]

Original comment by shannon....@gmail.com on 21 Oct 2009 at 3:10

• Changed state: Accepted
• Added labels: Framework-PLINQO

[GoogleCodeExporter]

We have updated PLINQO to support user defined functions.  It is included in 
the latest 
build and will be a part of the next release.  If you want to download and try 
it out, 
please go to the nightly build section at http://plinqo.com/download.ashx.

Original comment by shannon....@gmail.com on 27 Oct 2009 at 5:22

• Changed state: Fixed

[GoogleCodeExporter]

Does this require Anything special to Work ?

Original comment by steveatc...@gmail.com on 2 Nov 2009 at 6:04

[GoogleCodeExporter]

We are not getting any UDFs generated with the 10/29 nightly and codesmith 5.1.6

Original comment by steveatc...@gmail.com on 2 Nov 2009 at 6:19

[GoogleCodeExporter]

I apologize.  I failed to mention this requires the latest build of CodeSmith 
as well.  
We are planning to release them at the same time.

http://codesmithtools.com/download/builds/codesmithprofessional-51-build.msi

Original comment by shannon....@gmail.com on 2 Nov 2009 at 7:34

[GoogleCodeExporter]

Sannon, OK, Thanks

Before I start testing this. Can you tell me what the table functions are 
returning: 
Table function defined "Any UDF that returns entities" as opposed to intrinsic 
data 
types

are they returning   System.Linq.IQueryable or System.Data.Linq.ISingleResult

I hope the former.

Original comment by steveatc...@gmail.com on 2 Nov 2009 at 8:11

[GoogleCodeExporter]

I apologize.  I gave you the wrong build link.  It should be 
http://codesmithtools.com/download/builds/codesmithprofessional-52-build.msi

The user defined functions should work pretty similarly to the way the stored 
procedures work.  

ISingleResult result = context.ReturnResultSet();
List<User> users = result.GetResult<User>().ToList();

Original comment by shannon....@gmail.com on 2 Nov 2009 at 11:27

• Added labels: Type-Enhancement

[GoogleCodeExporter]

This is not working on table value functions. Below is code Plinqo is 
generating and 
below that is working code:

 [System.Data.Linq.Mapping.Function(Name="OLTP.UFN_GET_ENROLL_PART_A")]
        public System.Data.Linq.ISingleResult<ORMBuild.Data.UfnGetEnrollPartAResult> 
UfnGetEnrollPartA(
            [System.Data.Linq.Mapping.Parameter(Name="@PERSONID", DbType="bigint")] 
long? personid)
        {
            var methodInfo = (System.Reflection.MethodInfo)
System.Reflection.MethodInfo.GetCurrentMethod();
            var result = this.ExecuteMethodCall(this, methodInfo, personid);

            return 
((System.Data.Linq.ISingleResult<ORMBuild.Data.UfnGetEnrollPartAResult>)
(result.ReturnValue));
        }

WORKING CODE:
It needs to be similar to this:

[System.Data.Linq.Mapping.Function(Name="OLTP.UFN_GET_ENROLL_PART_A", 
IsComposable=true)]
        public System.Linq.IQueryable<Bees.Data.Ods.UfnGetEnrollPartAResult> 
UfnGetEnrollPartA(
            [System.Data.Linq.Mapping.Parameter(DbType="BigInt", Name="PERSONID")] 
long? personid)
        {
            var methodInfo = (System.Reflection.MethodInfo)
System.Reflection.MethodInfo.GetCurrentMethod();
            var result = this.ExecuteMethodCall(this, methodInfo, personid);

            return ((System.Linq.IQueryable<Bees.Data.Ods.UfnGetEnrollPartAResult>)
(result.ReturnValue));
        }

Original comment by steveatc...@gmail.com on 3 Nov 2009 at 4:16

[GoogleCodeExporter]

References:
http://msdn.microsoft.com/en-us/library/bb386954.aspx

Original comment by steveatc...@gmail.com on 3 Nov 2009 at 4:24

[GoogleCodeExporter]

It appears that the L2S ExecuteMethodCall returns an IQueryable for table value 
UDFs. When we tweaked the Context.Generated.cst template to recognize our UDF's 
by 
naming convention and changed the return type then we don't get the cast error 
we 
otherwise do. Here's the mod we made to the template's GetReturnType method:

public string GetReturnType(Function function)
{
    StringBuilder s = new StringBuilder();

    if (function.Types.Count == 0)
        s.Append(CSharpAlias[function.Return.Type]);
    else if (function.HasMultipleResults == true)
        s.Append("System.Data.Linq.IMultipleResults");
    else if (function.Method.StartsWith("Ufn"))
        s.AppendFormat("System.Linq.IQueryable<{0}>", GetResultName(function));
    else
        s.AppendFormat("System.Data.Linq.ISingleResult<{0}>", GetResultName
(function));

    return s.ToString();
}

Perhaps this is because a UDF table value can be embedded in a SQL query like a 
sub 
query whereas the result of s sproc cannot. So something in the translation 
layer in 
L2S recognizes that so that the use of the UDF in a query will translate into 
the 
expression tree as part of the SQL emitted.

Original comment by tyler.je...@gmail.com on 3 Nov 2009 at 1:14

[GoogleCodeExporter]

Original comment by shannon....@gmail.com on 3 Nov 2009 at 4:15

• Changed state: Started

[GoogleCodeExporter]

A quick note: The above works great for UDF's starting with Ufn. 

Also: I would hope for the long term there is a way to determine that the 
function
type is a UDF, then  is it a table-valued UDF , or a scalar valued UDF etc

Original comment by steveatc...@gmail.com on 3 Nov 2009 at 4:20

[GoogleCodeExporter]

this should be fixed in the latest build.

Original comment by paul.wel...@gmail.com on 4 Nov 2009 at 9:01

• Changed state: Fixed

[GoogleCodeExporter]

Paul,

quick question, can you define Latest build ?

Is this Plinqo templates and CS ?

from this location for CS
http://codesmithtools.com/download/builds/codesmithprofessional-52-build.msi

And daily on the Plinqo Tempates

Original comment by steveatc...@gmail.com on 4 Nov 2009 at 9:14

[GoogleCodeExporter]

You should just need the latest PLINQO build from here:
http://community.codesmithtools.com/nightly/plinqo/

Original comment by ejsm...@gmail.com on 5 Nov 2009 at 3:43

[GoogleCodeExporter]

Original comment by shannon....@gmail.com on 30 Dec 2009 at 5:43

• Added labels: PLINQO4.0

[GoogleCodeExporter]

Original comment by shannon....@gmail.com on 30 Dec 2009 at 6:04

• Added labels: PLINQO-4.0
• Removed labels: PLINQO4.0

[GoogleCodeExporter]

Original comment by shannon....@gmail.com on 30 Dec 2009 at 9:16

• Added labels: version-4.0
• Removed labels: plinqo-4.0