0

We have the below TVF, with a TableType input parameter. We have added the function and function import to our edmx model (database first approach). When we try to call the function using dbContext.Function the only input parameters are @radius. It seems the entity framework is not recognizing that there is a 2nd input param (the TableType). Ultimately we would like to pass a DataTable as this parameter.

CREATE FUNCTION [dbo].[fnWhatever] (
    @UserPOIList UserPOIListTableType READONLY,
    @Radius INT
)
RETURNS TABLE
AS
RETURN
    WITH AllZipsRanked
        AS (SELECT POI.UserPOIID
                   , Zip = ZipsWithinDistance.ZipCode
                   , ZipsWithinDistance.Distance
                   , RankZipDistance = ROW_NUMBER () OVER (PARTITION BY ZipsWithinDistance.ZipCode ORDER BY ZipsWithinDistance.Distance ASC) 
                           FROM dbo.UserPOI POI
                                JOIN @UserPOIList UserPOIIDs ON POI.UserPOIID = UserPOIIDs.UserPOIID 
                          CROSS APPLY mapping.fnGetZipsWithinDistanceLatLong (POI.LONGITUDE, POI.LATITUDE, @Radius) ZipsWithinDistance) 
        SELECT UserPOIID
             , Zip
             , Distance
               FROM AllZipsRanked
               WHERE RankZipDistance = 1
Yuck
  • 49,664
  • 13
  • 105
  • 135
jslumar
  • 23
  • 2
  • 6
  • I wouldn't even try. One way to go is to convert the contents of the function to a C# method containing equivalent LINQ that accepts a similar `IQueryable` instead. – Yuck Jul 30 '14 at 14:28
  • 1
    EF does not support table valued parameters. However, there are extensions that add support for SPs. You can [check this](http://stackoverflow.com/a/9837927/1119545) and see if works for TVFs as well. – Marcel N. Jul 30 '14 at 14:36

0 Answers0