C# 3.0 in a Nutshell
C# 3.0 in a Nutshell, Third Edition A Desktop Quick Reference By Joseph Albahari, Ben Albahari
September 2007
Pages: 858

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
User Defined Table Functions - Parameter Turned Into Column Name
Terry Aney
post Nov 3 2009, 11:53 AM
Post #1


Active Member
**

Group: Members
Posts: 16
Joined: 25-March 08
Member No.: 169
When running the following LINQ (where Profiles is a table and udfGetHistoricalData is a User Defined Function):

CODE
var keys = new XElement( "l", Profiles.Take (10).Select( p => new XElement( "p", p.pKey ) ) );
udfGetHistoricalData( keys ).Dump();


The following error occurs:

Incorrect syntax near '@profileKeys'.

The problem is the SQL that is generated (leading me to believe it is L2S - however when running same function in .NET assemblies outside of LINQPad, it doesn't cause a problem, but maybe because we explicitly use only a subset of the columns?)

CODE
SELECT TOP (10) [t0].[pKey] AS [content]
FROM [Profile] AS [t0]
GO

-- Region Parameters
DECLARE @p0 Xml SET @p0 = N'<l>
  <p>2087c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2187c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2287c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2387c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2487c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2587c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2687c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2787c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2887c6f0-a9bd-dc11-b4ff-006073e5733e</p>
  <p>2987c6f0-a9bd-dc11-b4ff-006073e5733e</p>
</l>'
-- EndRegion
SELECT [t0].@profileKeys AS [profileKeys], [t0].[hdDateCreated], [t0].[hdDateUpdated], [t0].[hdConcurrency], [t0].[hdProfileKey], [t0].[hdType], [t0].[hdIndex], [t0].[hdUntypedData], [t0].[hdTypedData]
FROM [dbo].[udfGetHistoricalData](@p0) AS [t0]


Notice how it selected the first column of @profileKeys? Upon Googling, it appears by all accounts that Table functions should be supported. Do you have any suggestions?

Below is the definition for our UDF:

CODE
ALTER FUNCTION [dbo].[udfGetHistoricalData]
(
    @profileKeys xml
)
RETURNS TABLE
AS
    RETURN SELECT * FROM HistoricalData
        WHERE hdProfileKey IN ( SELECT profiles.pKey.value( '.', 'uniqueidentifier' )
        FROM @profileKeys.nodes( 'l/p' ) profiles( pKey ) )
Go to the top of the page
 
+Quote Post
JoeAlbahari
post Nov 7 2009, 04:56 PM
Post #2


Advanced Member
******

Group: Members
Posts: 215
Joined: 15-February 08
From: Perth, Australia
Member No.: 90
I suspect LINQPad is OK in its handling of table-value functions.

You can examine the typed DataContext class that it produces using .NET Reflector as follows:

Process.Start (pathToReflector, GetType().BaseType.Assembly.Location);

Let me know if you see anything wrong.

Cheers
Joe
Go to the top of the page
 
+Quote Post
Terry Aney
post Nov 9 2009, 12:34 PM
Post #3


Active Member
**

Group: Members
Posts: 16
Joined: 25-March 08
Member No.: 169
QUOTE (JoeAlbahari @ Nov 7 2009, 07:56 PM) *
I suspect LINQPad is OK in its handling of table-value functions.

You can examine the typed DataContext class that it produces using .NET Reflector as follows:

Process.Start (pathToReflector, GetType().BaseType.Assembly.Location);

Let me know if you see anything wrong.

Cheers
Joe


CODE
var members =
    from m in
    GetType().BaseType.Assembly.GetType( "LINQPad.User.udfGetHistoricalDataResult" ).GetMembers()
    where m.MemberType == MemberTypes.Field
    select new { m.Name };
members.Dump();


Result:
profileKeys
hdDateCreated
hdDateUpdated
hdConcurrency
hdProfileKey
hdType
hdIndex
hdUntypedData
hdTypedData

I'm surprised at profileKeys as a property when that is simply a parameter to the function. It is of type XElement in Reflector.
Go to the top of the page
 
+Quote Post
JoeAlbahari
post Nov 9 2009, 06:27 PM
Post #4


Advanced Member
******

Group: Members
Posts: 215
Joined: 15-February 08
From: Perth, Australia
Member No.: 90
Thanks, Terry. I've found the problem: the schema reader was filtering parameter rows with an XTYPE of 'TF' but not 'IF'.

Try this preview:
http://www.linqpad.net/preview/LINQPad.exe

Note that there's been some additional refactoring in that preview (in preparation for the upcoming provider model). Tell me if you discover any issues.

Cheers
Joe
Go to the top of the page
 
+Quote Post
anjela8841
post Yesterday, 06:52 AM
Post #5


New Member
*

Group: Members
Posts: 1
Joined: Yesterday, 06:43 AM
Member No.: 21,108
Thanks for this C# edition. I was desperate to download it.

Java courses
It Career
Go to the top of the page
 
+Quote Post
 

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

RSS Lo-Fi Version Time is now: 21st November 2009 - 08:38 PM
Powered By IP.Board © 2009  IPS, Inc
Powered by Minerva SEO IPB module