Bulk Insert with NTS geometries in .NET Core

Question

As you probably know, inserting data into a table the "Entity Framework"-way is incredibly slow when you try to insert a lot of rows. One other way is to use SqlBulkCopy which does a great job of increasing performance. The problem is that SqlBulkCopy (from what I've read and tested) doesn't support the SQL geometry type in .NET Core.

This is in EF Core and the C# property type is an NTS Geometry which cannot be changed.

Using the old library Microsoft.SqlServer.Types is not an option because they don't work in .NET Core. The data is currently loaded as NTS Geometry from another SQL Server database.

Has anyone found an efficient way to insert many rows?

Answer 1

Disclaimer: I'm the owner of Entity Framework Extensions

As you probably know inserting data into a table the "Entity Framework"-way is incredibly slow

That's true and this is the main reason why we created our library (paid library).

Our library support context.BulkInsert through Entity Framework and supports SQL Geometries as well.

---

That being said, this is also possible to do it directly through SqlBulkCopy.

For EF Core, you need to convert your value using a SqlServerBytesWriter.

Here is a full example:

var list = // your list

var byteWriterGeometry = new NetTopologySuite.IO.SqlServerBytesWriter();
var byteWriterGeographgy = new NetTopologySuite.IO.SqlServerBytesWriter() { IsGeography = true };

var dt = new DataTable();
dt.Columns.Add("Geometry", typeof(object));
dt.Columns.Add("Point", typeof(object));

list.ForEach(x =>
{
    dt.Rows.Add(byteWriterGeometry.Write(x.Geometry), byteWriterGeographgy.Write(x.Point));
});

var connection = new SqlConnection("your connection string");

connection.Open();

var bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "your table name";

bulkCopy.ColumnMappings.Add("Geometry", "Geometry");
bulkCopy.ColumnMappings.Add("Point", "Point");              

bulkCopy.WriteToServer(dt);