Skip to content

SQL Bulk Copy does not support SQL Graph edge pseudo-column names #123

@Tornhoof

Description

@Tornhoof

Describe the bug

SQL Bulk Copy does not support inserting edges via the edge pseudo-column names $from_id and $to_id. Instead it is required to obtain the real column names (e.g. $from_id_BFD72683A83A41F0B34EF83B93841F8E), this needs to be done at runtime as those values are unique.

If you are seeing an exception, include the full exceptions details (message and stack trace).

System.InvalidOperationException
  HResult=0x80131509
  Message=The given ColumnMapping does not match up with any column in the source or destination.
  Source=Microsoft.Data.SqlClient
  StackTrace:
   at Microsoft.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at ConsoleApp25.Program.Main(String[] args) in C:\Users\stefan\source\repos\ConsoleApp25\Program.cs:line 95

To reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

using System.Data;
using Microsoft.Data.SqlClient;

namespace ConsoleApp25
{
    class Program
    {
        static void Main(string[] args)
        {
            using var sqlConnection =
                new SqlConnection("Server=localhost;Initial Catalog=SqlGraph;Integrated Security=true;");
            sqlConnection.Open();
            var setup = @"DROP TABLE IF EXISTS Nodes;" +
                        "DROP TABLE IF EXISTS Edges;" +
                        "CREATE TABLE Nodes (Id INT PRIMARY KEY Identity(1,1), [Name] NVARCHAR(100)) AS Node;" +
                        "CREATE TABLE Edges AS Edge;";
            using (var command = new SqlCommand(setup, sqlConnection))
            {
                command.ExecuteNonQuery();
            }

            var nodes = new DataTable("Nodes");
            nodes.Columns.Add("Name", typeof(string));
            for (int i = 0; i < 100; i++)
            {
                nodes.Rows.Add($"Hello World {i}");
            }

            using (var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
            {
                BatchSize = 5000,
                DestinationTableName = "Nodes",
                EnableStreaming = true
            })

            {
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.WriteToServer(nodes);
            }

            // we need to obtain the real column names
            string fromId = null;
            string toId = null;
            using (var command =
                new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Edges'",
                    sqlConnection))
            {
                using var output = command.ExecuteReader();
                while (output.Read())
                {
                    var columnName = output.GetString(0);
                    if (columnName.StartsWith("$from_id"))
                    {
                        fromId = columnName;
                    }
                    else if(columnName.StartsWith("$to_id"))
                    {
                        toId = columnName;
                    }
                }
            }

            var edges = new DataTable("Edges");
            edges.Columns.Add("From", typeof(string));
            edges.Columns.Add("To", typeof(string));
            using (var command = new SqlCommand("SELECT Id, $node_id AS NodeId FROM Nodes", sqlConnection))
            {
                string lastNodeId = null;
                using var output = command.ExecuteReader();
                while (output.Read())
                {
                    var nodeId = output.GetString("NodeId");
                    if (lastNodeId != null)
                    {
                        edges.Rows.Add(lastNodeId, nodeId);
                    }

                    lastNodeId = nodeId;
                }
            }


            using (var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
            {
                BatchSize = 5000,
                DestinationTableName = "Edges",
                EnableStreaming = true
            })

            {
                // 'The given ColumnMapping does not match up with any column in the source or destination.'
                sqlBulkCopy.ColumnMappings.Add("From", "$from_id");  // fromId needs to be the hex $from_id_ABCDEF from the db not the $from_id pseudo-column names
                sqlBulkCopy.ColumnMappings.Add("To", "$to_id"); // toId needs to be the hex $to_id_ABCDEF from the db not the $to_id pseudo-column names
                //sqlBulkCopy.ColumnMappings.Add("From", fromId); <-- this works
                //sqlBulkCopy.ColumnMappings.Add("To", toId);
                sqlBulkCopy.WriteToServer(edges);
            }
            sqlConnection.Close();
        }
    }
}

Expected behavior

$from_id and $to_id are accepted in the column mappings like for non-bulk sql statements.

Atleast the documentation on https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-sql-graph?view=sql-server-2017 should be updated to reflect, because the part about BULK Insert specifies how to get the appropriate $node_id values, but does not mention that for BULK you need to provide the real edge ids.

Further technical details

Microsoft.Data.SqlClient version: 1.0.19128.1-Preview (happens with the old System.Data.SqlClient too)
.NET target: Core 2.2.5, Core 3.0 Preview 5
SQL Server version: SQL Server 2017
Operating system: Windows 10

Additional context

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions