Multiple context queries inside a query

c# entity-framework entity-framework-6 linq

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();
1
1
1/8/2019 6:01:52 PM

Accepted Answer

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.

1
1/8/2019 6:07:34 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow