Bulk Insert with NTS geometries in .NET Core

user_6242828 0
Viewed 482

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?

nettopologysuite entity-framework-core sqlbulkcopy sql-server c#
2 Answers

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);
user_56191430

Disclaimer: I'm one of the creators of linq2db and extension linq2db.EntityFrameworkCore

linq2db itself has no dependencies to NetTopologySuite so library should know how to convert such types. Configure them ONCE per application (I hope covered everything):

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

MappingSchema.Default.SetConverter<Point, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));
MappingSchema.Default.SetConverter<Polygon, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));
MappingSchema.Default.SetConverter<GeometryCollection, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));
MappingSchema.Default.SetConverter<LinearRing, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));
MappingSchema.Default.SetConverter<LineString, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));
MappingSchema.Default.SetConverter<MultiLineString, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));
MappingSchema.Default.SetConverter<MultiPoint, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));
MappingSchema.Default.SetConverter<MultiPolygon, DataParameter>(p => new DataParameter(null, writer.Write(p), DataType.Udt));

Then you can use BulkCopy for any entities with any Geometry property:

context.BulkCopy(someEntities);
user_106463160
Related