Figure out testing for EF use of Microsoft.SqlServer.Types

[ajcvickers]

Testing for Microsoft.SqlServer.Types is problematic because:

• The types are not available on .NET Core
• Setting up a machine to have the correct versions of the correct types in the correct places is fragile and has traditionally causes C.I./build issues.

For now I have been using this little test app for most of my functional testing:

public class Program
{
    public static void Main()
    {
        var loggerProvider = new MyLoggerProvider();

        using (var context = new TestDbContext())
        {
            // Create UDT columns
            context.Database.EnsureDeleted();
            context.GetService<ILoggerFactory>().AddProvider(loggerProvider);
            context.Database.EnsureCreated();

            // Single save
            context.AddRange(NewFoo(0));
            context.SaveChanges();

            // Batch save
            context.AddRange(NewFoo(1), NewFoo(2));
            context.SaveChanges();
        }

        using (var context = new TestDbContext())
        {
            // Simple query
            var foos = context.Foos.OrderBy(e => e.Id).ToList();
            AssertFoo(foos[0], 0);
            AssertFoo(foos[1], 1);
            AssertFoo(foos[2], 2);

            // Equals translation works for HierarchyId but not for spatial types
            var foo = context.Foos.Single(e => e.HierarchyId.Equals(SqlHierarchyIds[1]));
            AssertFoo(foo, 1);

            // Simple prjection
            var projection = context.Foos.OrderBy(e => e.Id).Skip(1).Select(e => new {foo.Id, foo.Geography1}).First();
            Debug.Assert(projection.Geography1.STEquals(SqlGeographies[1]).IsTrue);

            // FromSql query using spatial function
            var bars = context.Bars.FromSql("SELECT [Id], [Geography1].STDistance([Geography2]) as [Distance] From [Foos]").OrderBy(e => e.Id).ToList();
            AssertDistance(bars[0].Distance, 0);
            AssertDistance(bars[1].Distance, 1);
            AssertDistance(bars[2].Distance, 2);

            // Manipulate and send database updates
            for (var i = 0; i < 3; i++)
            {
                foos[i].Geography1 = SqlGeographies[(i + 1) % 3];
                foos[i].Geography2 = SqlGeographies[(i + 2) % 3];
                foos[i].Geometry = SqlGeometries[(i + 1) % 3];
                foos[i].HierarchyId = SqlHierarchyIds[(i + 1) % 3];
            }
            AssertFoo(foos[0], 0, 1);
            AssertFoo(foos[1], 1, 1);
            AssertFoo(foos[2], 2, 1);

            context.SaveChanges();
        }

        using (var context = new TestDbContext())
        {
            // Query after update
            var foos = context.Foos.OrderBy(e => e.Id).ToList();
            AssertFoo(foos[0], 0, 1);
            AssertFoo(foos[1], 1, 1);
            AssertFoo(foos[2], 2, 1);
        }
    }

    private static void AssertFoo(Foo foo, int index, int offset = 0)
    {
        Debug.Assert((foo.HierarchyId == SqlHierarchyIds[(index + offset) % 3]).IsTrue);
        Debug.Assert(foo.Geometry.STEquals(SqlGeometries[(index + offset) % 3]).IsTrue);
        Debug.Assert(foo.Geography1.STEquals(SqlGeographies[(index + offset) % 3]).IsTrue);
        Debug.Assert(foo.Geography2.STEquals(SqlGeographies[(index + offset + 1) % 3]).IsTrue);
    }

    private static void AssertDistance(double distance, int index) 
        => Debug.Assert(
            Math.Abs(SqlGeographies[index].STDistance(SqlGeographies[(index + 1) % 3]).Value - distance) < 1);

    private static Foo NewFoo(int index)
        => new Foo
        {
            HierarchyId = SqlHierarchyIds[index],
            Geometry = SqlGeometries[index],
            Geography1 = SqlGeographies[index],
            Geography2 = SqlGeographies[(index + 1) % 3]
        };

    public class Bar
    {
        public int Id { get; set; }
        public double Distance { get; set; }
    }

    public class Foo
    {
        public int Id { get; set; }
        public SqlHierarchyId HierarchyId { get; set; }
        public SqlGeometry Geometry { get; set; }
        public SqlGeography Geography1 { get; set; }
        public SqlGeography Geography2 { get; set; }
    }

    public class TestDbContext : DbContext
    {
        public DbSet<Foo> Foos { get; set; }
        public DbSet<Bar> Bars { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .EnableSensitiveDataLogging()
                .UseSqlServer(
                    @"Server=(localdb)\mssqllocaldb;Database=TestTypeEF;ConnectRetryCount=0");
        }
    }

    private static readonly SqlHierarchyId[] SqlHierarchyIds =
    {
        SqlHierarchyId.Parse("/1/1/3/"),
        SqlHierarchyId.Parse("/1/2/3/"),
        SqlHierarchyId.Parse("/2/1/3/")
    };

    private static readonly SqlGeometry[] SqlGeometries =
    {
        SqlGeometry.Point(1, 2, 32768),
        SqlGeometry.Point(10, 12, 32768),
        SqlGeometry.Point(20, 22, 32768)
    };

    private static readonly SqlGeography[] SqlGeographies =
    {
        SqlGeography.Point(77, 88, 4326),
        SqlGeography.Point(7, 8, 4326),
        SqlGeography.Point(45, 21, 4326)
    };
}

public class MyLoggerProvider : ILoggerProvider
{
    public ILogger CreateLogger(string categoryName) => new MyLogger();

    public void Dispose()
    {
    }

    private class MyLogger : ILogger
    {
        public bool IsEnabled(LogLevel logLevel) => true;

        public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception,
            Func<TState, Exception, string> formatter) 
            => Console.WriteLine(formatter(state, exception));

        public IDisposable BeginScope<TState>(TState state) => null;
    }
}

[ErikEJ]

Seperate .net fw test project, and privately deploy the libraries (as content) ?

[ajcvickers]

Triage: we will put this code into a sample.

[ajcvickers]

Verified that test code still works on current bits. Closing this as remaining work is to document and move to a sample, which is covered by dotnet/EntityFramework.Docs#611

[KyleTraynor]

When I try to use SqlHierarchyId in one of my classes and then add-migration, i get the error:
The current CSharpHelper cannot scaffold literals of type 'Microsoft.SqlServer.Types.SqlHierarchyId'. Configure your services to use one that can.

I am using EF Core 2.1 with ASPNetCore 2.1 while targeting .net 4.7.1

Any ideas what I need to do to add a column that uses SqlHierarchyId to my existing entity?

Thanks

[ajcvickers]

@KyleTraynor Thanks for reporting this--filed as a new issue #12192