This is a migrated thread and some comments may be shown as answers.

WebApi and SqlGeograpgy.StDistance error

5 Answers 92 Views
Web Services
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Cuneyt
Top achievements
Rank 1
Cuneyt asked on 03 Sep 2013, 11:13 AM

I have latitude and longtitude and coordinate in a SQL DB stored as numeric(9,6), numeric(9,6),geography.
OpenAccess mapped this as Double,double,SqlGeography.
and I added web service(web api) to tfr.

I tried to find all points within a distance around my loc as you described link

extended TFRsControler.partial.cs

public virtual IQueryable<TFR> GetByCoordinateRad(double _lat,double _lon,  int _radius)
        {
            SqlGeography myLoc = SqlGeography.Point(_lat, _lon, 4326);
            double distanceMeter=_radius*1000.0;
 
            IQueryable<TFR> entity = repository.GetAll().Where(s => (double)s.Coordinate.STDistance(myLoc) < distanceMeter);
 
            if (entity == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
 
            return entity;
        }
 
but give this error:
System.ArgumentException was unhandled by user code
  HResult=-2147024809
  Message='Microsoft.SqlServer.Types.SqlGeography' türünde tanımlanan 'System.Data.SqlTypes.SqlDouble STDistance(Microsoft.SqlServer.Types.SqlGeography)' yöntemi 'Microsoft.SqlServer.Types.SqlGeography' türündeki örnekle çağrılamaz
  Source=System.Core
  StackTrace:
       konum: System.Linq.Expressions.Expression.ValidateCallInstanceType(Type instanceType, MethodInfo method)
       konum: System.Linq.Expressions.Expression.ValidateStaticOrInstanceMethod(Expression instance, MethodInfo method)
       konum: System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, IEnumerable`1 arguments)
       konum: System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, Expression[] arguments)
       konum: SocialTraffic.WebApiService.TFRsController.GetByCoordinateRad(Double _lat, Double _lon, Int32 _radius) *****.WebApiService\TFRsController.partial.cs içinde: satır 34
       konum: lambda_method(Closure , Object , Object[] )
       konum: System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass13.<GetExecutor>b__c(Object instance, Object[] methodParameters)
       konum: System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
       konum: System.Web.Http.Controllers.ReflectedHttpActionDescriptor.<>c__DisplayClass5.<ExecuteAsync>b__4()
       konum: System.Threading.Tasks.TaskHelpers.RunSynchronously[TResult](Func`1 func, CancellationToken cancellationToken)
  InnerException:

5 Answers, 1 is accepted

Sort by
0
Alexander
Telerik team
answered on 06 Sep 2013, 08:35 AM
Hello Cuneyt,

I am not quite sure what the error says in Turkish but I assume there is a problem loading the SqlGeography type. As a first step I would suggest you to have a look at the GAC (C:\Windows\assembly) and check if the Microsoft.SqlServer.Types assembly is available there and what version it has. If it is there, please also check if you reference the same assembly in your project.

You could also have a look at the Basic Spatial Data With OpenAccess demo project in our Samples Kit and use it as reference how to setup your project.

Regards,
Alexander
Telerik
OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
0
Cuneyt
Top achievements
Rank 1
answered on 11 Sep 2013, 08:08 AM
Thank you. problem is solved.
But i have another question. this is not really related with orm maybe linq.  but i cant solve.
Documentation has a method like below to use STDistance.
using (EntitiesModel dbContext = new EntitiesModel())
    {
       SqlGeography Munich = SqlGeography.Point(48.136944, 11.575278, 4326);
       const double DistanceMeters = 1500 * 1000.0;
 
       var query = from x in dbContext.Cities
                   where (double)x.Location.STDistance(Munich) < DistanceMeters
                   orderby x.Location.STDistance(Munich)
                   select new { Name = x.Name, Distance = x.Location.STDistance(Munich) };
 
       foreach (var city in query)
       {
           Console.WriteLine(city);
       }
    }

And now i want to use this at my web api service like below but I couldnt get any sql point. is This code that i use True? or not. how can i get this.

