WebApi and SqlGeograpgy.StDistance error

6 posts, 0 answers
  1. Cuneyt
    Cuneyt avatar
    36 posts
    Member since:
    Dec 2011

    Posted 03 Sep 2013 Link to this post

    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:

  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 06 Sep 2013 Link to this post

    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.
  3. DevCraft banner
  4. Cuneyt
    Cuneyt avatar
    36 posts
    Member since:
    Dec 2011

    Posted 11 Sep 2013 Link to this post

    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.
  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 13 Sep 2013 Link to this post

    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.
  6. Cuneyt
    Cuneyt avatar
    36 posts
    Member since:
    Dec 2011

    Posted 16 Sep 2013 Link to this post

    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(); 
  7. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 18 Sep 2013 Link to this post

    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.
Back to Top
DevCraft banner