How to call table-valued function in entity framework core

 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.

CREATE OR ALTER FUNCTION dbo.FilterByLocation
                (
                    @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
                AS
                RETURN
                (
                   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
                );

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))
                {
                    optionsBuilder.UseSqlServer(sqlConnString);
                }
            }
        }
    }
    public DbSet<User> Users { get; set; }
    public DbSet<UserCustomPeopleNearByMeModel> UserCustomPeopleNearBy { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

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

        modelBuilder.Entity<User>()
            .Property(e => e.Id)
            .HasDefaultValueSql("newsequentialid()");
    }
}

[HttpGet]
public ActionResult<List<UserCustomPeopleNearByMeModel>> GetPeopleNearbyMe(double latitude, double longitude, double radiusDistance = 288895.288400)
{
    // Zoom level 12
    List<UserCustomPeopleNearByMeModel> peopleNearby = new List<UserCustomPeopleNearByMeModel>();

    try
    {
        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);
}