public virtual IQueryable<TFR> GetByCoordinateRad(double _lat,double _lon,  int _radius)
        {
            SqlGeography myLoc = SqlGeography.Point(_lat, _lon, 4326);
            double distanceMeter=_radius*1000.0;
  
            IQueryable<TFR> entity = repository.GetAll().Where(s => (double)s.Coordinate.STDistance(myLoc) < distanceMeter);
  
            if (entity == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
  
            return entity;
        }

linq query:
var query = from x in dbContext.Cities
                   where (double)x.Location.STDistance(Munich) < DistanceMeters
                   orderby x.Location.STDistance(Munich)
                   select new { Name = x.Name, Distance = x.Location.STDistance(Munich) };
 
web service query that i try to wrote:
IQueryable<TFR> entity = repository.GetAll().Where(s => (double)s.Coordinate.STDistance(myLoc) < distanceMeter);
  
is this linq query equal to web service query ?

Very thanks.
0
Viktor Zhivkov
Telerik team
answered on 13 Sep 2013, 04:06 PM
Hello Cuneyt,

There is a small but important difference between the two code snippets.
In the one that uses Web API repository the distance filter will be applied on the client side and because of that it will load a lot of unnecessary data in-memory.
If you take a look at OpenAccessBaseRepository.GetAll() method you will see that there is a call to .ToList() and context.CreateDetachedCopy() that cause this behaviour. If you want to use the repository and have efficient database side queries you should expose the raw context.GetAll<T>() method publicly and use it in your custom service method.
Besides that your LINQ query is correct and is equivalent to the one that you have seen in the documentation.

If you have any additional questions do not hesitate to contact us again.

Regards,
Viktor Zhivkov
Telerik
OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
0
Cuneyt
Top achievements
Rank 1
answered on 16 Sep 2013, 12:18 PM
If i understand you correct, about that you say:
    "If you take a look at OpenAccessBaseRepository.GetAll() method you will see that there is a call to .ToList() and context.CreateDetachedCopy() that cause this behaviour. If you want to use the repository and have efficient database side queries you should expose the raw context.GetAll<T>() method publicly and use it in your custom service method."

I wrote something like below:

CustomBaseRepository:
public virtual IQueryable<TEntity> GetFiltered(Expression<Func<TEntity, bool>> filter)
       {
           if (filter == null)
               throw new ArgumentNullException("filter");
           IQueryable<TEntity> filteredEntities = dataContext.GetAll<TEntity>().Where(filter);
 
           return filteredEntities;
       }

and:
CustomTFRsController:
public virtual IQueryable<TFR> GetByGeo( double latitude, double longitude)
        {
           
             double ofset = 30* 0.0036;//stdistance didnt work for me. i cant find problem. did something like this.
            double latitudeMin = latitude - ofset;
            double latitudeMax = latitude + ofset;
            double longitudeMin = longitude - ofset;
            double longitudeMax = longitude + ofset;
 
            IQueryable<TFR> entityT = repository.GetFiltered(
             s =>  s.Latitude >= latitudeMin && s.Latitude <= latitudeMax &&
                    s.Longitude >= longitudeMin && s.Longitude <= longitudeMax);
  
            return entityT;
 
        }

But without  .ToList() and context.CreateDetachedCopy() query couldnt serialized correctly and give me error.
error:
Message1":"An error has occurred.","ExceptionMessage":"The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; charset=utf-8'.","ExceptionType":"System.InvalidOperationException","StackTrace":null,"InnerException":
 
Message2":"An error has occurred.","ExceptionMessage":"Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'.","ExceptionType":"Newtonsoft.Json.JsonSerializationException","StackTrace":"   konum: Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)\r\n 
 
Message3":"An error has occurred.","ExceptionMessage":"Data null. ","ExceptionType":"System.Data.SqlTypes.SqlNullValueException","StackTrace":"   konum: System.Data.SqlTypes.SqlDouble.get_Value()\r\n   konum: GetValue(Object )\r\n   konum: Newtonsoft
 
and i tried this.
CustomBaseRepository:
public virtual IQueryable<TEntity> GetFiltered(Expression<Func<TEntity, bool>> filter)
       {
           if (filter == null)
               throw new ArgumentNullException("filter");
 
           //dataContext.FetchStrategy = fetchStrategy;
 
           List<TEntity> filteredEntities = dataContext.GetAll<TEntity>().Where(filter).ToList();
 
           //return filteredEntities.AsQueryable();//without CreateDetachedCopy couldnt be serialized .
 
           if (filteredEntities == null)
           {
               return default(IQueryable<TEntity>);
           }
 
           List<TEntity> detachedEntity = dataContext.CreateDetachedCopy<List<TEntity>>(filteredEntities, fetchStrategy);
 
           return detachedEntity.AsQueryable();
       }

 Sorry about this this long message. but finnally i wrote above. and is this code true for your caution. (have efficient database side queries)
List<TEntity> filteredEntities = dataContext.GetAll<TEntity>().Where(filter).ToList(); 
0
Viktor Zhivkov
Telerik team
answered on 18 Sep 2013, 12:59 PM
Hello Cuneyt,

The implementation that you did looks fine and will perform the filters in the Where clause on the database side.
Here are some details about the crucial steps:
  • .ToList() will trigger the execution of the LINQ query and will load the result data into your entity instanced.
  • CreateDetachedCopy is required to detach the loaded entity instance from the data context to prevent any accidental data loading due to lazy loading of related objects.
  • After performing these steps your results are "safe" for transportation across the client-server boundary.
One additional note - there is no need to return IQueryable<T>. You can work fine with IEnumerable<T> and it will indicate much better that any further processing will be done in-memory. So please remove any calls to AsQueryable() method.

If you need any further assistance do not hesitate to contact us again.

Regards,
Viktor Zhivkov
Telerik
OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
Tags
Web Services
Asked by
Cuneyt
Top achievements
Rank 1
Answers by
Alexander
Telerik team
Cuneyt
Top achievements
Rank 1
Viktor Zhivkov
Telerik team
Share this question
or