Linq-to-SQL query trouble

Question

i have the following query in SQL:

SELECT TOP 25 *, SQRT(POWER(Latitude - 51.4644, 2) + POWER(Longitude - 0.0988, 2)) * 62.1371192 AS DistanceFromAddress
FROM [Stops].[dbo].[Stops_edited_small]
WHERE ABS(Latitude - 51.4644) < 0.09 AND ABS(Longitude - 0.0988) < 0.09
ORDER BY DistanceFromAddress 

I need to convert it into Linq-to-SQL

I have got this far so far:

 public List<Stops_edited_small> GetMembers(double curLatitude, double curLongitude, int number)
    {
     using (DataClasses1DataContext db = new DataClasses1DataContext())
        {
             int DistanceFromAddress;
            var members = (from member in db.Stops_edited_smalls
                          where Math.Sqrt(Math.Pow(Convert.ToDouble(member.Latitude) - curLatitude, 2) + Math.Pow(Convert.ToDouble(member.Longitude) - curLongitude, 2)) * 62.1371192 as DistanceFromAddress
                          where Math.Abs(Convert.ToDouble(member.Latitude) - curLatitude) < 0.05
                          && Math.Abs(Convert.ToDouble(member.Longitude) - curLongitude) < 0.05
                          select member).Take(25);

            return members.ToList();
        }
}

I am lost what to do with the as DistanceFromAddress part, and how to properly integrate it into my c#? I guess I have too many where clauses as well. Any help appreciated.

EDIT: included whole class to show return type (its a WCF service)

Answer 1

The solution was so simple I didnt even consider it!

 var members = (from member in db.Stops_edited_smalls

                          where Math.Abs(Convert.ToDouble(member.Latitude) - curLatitude) < 0.05
                          && Math.Abs(Convert.ToDouble(member.Longitude) - curLongitude) < 0.05
                          orderby (Math.Sqrt(Math.Pow(Convert.ToDouble(member.Latitude) - curLatitude, 2) + Math.Pow(Convert.ToDouble(member.Longitude) - curLongitude, 2)) * 62.1371192)
                          select member).Take(number);

            members.ToList();

Just added the orderby line.

Answer 2

Something like this ?

        var members = (from member in db.Stops_edited_smalls
                       where Math.Abs(Convert.ToDouble(member.Latitude) - curLatitude) < 0.05
                       && Math.Abs(Convert.ToDouble(member.Longitude) - curLongitude) < 0.05
                       select new { member, DistanceFromAddress = Math.Sqrt(Math.Pow(Convert.ToDouble(member.Latitude) - curLatitude, 2) + Math.Pow(Convert.ToDouble(member.Longitude) - curLongitude, 2)) * 62.1371192 }).Take(25);

Answer 3

I'm not fimiliar with that distance calculation formula, but I built a set of classes you can use to calculate distance using the Haversine formula. Hopefully, it should be a little easier to implement this within your LINQ query, since it's all wrapped up into two classes.

GeocodedPosition class:

public class GeocodedPosition
{
    private double lat;
    private double lon;

    public GeocodedPosition(double latitude, double longitude)
    {
        lat = latitude;
        lon = longitude;
    }

    public double Latitude
    {
        get
        {
            return lat;
        }
    }

    public double Longitude
    {
        get
        {
            return lon;
        }
    }
}

GeocodeCalculator class:

public class GeocodeCalculator
{
    private const int earthRadiusMiles = 3960;
    private const int earthRadiusKilometers = 6371;

    public enum DistanceType
    {
        Miles,
        Kilometers
    }

    /// <summary>
    /// Uses the Haversine formula to calculate the distance between two locations
    /// </summary>
    /// <param name="pos1"></param>
    /// <param name="pos2"></param>
    /// <param name="type"></param> 
    /// <returns></returns>
    public double Distance(GeocodedPosition PositionA, GeocodedPosition PositionB, DistanceType type)
    {
        double r = (type.Equals(DistanceType.Miles)) ? earthRadiusMiles : earthRadiusKilometers;
        double dLat = ToRadian(PositionB.Latitude - PositionA.Latitude);
        double dLon = ToRadian(PositionB.Longitude - PositionA.Longitude);
        double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) + Math.Cos(ToRadian(PositionA.Latitude)) * Math.Cos(ToRadian(PositionB.Latitude)) * Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
        double c = 2 * Math.Asin(Math.Min(1, Math.Sqrt(a)));
        double d = r * c;
        return d;
    }

    /// <summary>
    /// Convert to Radians
    /// </summary>
    /// <param name=”val”></param>
    /// <returns></returns>
    private double ToRadian(double val)
    {
        return (Math.PI / 180) * val;
    }
}