How can I use SQL Server's spatial geography types with the NetTopologySuite nuget package in LINQPad?

Question

I have a fairly mature ASP.NET Core web app that uses EF Core against SQL Server.

I needed to use SQL Server's spatial geography types, so added the NetTopologySuite Nuget package, and changed the code in Program.cs as follows...

builder.Services.AddDbContext<AppDbContext>(options => {
  options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"),
    x => x.UseNetTopologySuite());
  options.EnableSensitiveDataLogging();
  options.EnableDetailedErrors();
}, ServiceLifetime.Transient);

I was then able to add a property to one of my models as follows...

public Point Coordinates { get; set; }

...and take advantage of the geography features. This all works fine.

When developing, I generally query my database via LinqPad, using an EF Core connection (as this gives me the correct navigation property names, as well as extension methods, etc). However, since adding the geography stuff, when I try and query the database from LinqPad, I get an error...

InvalidOperationException: The property 'Point.UserData' could not be mapped because it is of type 'object', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

This makes sense, as LinqPad isn't running the application (so presumably doesn't execute the code in Program.cs), but is accessing the assembly containing the context.

Searching around, it looks like I need to call UseNetTopologySuite in the context's OnConfiguring method.

I tried this as follows...

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
    if (!optionsBuilder.IsConfigured) {
      optionsBuilder.UseSqlServer("connection-string", x => x.UseNetTopologySuite());
    }
  }

Ideally I would get the connection string from appSettings.json, but I couldn't work out how to do that, so did this as a first try.

However, even with this code in place, I get the same error when trying to query the database in LinqPad.

Anyone able to advise how I solve this?

Answer 1

Using LinqPad 8.7.4 (but maybe on v6+), I have the option to choose the DbContext from my own assembly (not let LinqPad auto-generate things)

Then you have a bit more options in the next dialog:

but the most important thing is found in the Help me choose link which provides a sample dbContext that's LINQPad Friendly with very helpful comments for different configuration scenarios:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System;

namespace Sample.Data
{
    // This class demonstrates how to write constructors on your DbContext class such that:
    //
    //   (a) it's friendly to LINQPad, ASP.NET Core, Visual Studio tools and Migrations
    //   (b) you get better ease and flexiblity in how/where you specify the connection string

    public class SampleDbContext : DbContext
    {
        string _connectionString;

        // The constructor that ASP.NET Core expects. LINQPad can use it too.
        public SampleDbContext(DbContextOptions<SampleDbContext> options) : base(options) { }

        // You can add extra parameters after the DbContextOptions parameter, as long as they're optional.
        // public SampleDbContext(DbContextOptions<SampleDbContext> options, string extraStuff = "foo") : base (options) { }

        // This constructor is simpler and more robust. Use it if LINQPad errors on the constructor above.
        // Note that _connectionString is picked up in the OnConfiguring method below.
        public SampleDbContext(string connectionString) => _connectionString = connectionString;

        // This constructor obtains the connection string from your appsettings.json file.
        // Tell LINQPad to use it if you don't want to specify a connection string in LINQPad's dialog.
        public SampleDbContext ()
        {
            IConfiguration config = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
            _connectionString = config.GetConnectionString("DefaultConnection");
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Assign _connectionString to the optionsBuilder:
            if (_connectionString != null)              
                optionsBuilder.UseSqlServer(_connectionString);    // Change to UseSqlite if you're using SQLite

            // Recommended: uncomment the following line to enable lazy-loading navigation hyperlinks in LINQPad:
            // if (InsideLINQPad) optionsBuilder.UseLazyLoadingProxies ();
            // (You'll need to add a reference to the Microsoft.EntityFrameworkCore.Proxies NuGet package, and
            //  mark your navigation properties as virtual.)

            // Recommended: uncomment the following line to enable the SQL trace window:
            // if (InsideLINQPad) optionsBuilder.EnableSensitiveDataLogging (true);
        }

        // This property indicates whether or not you're running inside LINQPad:
        internal bool InsideLINQPad => AppDomain.CurrentDomain.FriendlyName.StartsWith("LINQPad");
    }

    // This is just For Visual Studio design-time support and Migrations (LINQPad doesn't use it).
    // Include this class if you want to specify a different connection string when using Visual Studio design-time tools.
    public class SampleDbContextFactory : Microsoft.EntityFrameworkCore.Design.IDesignTimeDbContextFactory<SampleDbContext>
    {
        public SampleDbContext CreateDbContext(string[] args)
            => new SampleDbContext("...design-time connection string...");
    }
}

So, in your case I'd maybe add the parameterless constructor option which uses the appsettings and then keep the UseNetTopologySuite logic into the OnConfiguring method. Not sure if you'd need to add the NetTopologySuite Nuget package in LinqPad (which you can via F4) - but try that too if somehow it won't work.