In this post, we are going to learn about executing functions with parameters in Entity Framework Core, as well as how to call SQL functions in C# using Entity Framework Core.

Recently, I was working on a project where I needed to display nearby people based on latitude and longitude. Initially, I thought of writing an Entity Framework query for this task. However, I realized that it might be less efficient compared to a SQL query. So, I decided to create a SQL Server function named `dbo.FilterByLocation`. This function is designed to filter user records based on their geographical coordinates.

In detail, the steps I followed are:

  • Created a SQL Server function named dbo.FilterByLocation.Designed the function to take latitude, longitude, and radius as parameters. and the function to return a table containing filtered results.

After creating the SQL function, I consumed it in our Entity Framework Core code, to ensured that all the filtration was done at the database end, so that no need to write LINQ queries for this task.

                    @latitude float, --Assuming latitude is stored with 6 decimal places
                    @longitude float, --Assuming longitude is stored with 6 decimal places
                    @radius float-- Radius in kilometers
                RETURNS TABLE
                   SELECT * FROM(SELECT Top 50 Id, FirstName, LastName, Email, About, Livelat, Livelng, LiveLocation, CoverPicture, ProfilePicture, SQRT(POWER(69.1 * (Livelat - @latitude), 2) + POWER(69.1 * (@longitude - Livelng) * COS(Livelat / 57.3), 2)) AS Distance FROM users where UserStatus = 1 ORDER BY Distance) mytable WHERE Distance < @radius
The nearby people based on latitude and longitude are filtered and retrieved efficiently using the SQL Server function dbo.FilterByLocation, ensuring optimized performance in displaying relevant results.

As part of our project, I created a model class to store the results returned by the SQL Server function.

public class UserCustomPeopleNearByMeModel
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string About { get; set; }
    public double Distance { get; set; }
    public double Livelat { get; set; }
    public double Livelng { get; set; }
    public string LiveLocation { get; set; }
    public string CoverPicture { get; set; }
    public string ProfilePicture { get; set; }

Then, I registered this model in our DbContext.

public class DatabaseContext : DbContext
    private IConfiguration? _configuration;
    public DatabaseContext(IConfiguration configuration) : base()
        _configuration = configuration;
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        if (!optionsBuilder.IsConfigured)
            if (_configuration != null)
                var sqlConnString = _configuration.GetConnectionString("database");
                if (!string.IsNullOrWhiteSpace(sqlConnString))
    public DbSet<User> Users { get; set; }
    public DbSet<UserCustomPeopleNearByMeModel> UserCustomPeopleNearBy { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)

        modelBuilder.Entity<UserCustomPeopleNearByMeModel>(e => e.HasNoKey());

            .Property(e => e.Id)
Finally, I called the SQL Server function within our Controller to get nearby people based on the provided latitude, longitude, and radius distance.
public ActionResult<List<UserCustomPeopleNearByMeModel>> GetPeopleNearbyMe(double latitude, double longitude, double radiusDistance = 288895.288400)
    // Zoom level 12
    List<UserCustomPeopleNearByMeModel> peopleNearby = new List<UserCustomPeopleNearByMeModel>();

        peopleNearby = _context.UserCustomPeopleNearBy.FromSqlInterpolated($"SELECT * FROM dbo.FilterByLocation({latitude}, {longitude}, {radiusDistance})").ToList();
    catch (Exception ex)
        return StatusCode((int)HttpStatusCode.InternalServerError, ex.Message);
    return Ok(peopleNearby);

By utilizing a SQL Server function for geospatial filtering, we ensure efficient querying directly at the database level, which is crucial for performance, especially with large datasets. Integrating this function into Entity Framework Core allows us to maintain the benefits of ORM while offloading complex filtering logic to the database. This approach is cleaner and more maintainable code, as well as better scalability and performance.

This SQL Server function, `dbo.FilterByLocation`, is used to filter records from a table of users based on their geographical location using latitude and longitude coordinates. 

1. The function takes three parameters:
  •  `@latitude`: Represents the latitude coordinate of the location to filter around.
  • `@longitude`: Represents the longitude coordinate of the location to filter around.
  • `@radius`: Specifies the radius in kilometers within which to filter users.

2. It returns a table that will contain the filtered results.

3. The result is a table containing the top 50 closest users within the specified radius from the given latitude and longitude.