Better LINQ support for Microsoft.SqlServer.Types

[ajcvickers]

Methods like STDistance are not easy to translate right now because they are t-sql "instance" methods called like this:

SELECT [Id], [Geography1].STDistance([Geography2]) as [Distance] From [Foos]

Function registration and method call translation does not support this out-of-the-box, but it shouldn't be too hard to add support. Once this is done, we should consider whether to write the common translations and ship them somewhere or maybe publish them as a sample.

[ajcvickers]

@smitpatel Will do the initial work to support method call translation to this kind of function, and then pass of to @ajcvickers for testing.

[smitpatel]

Assigning to @ajcvickers since query related work is finished.

[ajcvickers]

Notes for triage: I was able to map t-sql functions and use them.

Functions look like:

public static class SqlGeographyExtensions
{
    public static bool Contains(this SqlGeography geography, SqlGeography otherGeography) 
        => throw new NotImplementedException();
    
    public static double Distance(this SqlGeography geography, SqlGeography otherGeography) 
        => throw new NotImplementedException();
}

Function mapping looks like:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(
        typeof(SqlGeographyExtensions).GetMethod(nameof(SqlGeographyExtensions.Contains)),
        b => b.HasTranslation(e =>
            new SqlFunctionExpression(e.First(), "STContains", typeof(bool), e.Skip(1))));

    modelBuilder.HasDbFunction(
        typeof(SqlGeographyExtensions).GetMethod(nameof(SqlGeographyExtensions.Distance)),
        b => b.HasTranslation(e =>
            new SqlFunctionExpression(e.First(), "STDistance", typeof(double), e.Skip(1))));
}

Usages:

var redmond = context.Cities.Single(c => c.Name == "Redmond");
var housesInRedmond = context.Houses.Where(h => redmond.Shape.Contains(h.Location)).ToList();

foreach (var house in context.Houses)
{
    foreach (var distanceInfo in context.Houses.Select(h => new
    {
        Name = h.Name,
        Distance = h.Location.Distance(house.Location)
    }))
    {
    }
}

Full sample:

using System;
using System.Data.SqlTypes;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;
using Microsoft.SqlServer.Types;

namespace SqlTypesTest
{
    public static class SqlGeographyExtensions
    {
        public static bool Contains(this SqlGeography geography, SqlGeography otherGeography) 
            => throw new NotImplementedException();
        
        public static double Distance(this SqlGeography geography, SqlGeography otherGeography) 
            => throw new NotImplementedException();
    }

    public class House
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public SqlGeography Location { get; set; }
    }

    public class City
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public SqlGeography Shape { get; set; }
    }

    public class TestContext : DbContext
    {
        public static readonly LoggerFactory MyLoggerFactory
            = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

        public DbSet<City> Cities { get; set; }
        public DbSet<House> Houses { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(
                typeof(SqlGeographyExtensions).GetMethod(nameof(SqlGeographyExtensions.Contains)),
                b => b.HasTranslation(e =>
                    new SqlFunctionExpression(e.First(), "STContains", typeof(bool), e.Skip(1))));

            modelBuilder.HasDbFunction(
                typeof(SqlGeographyExtensions).GetMethod(nameof(SqlGeographyExtensions.Distance)),
                b => b.HasTranslation(e =>
                    new SqlFunctionExpression(e.First(), "STDistance", typeof(double), e.Skip(1))));
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                //.UseLoggerFactory(MyLoggerFactory)
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=GeoTest;ConnectRetryCount=0");
    }

    public class Program
    {
        public static void Main(string[] args)
        {
            using (var context = new TestContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.AddRange(
                    new House
                    {
                        Name = "The Schoolhouse",
                        Location = SqlGeography.Point(47.6828774, -122.1105578, 4326)
                    },
                    new House
                    {
                        Name = "Mountain Estate",
                        Location = SqlGeography.Point(47.548186, -122.100786, 4326)
                    },
                    new House
                    {
                        Name = "City House",
                        Location = SqlGeography.Point(47.632915, -122.107813, 4326)
                    }, 
                    new House
                    {
                        Name = "Issquah4222",
                        Location = SqlGeography.Point(47.568359, -122.091143, 4326)
                    });

                context.AddRange(
                    new City
                    {
                        Name = "Bellevue",
                        Shape = SqlGeography.STPolyFromText(new SqlChars(new SqlString("POLYGON ((" + Bellevue + "))")),
                            4326)
                    },
                    new City
                    {
                        Name = "Redmond",
                        Shape = SqlGeography.STPolyFromText(new SqlChars(new SqlString("POLYGON ((" + Redmond + "))")),
                            4326)
                    },
                    new City
                    {
                        Name = "Issaquah",
                        Shape = SqlGeography.STPolyFromText(new SqlChars(new SqlString("POLYGON ((" + Issaquah + "))")),
                            4326)
                    });

                context.SaveChanges();
            }

            using (var context = new TestContext())
            {
                var redmond = context.Cities.Single(c => c.Name == "Redmond");
                var housesInRedmond = context.Houses.FromSql("SELECT Id, Name, Location FROM Houses WHERE {0}.STContains(Location) = 1", redmond.Shape).ToList();

                Console.WriteLine("Houses in Redmond:");
                foreach (var house in housesInRedmond)
                {
                    Console.WriteLine($"  {house.Name} at {house.Location.Lat}, {house.Location.Long}");
                }
            }

            using (var context = new TestContext())
            {
                var redmond = context.Cities.Single(c => c.Name == "Redmond");
                var housesInRedmond = context.Houses.Where(h => redmond.Shape.Contains(h.Location)).ToList();

                Console.WriteLine("Houses in Redmond:");
                foreach (var house in housesInRedmond)
                {
                    Console.WriteLine($"  {house.Name} at {house.Location.Lat}, {house.Location.Long}");
                }
            }

            using (var context = new TestContext())
            {
                foreach (var house in context.Houses)
                {
                    Console.WriteLine($"{house.Name} is ");
                    foreach (var distanceInfo in context.Houses.Select(h => new
                    {
                        Name = h.Name,
                        Distance = h.Location.Distance(house.Location)
                    }))
                    {
                        Console.WriteLine($"  {distanceInfo.Distance / 1609.344} miles from {distanceInfo.Name}");
                    }
                }
            }
        }

        // Note: this is not real data
        public const string Bellevue = @"
-122.209860 47.643414, -122.165056 47.646652, -122.164198 47.660528, -122.164198 47.660528, -122.142740 47.627335, -122.111841 47.646421,
-122.111841 47.646421, -122.087465 47.628030, -122.111326 47.605812, -122.097765 47.571427, -122.111326 47.536231, -122.196127 47.546081,
-122.191835 47.578283, -122.222656 47.621237, -122.209781 47.643564, -122.209860 47.643414";

        public const string Redmond = @"
 -122.163192 47.711288, -122.099677 47.710595, -122.091952 47.654082, -122.111522 47.667378, -122.126628 47.666800, -122.086974 47.627595,
 -122.111178 47.628174, -122.111865 47.646450, -122.143107 47.627364, -122.143622 47.660673, -122.164565 47.661944, -122.163192 47.711288";

        public const string Issaquah = @"
-122.097974 47.571744, -122.082524 47.567691, -122.067933 47.552052, -122.051969 47.552747, -122.051969 47.552747, -122.051969 47.552747,
-122.051969 47.552747, -121.988454 47.555064, -121.994977 47.522964, -122.079949 47.520182, -122.077031 47.547765, -122.101235 47.549156,
-122.097974 47.571744";
    }
}

[ajcvickers]

Filed dotnet/EntityFramework.Docs#611 to document.