CODEWITHSUNDEEP
c#sql-serverentity-framework-coresqlbulkcopynettopologysuite
Johan
Johan

Reputation: 1515

Bulk Insert with NTS geometries in .NET Core

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?

Upvotes: 2

Views: 1026

Answers (3)

Eric Robishaw
Eric Robishaw

Reputation: 33

The key for bulk insert with NetTopologySuite entities (of type geometry, geography) is:

  • Set your datatable's column type to object (or your entity's property type to object)
  • dtTable.Columns.Add("Shape", typeof(Object)); //force object
  • Use the NetTopologySuite.IO.SqlServerBytesWriter() object to convert your shape to a byte array (byte[]) and set your object column / property to this byte array
  • var byteWriterGeometry = new NetTopologySuite.IO.SqlServerBytesWriter(); dataRow["geometry"] = byteWriteGeometry.Write(shape);

Here's a dotnetfiddle using the fantastic Z.BulkOperations library which demonstrates this: https://dotnetfiddle.net/DZiQJG

Upvotes: 0

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27416

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

Upvotes: 3

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

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

Upvotes: 2

Related Questions