1

I have created below stored procedures in SQL Server 2014. 

Create procedure [dbo].[GetLocationsWithinDistance]
(
    @latitude float,
    @longitude float, 
    @distance float 
)
AS
BEGIN

with LocationsWithDistance as
    (
        select
        [Name]   ,[Timings] ,Address, City ,      
        ( 3959 * acos( cos( radians(@latitude) ) * cos( radians( [Lattitude] ) ) * cos( radians( [Longitude] )
       - radians(@longitude) ) + sin( radians(@latitude) ) * sin( radians( [Lattitude] ) ) ) )  As Distance
            --, more columns
        FROM [QuickLoo].[dbo].[RestRoomSample]
    )
    select
    [Name]   ,[Timings] ,Address, City ,
     Distance        


     from LocationsWithDistance
        where Distance <= @distance  
   END

I am using this stored procedure in Entity Framework 6.0. I have called this in my C# code like below:

var resultFetch = context.GetLocationsWithinDistance(latitude, longitude, distance);

But this is returning no results and also there are errors or warnings. I am not able to figure out where is the issue. all other procedures without CTE expressions are returning result properly. It seems there are some issues with stored proc with CTE expression.

I am calling same in my SQL server which is working fine:

DECLARE @return_value int

EXEC    @return_value = [dbo].[GetLocationsWithinDistance]
        @latitude = 28.5744,
        @longitude = 77.2387,
        @distance = 2

SELECT  'Return Value' = @return_value

GO
Girish Gupta
  • 1,241
  • 13
  • 27
  • possible duplicate of [Getting data from stored procedure with Entity Framework](http://stackoverflow.com/questions/32140774/getting-data-from-stored-procedure-with-entity-framework) – Salah Akbari Sep 12 '15 at 14:16
  • I am able to get results for other stored procured in Entity Framework, but there are some issues with Common Table Expression (CTE) in stored procedure. – Girish Gupta Sep 12 '15 at 14:26
  • Comment `WHERE` condition and check if it returns data, if yes there is something with your query. Second don't reinvent wheel use `GEOGRAPHY` type and functions like`DECLARE @source geography = 'POINT(0 51.5)' ,@target geography = 'POINT(-3 56)' SELECT @source.STDistance(@target)` – Lukasz Szozda Sep 12 '15 at 14:30
  • It is returning results well with SQL Server 2012, but no results in Entity framework. – Girish Gupta Sep 12 '15 at 14:41
  • Check in log how EF call stored procedure – Lukasz Szozda Sep 12 '15 at 14:49
  • Can you show how you are calling SP? – kamil-mrzyglod Sep 12 '15 at 15:18
  • in C# in calling like this: var context = new LocationEntities ; var resultFetch = context.GetLocationsWithinDistance(latitude, longitude, distance); – Girish Gupta Sep 12 '15 at 16:39
  • @GirishGupta Ok, but I want to see how EF call SQL. EF is ORM and in the end it has to translate to pure SQL, check in EF log what is sent to DB – Lukasz Szozda Sep 13 '15 at 07:29
  • Thanks all. I am able to fetch data from my proc. I was passing parameters for which there was no data. My fault. – Girish Gupta Sep 13 '15 at 07:40

0 Answers0