CODEWITHSUNDEEP
c#linqentity-framework
NZJames
NZJames

Reputation: 5055

Running age/distance LINQ query on EF result query

I want to return a list of users from the database within a certain distance of a point and between a certain age range. Each User record in the database has three relevant fields. LastLocationLatitude, LastLocationLongitude and DateOfBirth. The criteria I have passed in for the search are MinAge, MaxAge and MaxDistance. I am using Entity Framework code first. My query looks like this

 var filtered = _repository.GetItems()
                .Where(s => DateUtils.AgeInRange(s.UserCreated.DateOfBirth, criteria.MinAge, criteria.MaxAge))
                .Where(s => LocationUtils.DistanceInRange(currentLocationLat, currentLocationLon, s.UserCreated.LastLocationLat, s.UserCreated.LastLocationLon, criteria.MaxDistance));

            return filtered;

And the two methods in question are this

 public static bool AgeInRange(DateTime dob, int min, int max)
        {
            var now = DateTime.UtcNow;
            var years = now.Year - dob.Year;
            if (now > dob) years--;

            return years >= min && years <= max;
        }

 public static bool DistanceInRange(double currentLatitude, double currentLongitude, double otherLatitude, double otherLongitude, int maxDistance)
        {
            return SphericalLawOfCosinesCalc(currentLatitude, currentLongitude, otherLatitude, otherLongitude) <= maxDistance;            
        }

private static int SphericalLawOfCosinesCalc(double currentLatitude, double currentLongitude, double otherLatitude, double otherLongitude)
        {
            var latOne = (Math.PI/180)*currentLatitude;
            var latTwo = (Math.PI/180)*otherLatitude;
            var lonOne = (Math.PI/180)*currentLongitude;
            var lonTwo = (Math.PI/180)*otherLongitude;

            var distance = Math.Acos(
                Math.Sin(latOne) * Math.Sin(latTwo) +
                Math.Cos(latOne) * Math.Cos(latTwo) * Math.Cos(lonTwo - lonOne)
                ) * EarthsRadius;

            return Convert.ToInt32(distance / 1.6);
        }

Now before even running this I know it's going to throw an error, stating that LINQ-to-Entities does not recognise the method AgeInRange() or DistanceInRange() as these are all executed on the server. I just coded this up to illustrate what I am trying to achieve.

Without extracting each of those methods to one bestial and really ugly in-line statement, is there any way to achieve what I want to achieve? Obviously I can't return the entire list to the code, convert to a List and then do the filtering as that would be enormous DB traffic for every single search and very slow.

Upvotes: 1

Views: 123

Answers (1)

ken2k
ken2k

Reputation: 48985

Just use spatial types and queries that are now implemented in Entity Framework. You'll able to run your spatial computations on the DB itself, and you'll have better performances thanks to the spatial index.

See for example: http://kindohm.com/blog/2013/04/24/sql-spatial-search-demo.html

Upvotes: 3

Related Questions