title: Spatial type Entity Framework Core SQL Server
body: <p>I need to work with georaphic distances.</p>
  
  <p>I got        </p>
  
  <pre><code>public class Clinic
  {
      ...
      public double Latitude
      public double Longitud
      ...
  }
  </code></pre>
  
  <p>This clinics are on a SQL Server DB. I need to return them ordered by distance to a certain point. 
  For what I read, Entity Framework Core has no support for DbGeography or something of that sort as to represent the SQL Server type:</p>
  
  <pre><code>geography
  </code></pre>
  
  <p>I added to the database a column with the calculated geography point of each clinic</p>
  
  <pre><code>UPDATE Clinics SET Geo = geography::Point(Clinics.Latitude, Clinics.Longitud,4326)
  </code></pre>
  
  <p>OK, know i need to return them ordered. SQL supports this by querying</p>
  
  <pre><code>DECLARE @p geography;
  SET @p = geography::Point(41,2,4326);
  SELECT * FROM Clinics c ORDER BY @p.STDistance(c.Geo);
  </code></pre>
  
  <p>and Entity Framework Core supports querying with a raw SQL.</p>
  
  <pre><code>context.Clinics.FromSql("..query..")
  </code></pre>
  
  <p>Thing is, as the documentation of the FromSql says, if the data you are returning doesn't exactly match the model it crashes.
  As in C# I cant have a DbGeography that represents Geo I can't figure out how to resolve this. </p>
  
  <p><strong>EDIT 1:</strong></p>
  
  <p>Got it to partially work with the following:</p>
  
  <pre><code>string rawSQL = @"
          DECLARE @p geography;
          SET @p = geography::Point(41,2,4326);
          SELECT c.Id, c.Name, c.Price, c.Quote, c.QuoteAuthor, c.QuoteSource, c.Description, c.Image, c.Latitude, c.Longitude, c.Category, c.CenterDistance, c.NumReviews, c.Stars
          FROM Clinics c
          ORDER BY @p.STDistance(c.Geo); ";
  query = query.FromSql(rawSQL);
  </code></pre>
  
  <p>So now I get all the attributes of Clinics and it works! Thing is, clinics has a related class Amenities</p>
  
  <pre><code>public class Clinic
  {
      ...
      public double Latitude
      public double Longitud
      public Amenity amenity
      ...
  }
  </code></pre>
  
  <p>So when I run a query I need to include the amenities by linq</p>
  
  <pre><code>query = query.Include(c =&gt; c.ClinicAmenities).ThenInclude(ca =&gt; ca.Amenity);
  </code></pre>
  
  <p>Dies because Orderby from the raw sql comes before the include I believe</p>