Multiple context queries inside a query

Question

So, i've got a problem with querying a specific data from my database. Lets say i have two contexts containing objects of type:

TrackPoint {
    int Id;
    double Latitude;
    double Longitude;
    int TrackId
{

Track {
    int Id;
    double MinLatitude;
    double MaxLatitude;
    double MinLongitude;
    double MaxLongitude;
}

Every track has some amount of TrackPoints assigned. I want to query all trackpoints, whose tracks intersect with other track (areas constructed from min, max values overlap).

Im trying to achieve this within only one query, as the performance is important. I've managed to do this with the query below, but the execution time is not so good. Im sure there are better ways to this. I would appreciate any advice.

var similarTrackPoints = Context.TrackPoints.Include("Track").Where(
    tp => 
    Context.Tracks.Where(t => t.Id == tp.TrackId).FirstOrDefault().MinLongitude <= track.MaxLongitude &&
    Context.Tracks.Where(t => t.Id == tp.TrackId).FirstOrDefault().MaxLongitude >= track.MinLongitude &&
    Context.Tracks.Where(t => t.Id == tp.TrackId).FirstOrDefault().MinLatitude <= track.MaxLatitude &&
    Context.Tracks.Where(t => t.Id == tp.TrackId).FirstOrDefault().MaxLatitude >= track.MinLatitude)
.ToList();

You dont need to iterate Content.Tracks each time, store the result in a local var and then reuse that. e.g tp => { var x = Context.Tracks.Where(t => t.Id == tp.TrackId).FirstOrDefault(); return x.MinLongitude <= track.MaxLongitude && .... } Or maybe that is only legal ln LINQ for objects? — MikNiller
– MikNiller, Commented Jan 8, 2019 at 17:50
You need to learn how to do a join or, better yet, model the relationship in your entities. Then you can query that directly which could then all occur DB side. — Igor
– Igor, Commented Jan 8, 2019 at 17:53
If every Track has one or more TrackPoints, then shouldn't the class contain a collection of them? — littlerufe
– littlerufe, Commented Jan 8, 2019 at 18:02

Igor · Accepted Answer · 2019-01-08 18:07:34Z

You should model your relationships in your entities. Then you can more easily construct a lambda or linq query that results in a proper query that is executed DB Server side.

public class TrackPoint 
{
    public int Id { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
    public double TrackId { get; set; }
    public Track Track { get; set; }
}

public class Track 
{
    public double Id { get; set; }
    public double MinLatitude { get; set; }
    public double MaxLatitude { get; set; }
    public double MinLongitude { get; set; }
    public double MaxLongitude { get; set; }
    public ICollection<TrackPoint> TrackPoints { get; set; }
}

Context.TrackPoints.Include("Track")
    .Where(_ => 
        _.Track.MinLongitude <= track.MaxLongitude &&
        _.Track.MaxLongitude >= track.MinLongitude &&
        _.Track.MinLatitude  <= track.MaxLatitude &&
        _.Track.MaxLatitude  >= track.MinLatitude)
    .ToList();

I did not include relationship mappings in the code above but you can map the relationship using either fluent or attribute notations similar to other EF mappings.

Collectives™ on Stack Overflow

Multiple context queries inside a query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related