“未注册指定类型"批量插入具有地理空间数据类型的表时出错 [英] "Specified type is not registered" error when bulk inserting table with geospatial data types

问题描述

我正在尝试使用 System.Data 程序集(4.6.1)中的 SqlBulkCopy 类，使用代码批量插入具有地理空间数据类型的表看起来大致像这样(改编自 https://github.com/MikaelEliasson/EntityFramework.Utilities ):

I'm trying to use the SqlBulkCopy class from the System.Data assembly (4.6.1) to bulk insert a table with a geospatial data type, using code that looks roughly like this (adapted from https://github.com/MikaelEliasson/EntityFramework.Utilities):

public void InsertItems<T>(IEnumerable<T> items, string schema, string tableName, IList<ColumnMapping> properties, DbConnection storeConnection, int? batchSize)
{
    using (var reader = new EFDataReader<T>(items, properties))
    {
        var con = (SqlConnection)storeConnection;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }
        using (var copy = new SqlBulkCopy(con))
        {
            copy.BatchSize = batchSize ?? 15000; //default batch size
            if (!string.IsNullOrWhiteSpace(schema))
            {
                copy.DestinationTableName = $"[{schema}].[{tableName}]";
            }
            else
            {
                copy.DestinationTableName = "[" + tableName + "]";
            }

            copy.NotifyAfter = 0;

            foreach (var i in Enumerable.Range(0, reader.FieldCount))
            {
                copy.ColumnMappings.Add(i, properties[i].NameInDatabase);
            }
            copy.WriteToServer(reader); // <-- throws here
            copy.Close();
        }
    }
}

这很好用，直到我尝试在具有地理空间数据的表上使用它.当我这样做时，出现以下错误:

That works great, until I try to use it on a table with geospatial data. When I do that, I get the following error:

ERROR Swyfft.Console.TaskManager - Error running task SeedRating: 
(InvalidOperationException) The given value of type DbGeography from the data source cannot be converted to type udt of the specified target column.;   
(ArgumentException) Specified type is not registered on the target server.System.Data.Entity.Spatial.DbGeography, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.;
   at Swyfft.Data.Utilities.SqlQueryProvider.InsertItems[T](IEnumerable`1 items, String schema, String tableName, IList`1 properties, DbConnection storeConnection, Nullable`1 batchSize) in C:\source\swyfft\swyf-website\Swyfft.Data.Utilities\SqlQueryProvider.cs:line 78
   at Swyfft.Data.Utilities.EFBatchOperation`2.InsertAll[TEntity](IEnumerable`1 items, DbConnection connection, Nullable`1 batchSize) in C:\source\swyfft\swyf-website\Swyfft.Data.Utilities\EFBatchOperation.cs:line 138
   at Swyfft.Data.Rating.RatingContext.BulkInsert[T](IEnumerable`1 entities, Nullable`1 batchSize) in C:\source\swyfft\swyf-website\Swyfft.Data.Rating\RatingContext.cs:line 69
   at Swyfft.Seeding.CsvLoaders.CsvLoader.ProcessCsv[T](StreamReader streamReader, String fileName, ISwyfftContext ctx, Func`2 parserFunc) in C:\source\swyfft\swyf-website\Swyfft.Seeding\CsvLoaders\CsvLoader.cs:line 133
   at Swyfft.Seeding.CsvLoaders.CsvLoader.InitializeCountyBlockQualities(String stateFilter) in C:\source\swyfft\swyf-website\Swyfft.Seeding\CsvLoaders\InitializeCountyBlockQualities.cs:line 35

我到处搜索Google，但没有多大用处.我已经找到了调用链，深入到SqlBulkCopy程序集的内部(谢谢，Resharper！)，但是该错误似乎比我能够挖掘的更深.我尝试安装(并加载)适当的SQL Server类型程序包( https://www.nuget.org/packages/Microsoft.SqlServer.Types/)，但没有骰子.

I've Googled around, to not much avail. I've traced down the call chain, deep into the bowels of the SqlBulkCopy assembly (thanks, Resharper!), but the error seems to be hidden down deeper than I've been able to dig. I've tried installing (and loading) the appropriate SQL Server Types package (https://www.nuget.org/packages/Microsoft.SqlServer.Types/), but no dice.

有什么建议吗?

推荐答案

好的，我想我已经解决了.有问题的代码在 EFDataReader< T> 类中(我是从

OK, I think I got it fixed. The problematic code was in the EFDataReader<T> class (that I'd borrowed from https://github.com/MikaelEliasson/EntityFramework.Utilities/blob/master/EntityFramework.Utilities/EntityFramework.Utilities/EFDataReader.cs). Its GetValue(int ordinal) originally looked like this:

public override object GetValue(int ordinal)
{
    return Accessors[ordinal](Enumerator.Current);
}

但是，这意味着它将返回碰巧通过 as 和 DbGeometry 生成的所有与数据库无关的 DbGeometry 和 DbGeography 值./code>和 DbGeography ，这是SqlBulkCopy类无法理解的.它们实际上需要是特定于SQL Server的，即 SqlGeography 和 SqlGeometry ，如下所示:

But that meant that it was returning any db-agnostic DbGeometry and DbGeography values that happened to come through as DbGeometry and DbGeography, which the SqlBulkCopy class didn't understand. They actually need to be SQL-Server specific, i.e., SqlGeography and SqlGeometry, like so:

public override object GetValue(int ordinal)
{
    object value = Accessors[ordinal](Enumerator.Current);

    var dbgeo = value as DbGeography;
    if (dbgeo != null)
    {
        var chars = new SqlChars(dbgeo.WellKnownValue.WellKnownText);
        return SqlGeography.STGeomFromText(chars, dbgeo.CoordinateSystemId);
    }

    var dbgeom = value as DbGeometry;
    if (dbgeom != null)
    {
        var chars = new SqlChars(dbgeom.WellKnownValue.WellKnownText);
        return SqlGeometry.STGeomFromText(chars, dbgeom.CoordinateSystemId);
    }

    return value;
}

这篇关于“未注册指定类型"批量插入具有地理空间数据类型的表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！