Databases
StackOverflow2013
Postsdbo
POHow to optimize Entity Framework query when using spatial types and automapper?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimize Entity Framework query when using spatial types and automapper?
    primarykey
    data
    text
    <p>What I'm building is not something very unique. In a nutshell I'm creating a small FourSquare like service running in Azure using ASP.NET MVC 4(Web Api) and Entity Framework 5 (with Spatial support). So I'm using SQL Azure and not one of the NoSQL databases like MongoDB or CouchDB. Partly because I'm more fluent/familiar with .NET, partly to see what the development experience is (refactoring, deploying, testing) and partly to see how it will stack up against eg. node.js/MongoDB.</p> <p>Now let's see some code.</p> <pre><code>/// &lt;summary&gt; /// Return the nearest locations relative from the given longitude/latitude /// &lt;/summary&gt; /// &lt;param name="longitude"&gt;Longitude&lt;/param&gt; /// &lt;param name="latitude"&gt;Latitude&lt;/param&gt; /// &lt;param name="maxresults"&gt;Optional maximum results, default is 2&lt;/param&gt; /// &lt;param name="radius"&gt;Optional maximum radius in kilometres, default is 50 km&lt;/param&gt; /// &lt;returns&gt;&lt;/returns&gt; public JsonEnvelope Get(string longitude, string latitude, int maxresults = 2, int radius = 50) { var pointTxt = string.Format("POINT({0} {1})", longitude, latitude); var locations = (from s in locationEntityRepository.GetAll orderby s.Coordinates.Distance(DbGeography.FromText(pointTxt)) where s.Coordinates.Distance(DbGeography.FromText(pointTxt)) / 1000 &lt;= radius select new Location { Id = s.Id, Name = s.Name, LocationType = s.LocationType, Address = s.Address, Longitude = s.Coordinates.Longitude.Value, Latitude = s.Coordinates.Latitude.Value, Distance = (s.Coordinates.Distance(DbGeography.FromText(pointTxt)).Value) / 1000 }) .Take(maxresults).ToList(); // Bad bad bad. But EF/Linq doesn't let us do Includes when using subqueries... Go figure foreach (var location in locations) { location.Checkins = AutoMapper. Mapper. Map&lt;List &lt;Checkin&gt;, List&lt;LocationCheckinsJsonViewModel&gt;&gt; (checkinRepository.GetCheckinsForLocation(location.Id).ToList()); } // AutoMapper.Mapper.Map&lt;Checkin, CheckinViewModel&gt;(dbCheckin); var jsonBuilder = new JsonResponseBuilder(); jsonBuilder.AddObject2Response("locations", locations); return jsonBuilder.JsonEnvelope; } </code></pre> <p>A couple of things I think I need to clarify. The <code>locationEntityRepository.GetAll</code> looks like this.</p> <pre><code>public IQueryable&lt;LocationEntity&gt; GetAll { get { return _context.Locations; } } public IQueryable&lt;LocationEntity&gt; GetAllIncluding(params Expression&lt;Func&lt;LocationEntity, object&gt;&gt;[] includeProperties) { IQueryable&lt;LocationEntity&gt; query = _context.Locations; foreach (var includeProperty in includeProperties) { query = query.Include(includeProperty); } // var tmp = query.ToList(); return query; } </code></pre> <p>Now the code really smells funky. Ideally I want to be able to use an <code>GetAllIncluding(c =&gt; c.Checkins)</code> instead of the <code>GetAll</code> method, and to be able to use <code>AutoMapper</code> to map within the LINQ projection.</p> <p>I know it's by design that Include + LINQ/EF returns null by design when using subqueries. And using automapper in a LINQ/EF query should be done with the <code>Project().To&lt;&gt;</code>, but that doesn't work when using <code>.ForMember</code>.</p> <p>So the challenge is to make the code more efficient (less SQL and easy to maintain when changes to my JSON structures are needed. Remember, we're trying to beat node.js/MongoDB here ;) Should I bother, or leave it as is?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjoce
    1. USSaab
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to optimize Entity Framework query when using spatial types and automapper?
      1. USSaab
      1. VTBountyStart
    2. VO
      singulars
      1. POHow to optimize Entity Framework query when using spatial types and automapper?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to optimize Entity Framework query when using spatial types and automapper?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI'm confused what the problem is. If you add `.Include("Checkins")` in your select statement right after your `locationEntityRepository.GetAll` EF won't allow it? (where "Checkins" is the name of the navigation property on your entity) I have to admit I prefer method based Linq statement and didn't/can't test your code above. But at first glance this looks like a syntax issue. But even if not, you can write your requested "GetAllIncluding()" method, I just don't think that is the correct approach. If I've misunderstood, please edit your question to include your code for the two entity models.
      singulars
      1. POHow to optimize Entity Framework query when using spatial types and automapper?
      1. USBenSwayne
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload