Does SqlBulkCopy support Graphtables in MsSql 2017?

Question

I am trying out the new graphdatabase support that was added to Microsoft SQL Server 2017 I wanted to use SqlBulkCopy to insert a couple thousand nodes into a node table. However I always the error: Column '$node_id_DB218B0EAE294E37804103CF4E82BCD2' does not allow DBNull.Value.

My tables are create likes this

CREATE TABLE [Product] (
[id] bigint,
[name] nvarchar(max),
[partsNum] bigint,
[price] float) AS NODE;

CREATE TABLE [DependsOn] (
[weight] float,
[id] bigint) AS EDGE;`

I prepare my a datatable with all attributes and call SqlBulkCopy like this:

using (var bulkCopy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers, null)
{
    DestinationTableName = "Product"
})
{
    bulkCopy.WriteToServer(_dataTable);
}

Now I am wondering if I am doing something wrong or if this is just not supported yet.

Answer 1

SqlBulkCopy has no special handling for DataTable; it will map the columns to copy from source to destination by ordinal position, just as it does for the other overloads that take other sources. So setting up an identity mapping by name isn't optional:

foreach (DataColumn c in dataTable.Columns) {
    sqlBulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}

With graph and node tables the problem is a little more noticeable than usual because the internal columns supporting the structures (which you normally don't use explicitly) appear at the start of the column list, so that almost guarantees failure.