EF Core with spatial column throws exception: Could not load file or assembly Microsoft.SqlServer.Types

Dimigergo commented 2 years ago

My code to reproduce the bug

Bug descrption

I have a small db.sql file in the project which creates a minimal database with one table and one table value function. The table has a geography column and the function makes a query for this table. First question is why the function return type has byte[] for spatial type, why not Geometry like the table parse?

When you run the sample code, the insert is perfect, but when query the function the followin error occures: error I know that Microsoft.SqlServer.Types is a .net framework package only, issued by SQLServer (thats why we use NetTopologySuite), and no .netstandard or core version from it, but who want to use these package? If I query the table with linq, without the function, there is no error.

In the MyDbContext.partial.cs I made a connection with the following settings:

        public MyDbContext(string connectionString, int commandTimeout = 30)
            : base(new DbContextOptionsBuilder<MyDbContext>()
                .UseLazyLoadingProxies()
                .UseSqlServer(
                    connectionString,
                    sqlServerOptions =>
                    {
                        sqlServerOptions.CommandTimeout(commandTimeout);
                        sqlServerOptions.UseNetTopologySuite();
                    })
                .Options)
        {
        }

Include provider and version information

EF Core version: Database provider: (Microsoft.EntityFrameworkCore.SqlServer 5.0.16) Target framework: (.NET 5.0) Operating system: Windows 10 (19041) IDE: (Visual Studio 2022 17.1.3)

bricelam commented 2 years ago

Does it work if you use Geometry (or Point) in the class instead?

  public partial class funcGetCoordinatesResult
  {
-     public byte[] DevCoordinates { get; set; }
+     public Geometry DevCoordinates { get; set; } 
  }

UseNetTopologySuite is designed to seamlessly translate from geography in the database to the NTS types in your model. You shouldn't ever have to go through WKT, WKB, or the SQL Server serialized bytes yourself.

Dimigergo commented 2 years ago

Yes, in this case it works perfect.