none
SQLGeorgraphy not working with Linq to Entities

    Question

  • var query = from place in context.Addresses let distance = SqlGeography .Point(place.Latitude, place.Longitude, 4326) .STDistance(yourLocation) .Value where distance < 1 * 1609.344 orderby distance select place;

    Error: LINQ to Entities does not recognize the method 'System.Data.SqlTypes.SqlDouble STDistance(Microsoft.SqlServer.Types.SqlGeography)' method, and this method cannot be translated into a store expression.

    Any ideas ?


    • Edited by thukralz Friday, December 27, 2013 2:26 AM Updated Title
    Friday, December 27, 2013 2:25 AM

Answers

  • Hi,

    If you are using Code First in Entity Framework, you can refer to link below:

    http://msdn.microsoft.com/en-us/hh859721

    If you have a model then you can refer to link below:

    http://blogs.msdn.com/b/efdesign/archive/2011/05/04/spatial-types-in-the-entity-framework.aspx

    It says that to make this work, we have created the most common functions as canonical functions on EDM. As a result, Entity Framework will defer the execution of the function to the server.

    Hope it to be helpful to you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by thukralz Friday, December 27, 2013 10:08 AM
    Friday, December 27, 2013 9:53 AM

All replies

  • Hello,

    For this error message, it is because LINQ to Entities requires the whole LINQ query expression to be translated to a server query. Only a few uncorrelated subexpressions (expressions in the query that do not depend on the results from the server) are evaluated on the client before the query is translated. Arbitrary method invocations that do not have a known translation, like SqlGeography() in this case, are not supported.

    To be more specific, LINQ to Entities only support Parameterless constructors and Initializers.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 27, 2013 6:43 AM
  • Hello,

    For this error message, it is because LINQ to Entities requires the whole LINQ query expression to be translated to a server query. Only a few uncorrelated subexpressions (expressions in the query that do not depend on the results from the server) are evaluated on the client before the query is translated. Arbitrary method invocations that do not have a known translation, like SqlGeography() in this case, are not supported.

    To be more specific, LINQ to Entities only support Parameterless constructors and Initializers.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    thanks for mod sharing
    Friday, December 27, 2013 6:55 AM
  • Is there an example on fixing this using Linq 2 Entity? I can directly use haversine, I was hoping if I can use Sqlgeography.
    Friday, December 27, 2013 8:59 AM
  • Hi,

    If you are using Code First in Entity Framework, you can refer to link below:

    http://msdn.microsoft.com/en-us/hh859721

    If you have a model then you can refer to link below:

    http://blogs.msdn.com/b/efdesign/archive/2011/05/04/spatial-types-in-the-entity-framework.aspx

    It says that to make this work, we have created the most common functions as canonical functions on EDM. As a result, Entity Framework will defer the execution of the function to the server.

    Hope it to be helpful to you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by thukralz Friday, December 27, 2013 10:08 AM
    Friday, December 27, 2013 9:53 AM