Table valued functions C#, SQL, CLR

[normmy]

Hi all,

I am having some difficulty in finding how to make a Table Valued Function (TVF) work in C# and T-SQL. I have managed to get a Scalar Valued Function (SVF) working perfectly however I have not been able to find any adequate documentation on how to implement a TVF.

I am not a fluent C# developer, so please forgive any ignorance on my part - I have only used C# to fulfil the needs of my current project.

Essentially I think I need to learn how to return the data from my C# method correctly, I thought it would be as simple as returning the data as a data table;

DataTable SearchResults = new DataTable();
SearchResults.Columns.Add("EventID", typeof(int));
SearchResults.Columns.Add("PatientID", typeof(int));
SearchResults.Columns.Add("OverallScore", typeof(double));

Evidently not as when trying to create the T-SQL side of this I get the following error message.

Msg 6551, Level 16, State 2, Procedure fncParsePatients, Line 1
CREATE FUNCTION for "fncParsePatients" failed because T-SQL and CLR types for return value do not match.

using the following T-SQL code to do it

create FUNCTION fncParsePatients(@NhsNo_in nvarchar(256), @CaseNo_in nvarchar(256), @Sex_in nvarchar(256), @Dob_in nvarchar(256), @FirstName_in nvarchar(256), 
@FamilyName_in nvarchar(256), @FamilyName2_in nvarchar(256), @Postcode_in nvarchar(256)) RETURNS table(EventID bigint, PatientID bigint, OverallScore float)
AS EXTERNAL NAME Linkage.RecordLinkage.ParsePatients

GO

My C# code cycles through a list of objects calculating the values to be returned and building the result set.

foreach (Patient patient in patients)
            {
                //initialise patient.OverallScore
                patient.OverallScore = 0;

                //NHS Number
                patient.OverallScore = patient.OverallScore + ReturnComparisonWeight(patient.NhsNo, NhsNo_in, nhs_m_prob, nhs_u_prob, nhs_mincompscore);

                //Case Number
                patient.OverallScore = patient.OverallScore + ReturnComparisonWeight(patient.CaseNo, CaseNo_in, case_m_prob, case_u_prob, case_mincompscore);

                //Sex
                patient.OverallScore = patient.OverallScore + ReturnComparisonWeight(patient.Sex, Sex_in, sex_m_prob, sex_u_prob, sex_mincompscore);

                //Dob
                patient.OverallScore = patient.OverallScore + ReturnDateComparisonWeight(patient.Dob, Dob_in, dob_m_prob_day, dob_u_prob_day, dob_m_prob_month, dob_u_prob_month, dob_m_prob_year, dob_u_prob_year, dob_max_daysbefore, dob_max_daysafter);

                //FirstName
                patient.OverallScore = patient.OverallScore + ReturnComparisonWeight(patient.FirstName, FirstName_in, FirstName_m_prob, FirstName_u_prob, FirstName_mincompscore);

                //FamilyName
                //Return only the maximum result of FamilyName test - all combinations of FamilyName, FamilyName2, FamilyName_in, FamilyName2_in
                patient.OverallScore = patient.OverallScore +
                Math.Max(ReturnComparisonWeight(patient.FamilyName, FamilyName_in, FamilyName_m_prob, FamilyName_u_prob, FamilyName_mincompscore), Math.Max(ReturnComparisonWeight(patient.FamilyName, FamilyName2_in, FamilyName_m_prob, FamilyName_u_prob, FamilyName_mincompscore), Math.Max(ReturnComparisonWeight(patient.FamilyName2, FamilyName_in, FamilyName_m_prob, FamilyName_u_prob, FamilyName_mincompscore), ReturnComparisonWeight(patient.FamilyName2, FamilyName2_in, FamilyName_m_prob, FamilyName_u_prob, FamilyName_mincompscore))));

                //Postcode
                patient.OverallScore = patient.OverallScore + ReturnComparisonWeight(patient.Postcode, Postcode_in, Postcode_m_prob, Postcode_u_prob, Postcode_mincompscore);

                //Patient overall score as a percentage
                patient.OverallScore = ((patient.OverallScore / maxPossResult) * 100.00);

                //insert into results table
                SearchResults.Rows.Add(patient.EventID, patient.PatientID, patient.OverallScore);
            }

            return SearchResults;

Is there a simple form that I can put my results in that can be accepted by the T-SQL function declaration or am I missing something more fundamental?
Apologies again for my lack of C# understanding and thanks for any help you can give me.

Normm