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?
3条答案
按热度按时间fjaof16o1#
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):Then you can use
BulkCopy
for any entities with any Geometry property:ebdffaop2#
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 supportsSQL 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:
djmepvbi3#
The key for bulk insert with NetTopologySuite entities (of type geometry, geography) is:
dtTable.Columns.Add("Shape", typeof(Object)); //force object
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