REST-Api with Entity Framework CORE / MSSQL server: Compute and return additional field/attribute at runtime

.net-core asp.net-core-webapi entity-framework-core sql sql-server

Question

Intro and sample data

First of all, I want to apologize for the unspecific title. Since I have spent a lot of time on solving this problem (Google, SO, trial & error), I have come to a point at which I don't even know whether I am close to a solution or not.
My basic problem: I want to create a REST-Api with .NET Core which returns entities in a certain distance to a user.

SQL

Let's assume I have a table called Museums which has the following columns:


+------------+---------------+
|    name    |     type      |
+------------+---------------+
| Id         | int           |
| MuseumName | nvarchar(max) |
| Location   | geography     |
+------------+---------------+

Model

The belonging .NET Core model looks like this:

    public class MuseumModel
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public string MuseumName { get; set; }

        [JsonIgnore]
        public Geometry Location { get; set; }
    }

The problem

Now I want to create a REST-Api which returns museums based on a user's location and a max distance. Let's assume an Api call like this: Museums/ByLocation?Latitude=50.114034&Longitude=8.679355&distance=1000. The elements returned with the JSON should contain Id, MuseumName and Distance. In addition to that, they should be ordered by the distance.

Main Problem: Where should I compute that location stuff. In my eyes there are 3 theoretical options where the computing can be done:

  • SQL
  • REST-Api
  • Native Client (in my eyes this is not really in option because I would need to transfer all museums first)

Until now I was trying to do the computing with the SQL server. Are there any arguments against this?

Idea A (logic at controller, computing on DB)

First of all I tried to solve the problem in the controller of the REST-Api:

var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
Geometry userLocation = geometryFactory.CreatePoint(new Coordinate(longitude, latitude));

return await _context.Museums
       .Where(x => x.Location.IsWithinDistance(userLocation, distance))
       .OrderBy(x => x.Location.Distance(userLocation))
       .ToListAsync();

Problem A.1): I am asking myself if the computing time is optimal here since IsWithinDistance and Distance are called separately. Does the SQL server optimize this query?
Problem A.2): How can I easily add the Distance to the resulting JSON? In this example the distance is thrown away after the computing.

Idea B (stored procedure called by controller)

My second approach was a stored procedure on the SQL server which basically adds the field DistanceToUser to the SELECT statement. The SP looks like this:

CREATE PROCEDURE [dbo].[MuseumsWithDistanceToUser]
    @latitude float,
    @longitutde float
AS
    DECLARE @userLocation geography;
    SET @userLocation = geography::STGeomFromText('POINT('+CONVERT([varchar](20),@longitutde)+' '+CONVERT([varchar](20),@latitude)+')', 4326);
    SELECT *, DistanceToUser = Location.STDistance(@userLocation) FROM [dbo].[Museums]
GO

With this approach I think I have solved problem A.1 because the computing should be optimized. The resulting table now contains the DistanceToUser column but unfortunately there is a new problem...

Problem B.1): The new problem is that I am not able to cast the data into the model. I even created a new model MuseumWithDistanceToUserModel : MuseumModel with the attribute DistanceToUser but this approach resulted in this error and adding the [NotMapped] attribute is not possible because basically the new model is mapped to the SP.

If you think this is the way to go, I can share the code and the occurring problems. But is this a good solution at all?

Conclusion

I know I could do the WHERE and ORDER BY statements within the stored procedure and create a new model without any inheritance. But this seems kind of not clean to me. In addition to that I still want to be able to manipulate the query at the controller. Editing the SP on the DB each time there an adjustment is necessary is not preferred. Please share any thoughts you have on this. I am grateful for everything :)

1
0
4/6/2020 11:34:14 AM

Accepted Answer

First issue is, you are not clear about entity framework(EF) and stored procedures(SP). Both are same in big picture. EF is converting your code to sql query, execute it and return result.

SP, it is stored in sql server, when you call it, it will give results. Then you need to map those results to a model(c#).

Therefore at the end it will execute the same query.

And apart from both methods, you can run the query directly using EF.

public async Task<List<Museum>> GetMuseumsByRadius(Point point, double radius){
     return await _context.MuseumTemp
        .FromSql("SELECT Id, MuseumName, ST_Distance(Location::geography, ST_GeomFromText({0}, 4326)) as DistanceToUser  
                  FROM Museums 
                  WHERE ST_Distance(Location::geography, ST_GeomFromText({0}, 4326)) <= {1}"
                , point.AsText(), radius)
        .OrderByDescending(x => x.Distance)
        .AsNoTracking()
        .ToListAsync();
}

public class MuseumTemp
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string MuseumName { get; set; }

    public double? DistanceToUser { get; set; }

    [JsonIgnore]
    public Point Location { get; set; }
}

public virtual DbSet<MuseumTemp> MuseumTemps { get; set; }

// convert lnt,lat to a point
    Point userPoint = new Point(lng, lat)
    {
        SRID = 4326
    };

Alternative, This will generate optimal query by efcore. You can get generated query using web server out put (VS) or sql server profiler.

return await _context.Museums
       .Where(x => x.Location.IsWithinDistance(userLocation, distance))
       .OrderBy(x => x.Location.Distance(userLocation))
       .Select(e => new MuseumModel
                    {
                        Id  = e.Id,
                        MuseumName  = e.MuseumName 
                        DistanceToUser  = e.Location.Distance(userLocation)
                    })
       .ToListAsync();

public class MuseumModel
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string MuseumName { get; set; }

    [NotMapped]
    public double? DistanceToUser { get; set; }

    [JsonIgnore]
    public Geometry Location { get; set; }
}
1
4/14/2020 11:08:41 AM


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